なにこれ
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関数も使えます。