SQL初心者必見のデータ分析に使えるSQL

この記事はLinkbal Advent Calendar 2018の22日目の記事です。

リンクバルでデータアナリストをしている滑川です。
データ分析の時に知っていると便利な書き方をユースケースに合わせて紹介します。
さくっと数字をみたい時や依頼をされたデータをRedashで共有したい時など、SQLだけでいろいろ書けるようになると便利になる場面が多いと思っているので参考にしてください。
SQLを覚えたての人や、PythonやR中心でSQLではテーブルをそのまま取ってくるだけみたいな人の役に立てば嬉しいです。

前提

• SQLの基本的な文法は知っていて、sum()やcount()で集計できるくらいのレベル感の人を想定しています。
• 1queryで書きます。
  中間テーブルを作成したり、SQLでざっくり取得したあとにPythonやスプレッドシートで加工したほうが簡単なケースもあります。
• データベースはMySQL 5.7.14を利用しています。
• データがないと説明しづらいので、下記のような購入データ(purchasesテーブル)を使って進めます。

id	price	user_id	user_gender	purchase_created_at
1	3000	1	女性	2018-11-23 14:00:00
2	2000	2	男性	2018-11-25 16:30:50
3	5000	3	女性	2018-12-02 14:00:00
4	500	1	女性	2018-12-02 19:47:01
5	1000	4	男性	2018-12-04 10:00:00
6	3000	4	男性	2018-12-05 20:11:00

日次の男女別の購入数や購入金額を知りたい

紹介したいもの

• date_format()
• sum(条件式)

query

select
  date_format(purchase_created_at, '%Y-%m-%d') as '購入日',
  count(*) as '総購入数',
  sum(price) as '総購入金額',
  sum(user_gender = '女性') as '女性の購入数',
  sum(
    case user_gender
      when '女性' then price
      else 0
    end
  ) as '女性の購入金額',
  sum(user_gender = '男性') as '男性の購入数',
  sum(
    case user_gender
      when '男性' then price
      else 0
    end
  ) as '男性の購入金額'
from
  purchases
group by
  date_format(purchase_created_at, '%Y-%m-%d')
;

結果

購入日	総購入数	総購入金額	女性の購入数	女性の購入金額	男性の購入数	男性の購入金額
2018-11-23	1	3000	1	3000	0	0
2018-11-25	1	2000	0	0	1	2000
2018-12-02	2	5500	2	5500	0	0
2018-12-04	1	1000	0	0	1	1000
2018-12-05	1	3000	0	0	1	3000

説明

date_format(purchase_created_at, '%Y-%m-%d')

日付を指定したフォーマットに変換してくれる関数。
上の例ならdate(purchase_created_at)でも同じだが、他のフォーマットにも書きかえられるので使いやすい。
'%Y-%m-%d'を'%Y-%m'にすれば月次の集計結果にできる。
また、date_format(date_sub(purchase_created_at, interval weekday(purchase_created_at) day), '%Y-%m-%d')にすると、週次(月曜始まり)で集計できる。

sum(gender = '女性')

条件にあったレコード数をカウントできる。
人によっては邪道と思うかもしれないが、group byしてピボットテーブルで集計したりせずともきれいな形で出力できるので便利。
条件式ならなんでもいけるので、汎用性も高い。

前回の購入日との差分を知りたい

紹介したいもの

• 結合条件
• max()

query

select
  purchases.id,
  max(purchases.purchase_created_at) '購入日時',
  max(pre_purchases.purchase_created_at) '前回の購入日時',
  timestampdiff(day, max(purchases.purchase_created_at), max(pre_purchases.purchase_created_at)) '前回の購入は何日前か'
from
  purchases
  left join purchases as pre_purchases
    on purchases.user_id = pre_purchases.user_id
      and purchases.purchase_created_at > pre_purchases.purchase_created_at
group by
  purchases.id
;

結果

id	購入日時	前回の購入日時	前回の購入は何日前か
1	2018-11-23 14:00:00	NULL	NULL
2	2018-11-25 16:30:50	NULL	NULL
3	2018-12-02 14:00:00	NULL	NULL
4	2018-12-02 19:47:01	2018-11-23 14:00:00	-9
5	2018-12-04 10:00:00	NULL	NULL
6	2018-12-05 20:11:00	2018-12-04 10:00:00	-1

説明

結合条件

結合条件は非正規化するためにidを指定することが多いとおもうが、上記のon ~ and purchases.purchase_created_at > pre_purchases.purchase_created_atのような使い方もできる。
そのほかにもon ~ and pre_purchases.user_gender = '女性'のように片方のテーブルだけで条件をして結合するレコード数を減らす時にも使える。

max()

最高金額など単純に最大値をとってくる関数だが、上記のように結合条件やgroup byと組み合わせて使うと応用が効く。
また、user_idでgroup byしたあとにmin()を使ってユーザーごとの初回購入日時を出すこともよくやる。

おわりに

本当は他にもいろいろな書き方を紹介したいのですが、サービスやユースケースに依存しすぎていて紹介しづらいのでこのあたりにしておきます。
SQLはエンジニアやデータアナリスト以外も使えて損がないものなので、マーケッターなどいろいろな職種の方にも広がってほしいです。
※ただし100行を超えるSQLや複雑すぎて魔改造に近いSQLを書くと後で自分で苦しむので用法用量を守りましょう（そのあたりのつらみを共有する会があれば参加したい）。