この記事は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を書くと後で自分で苦しむので用法用量を守りましょう（そのあたりのつらみを共有する会があれば参加したい）。