なにこれ

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 - 作成したもの)::date
こちらではdate型に変換することでdaily集計を行なっていますが、上記同様to_char関数も使えます。