PostgreSQLで時系列データを分析！ 時間帯・曜日別に集計するSQL

こんにちは、飯塚です。
 
時系列データを分析するときに、周期性に注目することがありますよね。そんなときに役立つ、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
    時間帯;

実行結果

時間帯・曜日別にデータを集計する

自然現象なら曜日は関係ないですが、人が関わるデータであれば平日と土日では大きく差が出ることが多いです。例えばログイン頻度の傾向を見るなら、曜日の観点も必要ですよね。
 
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 時間帯テーブル.時間帯 = 日曜日データ.時間帯;

実行結果

まとめ

いかがでしょうか。
 
意外と最初にSQLを組んだときには時間がかかったので、読んでいただいた方のお役に立てれば幸いです。
 
A5M2でSQLを実行した結果をExcelに張り付けて、Excelのヒートマップを使うと一目でデータの傾向が見れるのでおすすめです。
 
 
 
 
《関連記事》

技術

2022.10.14(Fri)

PostgreSQLで連番を作るGENERATE_SERIES関数を使ってみた

#SQL

技術

2022.8.24(Wed)

PostgreSQLで検索結果を結合する！ INTERSECTとEXCEPTを使ってみた

#SQL

技術

2022.7.20(Wed)

PostgreSQLで検索結果を結合する！ UNIONを使ってみた

#SQL