37
33

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Last updated at Posted at 2019-06-12

前提:テーブル構成

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

参考

37
33
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
37
33

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?