データビジュアライズのための基本的なSQLサンプル（時系列集計編）

今回はビジュアライズのためのSQLの基本的なサンプルをいくつか紹介したいと思います。

データを知恵に変換していく過程において、様々な業種がひしめく企業の中でビジュアライゼーションは当たり前に行われていて欲しいコミュニケーションの一つですから、これがスムーズでないとCTOは怒られます。笑

こういったところは意外とアナになりがちなので、こういう技能の普及に努めるCTO は世の中にも意外と多いことでしょう。

redash や metabase など、SQL を扱えるデータビジュアライゼーションツールもだいぶ一般的になってきたと思いますが、データの取り扱いに慣れていると一般的には評価されがちなエンジニアでさえ、社内でORMに慣れ親しんだ層にはそういった用途のSQLの書き方を聞かれるケースもなくはないですから、データビジュアライゼーション用のデータアクセスにおいてはデータアナリストやビッグデータエンジニアでもない限り、苦手意識の存在については例外ではありません。

そんな環境に対し、同志の皆さんがが教材作成に苦慮することのないように、ちょっとSQLが書けるようになってきた様々な業態のメンバーにダッシュボードを作ろうという命題の前段として読んでおいて欲しいコツをまとめて置いておきます。

軽い前提はこんな感じ。

・RDBMSはなんでもいいですが、今回は一番シンプルなMySQLを用います。
・date型でタイムスタンプが保存されているとします。
・データ名はできるだけgeneral standard な名称を使い、詳細定義は省きます。
・構造文は大文字で、テーブル・カラム名は小文字で書きます

初回として、時系列集計編です。

時系列グラフはどんな時に使うのか

時系列グラフは様々な局面で使われますよね。
サービスの成長を見るためのKPIダッシュボードに特によく使われます。
しかしながら、SQLだけでそれをやるとどうしても負荷が大きくなりがち＆データストアに負荷を寄せてしまいがちになるので、普段のアプリケーション実装でお目にかかる機会は少ないと思います。

データビジュアライゼーションツールを使っていると、どうしてもSQL だけでそれを表現したい局面は珍しくありませんから、そういった局面のためのサンプルです。

時系列グラフを出したい場合の基本的なSQLデザインパターン

時系列ごとに出したい場合は基本的にGROUP句を使います。
たとえば、サービス開始以来の毎月のユーザー登録数を出したい場合はこんな感じ。

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

これだけで、対象月で括られた登録ユーザーの数が出ます。

※細かいテクニックとして、GROUP、ORDER 句はSELECT 区の結果を参照できるので、同じ処理は書かないようにします。でないと複数回同じで無駄な処理が走ってしまう場合があります。

括れる期間のバリエーション

くくれる期間は

DATE_FORMAT(users.created_at,"%Y年%m月") AS "対象",

で決まります。
参考： MySQLのDATE_FORMAT指定マニュアル

年区切りの場合

DATE_FORMAT(users.created_at,"%Y年") AS "対象",

週区切りの場合①（年間の週番号）

DATE_FORMAT(users.created_at,"%x年%v週目") AS "対象",

日区切りの場合

DATE_FORMAT(users.created_at,"%Y年%m月%d日") AS "対象",

こんな感じで、基本的な区切りは設定することができます。

ちょっとした応用編

データビジュアライゼーションは優しさが大切、ということで、ちょっと応用して優しくしてあげます。

週区切りの場合②（月曜日の日付）
週区切りは意外と週番号という普段意識しないパラメータだとドキドキするので、月曜の日付などで出してあげるのがベストです。

DATE_FORMAT(
    users.created_at - interval WEEKDAY(users.created_at),
    "%Y年%m月%d日の週"
) AS "対象",

年度区切り（3月締めのサンプル）
この年度区切りをオーダーされた時に、ひと溜息つく瞬間があるかと思います。
この辺りになってくると、 `CASE` 文が登場します。
%mの月が文字列で帰ってくるので、数値で比較したら環境によって狂う事に留意してください。

CASE
    WHEN DATE_FORMAT(users.created_at,"%m") <= "03"
        THEN DATE_FORMAT(users.created_at - interval 1 year,"%Y年")
    ELSE DATE_FORMAT(users.created_at,"%Y")
END AS "対象",

Q区切り
意外と多いのがこのQuoter 区切りです。エンジニアリングと商慣習の当たり前の違いを感じるわかりやすい事例ですよね。
年度区切りが分かった人には容易いと思います。今度はCONCATが出てきます。

※除算と余りを使った方法もありますが、MySQLの場合はこちらの方が処理が軽いですね。

CONCAT(
  DATE_FORMAT(users.created_at,"%Y年"),
  "第",
  CASE
    WHEN DATE_FORMAT(users.created_at,"%m") BETWEEN "01" AND "03" THEN "1"
    WHEN DATE_FORMAT(users.created_at,"%m") BETWEEN "04" AND "06" THEN "2"
    WHEN DATE_FORMAT(users.created_at,"%m") BETWEEN "07" AND "09" THEN "3"
    ELSE "4"
  END,
  "四半期"
) AS "対象",

こんな感じです。
他の区切りかたも、リクエストがあれば追記しようと思います。

集計期間を制限する

場合によっては2019年以降のデータのみ欲しい、なんてこともあるでしょう。
そういう場合はこうします。

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

WHERE 句が追加されているのがわかると思います。
この仕組みがわかると、WHERE 句のアレンジで以下のようなことも簡単です。

月曜日のデータだけ集計したい

WHERE DATE_FORMAT(users.created_at,"%w") = "1"

お昼休み（12:00 - 12:59）のデータだけ集計して、傾向を比較したい

WHERE DATE_FORMAT(users.created_at,"%H") >= "12"

男性のデータだけ集計したい

WHERE users.gender = "男"

と、こんな感じです。

まとめと次回

今回はデータビジュアライゼーションの基本となる期間区切りの実装を通じてGROUP 句とWHERE 句のイメージをつけました。

次回は、

・男性と女性を同時に集計して比較するにはどうしたらいいんだ？
・LTVとか出してみたい

そんなあたりに突っ込んでみたいと思います。