PostgreSQLで日次、週次、月次の集計をする

前提：テーブル構成

daily_active_users

date	user_id
2019-01-01	100
2019-01-02	100
2019-01-02	111
2019-01-03	100
2019-01-03	111
2019-01-04	100
2019-01-05	100

各期間ごとに必ずデータがある場合

※日次なら毎日1件以上対象レコードがあり、集計結果に歯抜けが出ない場合

日次

date型の場合

SELECT date,
       COUNT(DISTINCT user_id) AS dau
FROM daily_active_users
GROUP BY date
ORDER BY date

timestamp型の場合

日付に変換してから利用します。

SELECT to_char(created_at, 'YYYY/MM/dd') as date,
       COUNT(DISTINCT user_id) AS dau
FROM daily_active_users
GROUP BY date
ORDER BY date

週次：日曜始まり

date_part('dow', date) で曜日の数字を取得し、 date から引くことで日曜日に変換しています。

SELECT date - INTERVAL '1 day' * date_part('dow', date) AS week,
       COUNT(DISTINCT user_id) AS wau
FROM daily_active_users
GROUP BY week
ORDER BY week

月次

SELECT to_char(date, 'YYYY-MM') AS month,
       COUNT(DISTINCT user_id) AS mau
FROM daily_active_users
GROUP BY month
ORDER BY month

データが無い期間がある場合

上のSQLだとアクセスがない期間がある場合、結果が歯抜けになります。
※1/2のデータがなかった例

date	dau
2019-01-01	10
2019-01-03	20
2019-01-04	10

これを回避するために日次、週次、月次などのマスターデータを用意してそこにjoinしていきます。

日次のマスターを作る

SELECT generate_series AS from_date,
       generate_series + '1 days'::interval AS to_date
FROM generate_series('2019-1-1'::TIMESTAMP, '2019-1-3'::TIMESTAMP, '1 days')

from_date	to_date
2019-01-01 00:00	2019-01-02 00:00
2019-01-02 00:00	2019-01-03 00:00
2019-01-03 00:00	2019-01-04 00:00

1 days の部分を変更すれば週次、月次だけでなくx日毎なども作成可能です。

日次

SELECT from_date,
       COUNT(DISTINCT user_id) as dau
FROM
  (SELECT generate_series AS from_date,
          generate_series + '1 day'::interval AS to_date
   FROM generate_series('2019-1-1'::TIMESTAMP, '2019-1-3'::TIMESTAMP, '1 day')) AS time_ranges
LEFT JOIN daily_active_users
  ON from_date <= daily_active_users.date
     AND daily_active_users.date < to_date
GROUP BY from_date

週次

※開始日（例では2019-1-1）から7日毎になるため、日曜始まりにする場合は、開始日を日曜に指定してください

SELECT from_date,
       COUNT(DISTINCT user_id) as wau
FROM
  (SELECT generate_series AS from_date,
          generate_series + '7 days'::interval AS to_date
   FROM generate_series('2019-1-1'::TIMESTAMP, '2019-2-1'::TIMESTAMP, '7 days')) AS time_ranges
LEFT JOIN daily_active_users
  ON from_date <= daily_active_users.date
     AND daily_active_users.date < to_date
GROUP BY from_date

月次

SELECT from_date,
       COUNT(DISTINCT user_id) as mau
FROM
  (SELECT generate_series AS from_date,
          generate_series + '1 month'::interval AS to_date
   FROM generate_series('2019-1-1'::TIMESTAMP, '2019-3-1'::TIMESTAMP, '1 month')) AS time_ranges
LEFT JOIN daily_active_users
  ON from_date <= daily_active_users.date
     AND daily_active_users.date < to_date
GROUP BY from_date

参考