お知らせ
2023.12.5(Tue)
目次
こんにちは、飯塚です。
時系列データを分析するときに、周期性に注目することがありますよね。そんなときに役立つ、PostgreSQLを使った時間帯別、曜日別集計のSQLをまとめました。
時間帯別に集計すると、どの時間帯にデータが集中するのかが分かります。
SQL
--時間帯別に、作成されたアカウント数を集計する SELECT '00:00' + interval '1 hours' * full_hours_num AS 時間帯 , count(t.id) AS アカウント数 FROM generate_series(0, 23) full_hours_num LEFT JOIN account t ON full_hours_num = date_part('hour', created_at) and date_part('dow', created_at) = 0 GROUP BY 時間帯 , date_part('hour', created_at) ORDER BY 時間帯;
ID | created_at | |
---|---|---|
1 | 2023-04-01 01:02:00 | aaa@sample.com |
2 | 2023-07-14 02:17:00 | bbb@sample.com |
3 | 2023-10-22 02:28:00 | ccc@sample.com |
実行結果
時間帯 | アカウント数 |
---|---|
00:00:00 | 0 |
00:01:00 | 1 |
00:02:00 | 2 |
… | … |
23:00:00 | 0 |
自然現象なら曜日は関係ないですが、人が関わるデータであれば平日と土日では大きく差が出ることが多いです。例えばログイン頻度の傾向を見るなら、曜日の観点も必要ですよね。
SQL
WITH 時間帯テーブル AS ( SELECT full_hours_num , '00:00' + interval '1 hour' * full_hours_num AS 時間帯 FROM generate_series(0, 23) full_hours_num ) , 曜日テーブル AS ( SELECT generate_series(0, 6) AS 曜日番号 ) , 全組み合わせ AS ( SELECT 時間帯テーブル.full_hours_num , 時間帯テーブル.時間帯 , 曜日テーブル.曜日番号 FROM 時間帯テーブル CROSS JOIN 曜日テーブル ) , 時間帯別曜日別アカウント数 AS ( SELECT 全組み合わせ.時間帯 , 全組み合わせ.曜日番号 , COUNT(unit_member.id) AS アカウント数 FROM 全組み合わせ LEFT JOIN unit_member ON 全組み合わせ.full_hours_num = date_part('hour', account.created_at) AND 全組み合わせ.曜日番号 = date_part('dow', account.created_at) GROUP BY 全組み合わせ.時間帯 , 全組み合わせ.曜日番号 ORDER BY 曜日番号 , 時間帯 ) SELECT 時間帯テーブル.時間帯 , 月曜日データ.アカウント数 AS 月 , 火曜日データ.アカウント数 AS 火 , 水曜日データ.アカウント数 AS 水 , 木曜日データ.アカウント数 AS 木 , 金曜日データ.アカウント数 AS 金 , 土曜日データ.アカウント数 AS 土 , 日曜日データ.アカウント数 AS 日 FROM 時間帯テーブル LEFT JOIN (SELECT 時間帯, アカウント数 FROM 時間帯別曜日別アカウント数 WHERE 曜日番号 = 1) 月曜日データ ON 時間帯テーブル.時間帯 = 月曜日データ.時間帯 LEFT JOIN (SELECT 時間帯, アカウント数 FROM 時間帯別曜日別アカウント数 WHERE 曜日番号 = 2) 火曜日データ ON 時間帯テーブル.時間帯 = 火曜日データ.時間帯 LEFT JOIN (SELECT 時間帯, アカウント数 FROM 時間帯別曜日別アカウント数 WHERE 曜日番号 = 3) 水曜日データ ON 時間帯テーブル.時間帯 = 水曜日データ.時間帯 LEFT JOIN (SELECT 時間帯, アカウント数 FROM 時間帯別曜日別アカウント数 WHERE 曜日番号 = 4) 木曜日データ ON 時間帯テーブル.時間帯 = 木曜日データ.時間帯 LEFT JOIN (SELECT 時間帯, アカウント数 FROM 時間帯別曜日別アカウント数 WHERE 曜日番号 = 5) 金曜日データ ON 時間帯テーブル.時間帯 = 金曜日データ.時間帯 LEFT JOIN (SELECT 時間帯, アカウント数 FROM 時間帯別曜日別アカウント数 WHERE 曜日番号 = 6) 土曜日データ ON 時間帯テーブル.時間帯 = 土曜日データ.時間帯 LEFT JOIN (SELECT 時間帯, アカウント数 FROM 時間帯別曜日別アカウント数 WHERE 曜日番号 = 0) 日曜日データ ON 時間帯テーブル.時間帯 = 日曜日データ.時間帯;
ID | created_at | |
---|---|---|
1 | 2023-04-01 01:02:00 | aaa@sample.com |
2 | 2023-07-14 02:17:00 | bbb@sample.com |
3 | 2023-10-27 02:28:00 | ccc@sample.com |
実行結果
時間帯 | 月 | 火 | 水 | 木 | 金 | 土 | 日 |
---|---|---|---|---|---|---|---|
00:01:00 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
00:02:00 | 0 | 0 | 0 | 0 | 2 | 0 | 0 |
… | … | … | … | … | … | … | … |
23:00:00 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
いかがでしょうか。
意外と最初にSQLを組んだときには時間がかかったので、読んでいただいた方のお役に立てれば幸いです。
A5M2でSQLを実行した結果をExcelに張り付けて、Excelのヒートマップを使うと一目でデータの傾向が見れるのでおすすめです。
《関連記事》