Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

tomoyanamekawa
データエンジニアをしています。
classi
学校の先生・生徒・保護者向けのB2B2Cの学習支援Webサービス「Classi(クラッシー)」 を開発・運営している会社です。
https://classi.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした