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件以上対象レコードがあり、集計結果に歯抜けが出ない場合


日次

SELECT 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


参考

https://qiita.com/yskur/items/4e114a8143486914870c