Creazy!

WEBエンジニア・ヤガーのテック・ガジェットブログ

SQL 小技集

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

投稿日:


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

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

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

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

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

他にもヒートマップは、サイト上のどのあたりがよくクリックされるかなどでも使われますね。
で、この曜日と時間で2軸を作る分析はデータベースを持っていればよく使えるものだと思うのですが、意外と簡単に出す方法が思いつかないようで、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句を追加して調整しましょう。

§

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

-SQL, 小技集
-,

執筆者:


comment

メールアドレスが公開されることはありません。

関連記事

PostToTwitter v2.0.0公開:自分好みのフォーマットでブログ更新をお知らせ

ボクはこのブログ(MT)の更新通知をTwitterに送るのにエムロジックさんのPostToTwitterプラグインを使わせてもらっています。先日MT5にアップグレードしてからはv1.3.0を使っていた …

BingをGoogle Analyticsで検索エンジンとして認識させる方法

Microsoftが満を持して(?)投入してきた次世代検索サービス「Bing」ですが、全然ダメって言われたり、エロいって言われたり、どちらかというと評判よくなさそうな感じですね。 6月1日にプレビュー …

Deliciousの被ブックマーク推移グラフを表示するURL見つけた

ボクは普段ソーシャルブックマークサービスはDeliciousを使っているんですが、ブログ用にブクマ数やグラフ、タグなんかを表示できるTagometerというブログパーツが用意されています。 With …

no image

第7回:サムネイル作成APIをJavaScriptで簡単に切り替える方法

SimpleAPIをはじめたくさんのサムネイルAPIサービス(もちろん無料)のおかげで、簡単にサイトのスクリーンショットをサムネイル画像として自分のブログに貼り付ける事ができるようになりました。すでに …

Skypeチャットの隠しコマンド /htmlhistory とBookmarkletで簡単レポート作成術

プライベートと仕事の両方で Skype はかなり前から使っていますが、チャットの内容をどうにか別のファイルにエクスポートしたいと思いました。 Skypeチャットは設定で過去の履歴も保存・表示できるので …

スポンサードリンク

スポンサードリンク