データビジュアライズのための基本的なSQLサンプル（複数の軸を同時に集計する場合）

さて、今回のSQLシリーズは男女の集計を同時にするにはどうしたらいいんだろうかという話です。（前回記事はこちら）

これはもうちょっと抽象的に言うと、異なる切り口での集計を複数同時にグラフに起こしたい場合にどうやるのがいいのかという話です。

今回も、前回同様ベースのSQL サンプルはこちらを利用したいと思います。

SELECT
  DATE_FORMAT(users.created_at,"%Y年%m月") AS "対象",
  COUNT(users.id) AS "登録人数"
FROM users
GROUP BY `対象`
ORDER BY `対象`

方法は大きく分けて2つあります。

①GROUP句を拡張する

この方法の場合は、まずSELECTセクション性別を追加し、

SELECT
  DATE_FORMAT(users.created_at,"%Y年%m月") AS "対象",
  users.gender AS "性別",
  COUNT(users.id) AS "登録人数"

GROUP句をこうしてやります。GROUP 句をいじるときは、ORDER 句もいじってやると情報が揃っていいですね。

GROUP BY `対象`, `性別`
ORDER BY `対象`, `性別`

こうすると、このような結果が返ってきます。

|    対象    | 性別 |  登録人数  |
| --------- | ----- | -------- |
| 2019年01月 |  男  |    1,092 |
| 2019年01月 |  女  |    2,992 |
| 2019年02月 |  男  |      897 |
| 2019年02月 |  女  |    1,950 |

これをもとに、男性・女性別の月ごとの登録者数のグラフなどを出すことができます。

②COUNTの条件を拡張する

もう一つの方法が、COUNT句の条件拡張です。
この方法の場合、

COUNT(users.id) AS "登録人数"

が拡張対象になります。具体的にはこうします。

COUNT(CASE users.gender
    WHEN '男' THEN users.id
    ELSE NULL
  END) AS "男性登録人数",
COUNT(CASE users.gender
    WHEN '女' THEN users.id
    ELSE NULL
  END) AS "女性登録人数",

この辺からちょっと解説を挟んでいきます。
この例では、CASE 文でgenderが男の場合、女の場合をそれぞれピックアップしてカウントしています。それ以外の場合をNULLとし、COUNTに含まれないようにしているのです。
この手法は一見すると処理が重そうな感じに取られますがそんなことはなく、①同様何回もQueryを回す実行するわけではないので ①のケースと比べて処理負荷もそう大きい差はありません。

この場合、処理結果のテーブルはこうなります。

|    対象    | 男性登録人数 | 女性登録人数 |
| --------- | ---------- | --------- |
| 2019年01月 |      1,092 |     2,992 |
| 2019年02月 |        897 |     1,950 |

文法がちょっと奇妙なので、敬遠されがちですが、この方法によって生成されるテーブルは非常に扱いやすく、①の手法と違って同じ手法の延長線上で３つ目、４つ目の軸を増やすことができます。

逆に①の方法の場合で出したテーブルでは、一般的なデータビジュアライゼーションツールの機能ではシリーズ表示できる数が２つまでに限られてしまうので、３つ目の軸を追加した場合に対応できなくなってしまうのです。

これら二つの手法は集計をしていると割とよく使うので、どちらの手法も覚えて置いて損はないかと思います。

次回はLTVの例を見てみましょう。