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

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

関連記事

Safariでページを大回転させるブックマークレット

Safari限定ですが、WebKit transformationを使ってページの表示角度を変えられるんだそうです。 Ajaxianに出てた。Safariブラウザだけの機能。 -webkit-tran …

no image

AmazonのアソシエイトID入りで一番短いURL

via: AmazonはTwitterで自由にアフィリエイトして良いとは言ってないっぽい [C!] の、続きっぽくなっちゃいますが、TwitterでアフィリエイトURLをツイートするときに一番問題なの …

「TwitterFollowBadge」がIEで見れない件の対応方法

なんか、近くで嘆きの声が聞こえた気がするので(w)さらっと書いときます。 TwitterFollowBadgeっていう、ブログのサイド(右か左)にはれるTwitter用のブログパーツがあるんだけど、こ …

no image

AmazonのURLを短くするブックマークレット

今日、はてブ経由でこんな豆知識を仕入れました。 もともとAmazonの商品ページは、書籍であれば10けたのISBNコード、それ以外の製品もASINの10けた番号さえあればアクセス可能だ。 via: 3 …

タブ開き過ぎの人用のFirefox拡張「Tab mix Plus」で「FaviconizeTab」みたいなミニマム表示を実現

Firefoxの拡張といえば、タブ拡張なんかはメジャーな方だと思うんだけど、色々入れてると機能がコンフリクトしちゃってうまく動かなくなったりしますよね。ボクは基本的には「Tab Mix Plus」しか …

スポンサードリンク

スポンサードリンク