前提:テーブル構成
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
参考