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

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

関連記事

Re: 第3回:JavaScriptで画像サイズと合ったポップアップウィンドウを開く

[追記:2008/07/04] 更に改良したスクリプトをつくりましたので下記エントリーの方を参照して下さい。 ・Re2: 第3回:JavaScriptで画像サイズと合ったポップアップウィンドウを開く …

FacebookのOGPを簡単に確認するための5つの便利ツール

ご無沙汰しております、ヤガーです。 今までのFacebook関連のネタや、OGPに関する記事を書いてきましたが、さすがに企業サイトやブログなどではOGPの設定が浸透してきていると思います。OGPがある …

Twitter @Anywhereをブログのコメントシステムにする方法

Welcome to @Anywhere | dev.twitter.com もはや、WEB界隈ではコミュニケーションプラットフォームとして定着しつつあるTwitterですが、先週、自分のサイトにJa …

ブログに埋め込んだFlickr画像に自動でEXIF情報を追加する「FlickrEx」がLazyLoadに対応!

ボクは写真が好きで、バックアップも兼ねてFlickrに全ての写真データをアップロードしているのですが、ブログに貼り付ける画像もFlickrにアップロードしたものを使っています。プロアカウントを持ってい …

no image

PostgreSQLでCSVやTSV用に改行コードを取り除く方法

DBからCSV(カンマ区切り)やTSV(タブ区切り)でデータを出力するってことがたまにあるのですが、出力するデータに改行が入っているとCSVやTSVのフォーマットが崩れてしまってエクセルなどでうまく開 …

スポンサードリンク

スポンサードリンク