なにこれ
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日戻して月曜に統一するという処理を施しています。
週番号を抽出する
extract(isodow FROM created_at)
週番号(月曜:1〜日曜:7)を抽出します。週番号を修正する
cast(extract(isodow FROM created_at) as int) - 1
月曜は動かしたくないので月曜:0〜日曜:6にするためにその後1を引いています。INTERVAL型に変換する
concat(cast(extract(isodow FROM created_at) as int) - 1, 'days')::INTERVAL
'4 days'や'6 days'とするために、'days'と結合させ、INTERVAL型に変換しています。created_atからINTERVAL型を引いてdailyと同じ要領で集計する
(created_at - 3で作成したもの)::date
こちらではdate型に変換することでdaily集計を行なっていますが、上記同様to_char関数も使えます。