【SQL入門】データ分析に必須なSQL文とは？基本構文を6つ紹介

本記事の概要

本記事の対象者

・SQLでのデータ分析を勉強されている方
・特に非エンジニアのマーケティング担当者やPMの方
・【SQL練習】サンプルデータベースを用意するでMySQL公式のデータベースをローカルに作成されている方には特におすすめです。

本記事の目的

・SQLを使ったデータ分析に必須な6つの基本構文を理解し、使えるようになる。

paymentテーブル概要

決済ごとにID（payment_id）がふられていたり、決済金額(amount)が記録されているなど、一般的なテーブルになっております。
そもそもテーブルとは？という方は【初心者必見】SQLとは？非エンジニアに向けて簡単解説の記事を一度見てSQLの基礎を理解してください。
では以下から具体的にどんな構文を使うのか見ていきましょう。

【SQL入門】データ分析に必須なSQL文とは？基本構文を6つ紹介

まずは6つの構文が使われたコードとその結果を見てください。
（※本当は中央値を取得できるMEDIANなる構文も入れたかったのですが、MySQLでは便利な構文がなかったので、外してます。）

私は初めてのテーブルを見る際、毎回この6つの構文に中央値（MEDIAN）を加えた7つでデータの概要を把握するようにしております。
なぜならデータの概要をイメージしておくことで、分析の後戻りが少なくなるからです。
分析を深掘りした段階で、異常値に引っ張られているデータであることに気が付くと、
一気にそれまで行った分析がふりだしに戻ってしまいタイムロスが非常に大きいです。
そのため感覚値ではなく、数値としてデータの概要を掴むことはデータ分析を行う上で非常に重要なのです。
では1つ1つ構文を解説していきます。

①COUNT構文

一言でまとめるとCOUNTは「数を数える構文」になります。
もう少しSQL風に言うと、「指定したカラムのレコードの数を取得する構文」になります。
上記の例では　count(payment_id) で何回決済されたかを取得する命令を行なっています。

では何人決済したかを見たい時はどうすれば良いのでしょう？

これは　DISTINCT　を使います。
DISTINCTは重複したレコードを1つにまとめてくれる構文になります。

改めて上記のpyamentテーブルを見て頂きたいのですが、
customer_id　のカラムがすべて「１」になっていると思います。
これは「customer_id=1」が振られた同一人物が決済をしているデータということを現しております。

なので何人決済をしたかを確認したい場合はDISTINCTを使って、同一人物を重複してCOUNTする必要があるのです。

以下が集計コードを実際の結果になります。
※画像の赤線の部分がコードになります。

先ほど見たように合計で16,049回決済されているのにも関わらず、
599人しか利用していないことがわかりますね。

COUNTは最頻出構文なのでぜひ覚えてください！

②SUM構文

これも一言でまとめると「数を集計する構文」になります。
エクセルで使うSUM関数と概念はまったく同じです。
指定したカラム（今回はamount）を集計してくれます。

つまり今回の例では合計で67,416.51ドルの売上があったということです。

では合計の売上がTOP5の人を抽出するにはどうすれば良いのでしょう。

これは　GROUP BY　を使います。
GROUP BYはカラムごとにグループ化してくれる構文になります。
今回は人物ごとに売上の合計を集計する必要があるので、customer_idに対してGROUP BYする必要があります。

実際のコード例は以下です。

無事売上TOP5の人物のcustomer_idを抽出することができてますね。
ここからこの大口顧客のペルソナを分析してみても面白いかもしれません。

SUM×GROUP BYも最頻出なのでぜひ覚えてください。

③AVG構文

SUM構文を理解された方ならもうお分かりだと思います。
指定したカラム（今回はamount）の平均を取得してくれる構文になります。

使い方はSUM構文と全く同じです。
一回あたりの決済の平均は約4.2ドルということになります。

では一回あたりの平均決済額が高いTOP5の人物を抽出するにはどうすればよいでしょう。

実際のコードが以下です。

先ほどのSUM構文で抽出したTOP5の人物のcustomer_idとは違ってますね。
ここから以下の仮説が思い浮かびます。
大口顧客は一回あたり大きい金額を払っているわけではなく、何回も利用しているのではないか？
これはぜひこの記事を読み終わったら練習も兼ねて抽出してみてください。
先ほどのSUM構文に　count(payment_id）をつけると大口顧客がどれくらい決済しているのか傾向は掴めると思います。

④STDDEV構文

これは指定したカラム（今回はamount）の標準偏差を求める構文になります。
標準偏差の詳細についてはこちらの記事を参考に理解していただければと思うのですが、
要は「データのばらつきを表す指標」になります。
今回この決済の平均取引額は約4.2ドルになってますが、
これって平均という数字だけでは、「0,0,0,11,10」のように極端に数字が離れて平均が約4.2にドルになっているのか、
「4,4,4,4,5」のように近い数値が並んで平均が4.2ドルになっているかわからないですよね。
ここが標準偏差の便利なところでして、「68%95%ルール」というのが存在することでデータ全体のイメージをしやすくなります。
以下になぜ「68%95%ルール」が便利なのか上記の記事の該当部分を引用してます。

「68%95%ルール」とは？

もし、データの確率分布が正規分布と呼ばれる上図のような形をしていた場合

「平均－1×標準偏差」～「平均＋1×標準偏差」内に、あるデータが含まれる確率が約68%

「平均－2×標準偏差」～「平均＋2×標準偏差」内に、あるデータが含まれる確率が約95%

ということが分かっています。

あるテストの点数分布が正規分布に近似できて、平均点50点・標準偏差10点だったのなら

40点から60点の間に受験者の約68%が存在し

30点から70点の間に受験者の約95%が存在する

ということです。

この標準偏差の「68%95%ルール」、知っているとものすごく便利なんですよ。

なぜなら、データの総数が1000を超えた分布は、正規分布に近い分布になるケースが多いことが分かっているから。

つまり、この標準偏差の「68%95%ルール」は身近にある様々なデータに活用できるのです。

■参考
標準偏差とは何か？その求め方や公式の意味・使い方をわかりやすく説明します

今回のpaymentテーブルのレコード数は16,049ですので、正規分布になると仮定して、
平均約4.2ドル、標準偏差約2.3ドルから考えると、

約1.9ドルから約6.5ドルの間に決済数の約68%が存在し、

0ドルから約8.8ドルの間に決済数の約95%が存在することになります。

上記のように言われるとデータ全体のイメージがつきやすいですよね。
このように標準偏差は非常に便利ですので、SQLを機に覚えてみてください。

⑤MAX構文

MAX構文は指定したカラムの中から最大値を取得してくれる構文になります。
今回の例ですと、11.99ドルが全決済における最大決済額となることがわかります。
こちらはイメージしやすいのではないでしょうか。

⑥MIN構文

MIN構文は指定したカラムの中から最小値を取得してくれる構文になります。
今回の例ですと、0ドルが全決済における最小決済額になります。
余談ですが決済なのに0ドル？と思われた方も多いと思います。
私もそのように考え、少し調べたのですが、謎ですね。
0ドルのレコードが24個あったのですが、決済時間がすべて同じであったので、
その時間に何かしらのバグが発生したのかと思います。