夕蛙のなく頃に

データアナリストとして学んだことや趣味で勉強し始めたIoTをアウトプットする

PostgreSQLで日次・週次・月次集計する

なにこれ

PostgreSQLでのtimestamp型から日次(daily)・週次(weekly)・月次(monthly)で集計するための変換方法です。

日次・月次はto_char関数で'YYYY-MM-DD'や'YYYY-MM'を出力すればいいのですが、週次が少し面倒です。

今回の週次は、月曜日起点で集計するようようにしています。

テストデータ

2019/1/1から2019/3/31まで日付・時刻がバラバラの値が10000個あるテーブルをWITH句で作成して使います。

SELECT
  '2019-01-01'::date
    + cast(((random() * 90)::int % 90)::text || ' days' AS INTERVAL)
    + cast(((random() * 24)::int % 24)::text || ' hours' AS INTERVAL) AS created_at
FROM generate_series(1,10000);

日次・月次

to_char関数を使います。

-- 日次
WITH base AS (
    SELECT '2019-01-01' :: DATE
           + cast(((random() * 90) :: INT % 90) :: TEXT || ' days' AS INTERVAL)
           + cast(((random() * 24) :: INT % 24) :: TEXT || ' hours' AS INTERVAL) AS created_at
    FROM generate_series(1, 10000)
)
SELECT
  to_char(created_at, 'YYYY-MM-DD') AS yyyymmdd,
  count(*)
FROM base
GROUP BY yyyymmdd
ORDER BY yyyymmdd

-- 月次
WITH base AS (
    SELECT '2019-01-01' :: DATE
           + cast(((random() * 90) :: INT % 90) :: TEXT || ' days' AS INTERVAL)
           + cast(((random() * 24) :: INT % 24) :: TEXT || ' hours' AS INTERVAL) AS created_at
    FROM generate_series(1, 10000)
)
SELECT
  to_char(created_at, 'YYYY-MM') AS yyyymm,
  count(*)
FROM base
GROUP BY yyyymm
ORDER BY yyyymm

週次

WITH base AS (
    SELECT '2019-01-01' :: DATE
           + cast(((random() * 90) :: INT % 90) :: TEXT || ' days' AS INTERVAL)
           + cast(((random() * 24) :: INT % 24) :: TEXT || ' hours' AS INTERVAL) AS created_at
    FROM generate_series(1, 10000)
)
SELECT
  (created_at - concat(cast(extract(isodow FROM created_at) as int) - 1, 'days')::INTERVAL)::date AS week,
  count(*)
FROM base
GROUP BY week
ORDER BY week

週次は少し毛色が違います。
月曜始まりにしたいので、火曜なら1日・金曜なら4日戻して月曜に統一するという処理を施しています。

  1. 週番号を抽出する
    extract(isodow FROM created_at)
    週番号(月曜:1〜日曜:7)を抽出します。

  2. 週番号を修正する
    cast(extract(isodow FROM created_at) as int) - 1
    月曜は動かしたくないので月曜:0〜日曜:6にするためにその後1を引いています。

  3. INTERVAL型に変換する
    concat(cast(extract(isodow FROM created_at) as int) - 1, 'days')::INTERVAL
    '4 days'や'6 days'とするために、'days'と結合させ、INTERVAL型に変換しています。

  4. created_atからINTERVAL型を引いてdailyと同じ要領で集計する
    (created - 作成したもの)::date
    こちらではdate型に変換することでdaily集計を行なっていますが、上記同様to_char関数も使えます。

参考ドキュメント

www.postgresql.jp