何曜日の何時ころがピークなのか分かりやすいヒートマップ的な分布を出すSQL

投稿日：2022年8月11日

ご無沙汰しております。ヤガーです。

すっかりエンジニア的な投稿をしなくなってしまったんですが、食っていかないといけないわけでずっとプログラム書き続けております(笑)

昨今ではプログラミングはどんどん高度化され、データベースの操作は全てGUIだったりフレームワークのORMだったりでSQL書ける人が私も含む老害ばかりという悲しい状況になっておりますが、久しぶりにそれすごいですねと言われたので紹介しておきます。

サービス分析によく使われるヒートマップ

サービス運営をしていて分析などする場合、ヒートマップというグラフを使うことがあります。
例えば、Google Analyticsで曜日と時間でどの時間帯にアクセスが多いのかというのがわかりやすくなります。

他にもヒートマップは、サイト上のどのあたりがよくクリックされるかなどでも使われますね。
で、この曜日と時間で２軸を作る分析はデータベースを持っていればよく使えるものだと思うのですが、意外と簡単に出す方法が思いつかないようで、SQLで出す方法をご紹介します。

datetime型のカラムを曜日・時間で集計するSQL

もったいぶっても仕方ないのでいきなり正解を書いておきますね。
ちなみにMySQLでの実現方法です。

SELECT
  DATE_FORMAT(created,'%H') as `時間`
, SUM( CASE WHEN DATE_FORMAT(created,'%w') = 0 THEN 1 ELSE 0 END ) as `日曜`
, SUM( CASE WHEN DATE_FORMAT(created,'%w') = 1 THEN 1 ELSE 0 END ) as `月曜`
, SUM( CASE WHEN DATE_FORMAT(created,'%w') = 2 THEN 1 ELSE 0 END ) as `火曜`
, SUM( CASE WHEN DATE_FORMAT(created,'%w') = 3 THEN 1 ELSE 0 END ) as `水曜`
, SUM( CASE WHEN DATE_FORMAT(created,'%w') = 4 THEN 1 ELSE 0 END ) as `木曜`
, SUM( CASE WHEN DATE_FORMAT(created,'%w') = 5 THEN 1 ELSE 0 END ) as `金曜`
, SUM( CASE WHEN DATE_FORMAT(created,'%w') = 6 THEN 1 ELSE 0 END ) as `土曜`
FROM reports
GROUP BY `時間`
ORDER BY `時間` ASC;

そうすると下記のような結果を取得できました。

SQLで直接グラフは出せませんので、数字で集計されていますが、これを元にスプレッドシートなりHTMLなりで色付けすればいいことは分かりますね。

SQL解説

MySQLなので日付をフォーマットする DATE_FORMAT 関数を使います。
今回起点にしているのは reports というテーブルの登録日時を入れている created というカラムです。
恐らくどんなテーブルにも created や create_date のようなカラムを用意していると思うのでそれを使うイメージです。

まず、 DATE_FORMAT(created,'%H') で時間ごとにグループ化することは割と簡単に思いつくと思います。
それと同時に曜日のカウントをどうするかなんですが、CASE文で曜日を判断し、特定の曜日だけカウントアップするカラムを日〜土まで用意してあげればいいのです。

ちなみに、PostgreSQLだと日付フォーマットの関数が TO_CHAR になるのでそこだけ差し替えれば使い回せますね。

今回は全データを参考にしていますが、短い期間の集計をする場合、日曜から土曜日までの区切りで期間設定してあげないと曜日によって集計回数が異なってしまう場合があるので、WHERE句を追加して調整しましょう。

誰かのお役に立つことを祈って。

執筆者：yager