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

BigQueryで集計

データソース

created_at にTIMESTAMP型で何かしらのデータの作成日が入っている想定です。

created_at(TIMESTAMP)	user_id(INT)
2022-01-02 14:13:30.801506 UTC	1
2022-01-03 12:13:30.801506 UTC	2
2022-01-04 11:13:30.801506 UTC	1

日次で集計

UU数で出すように DISTINCT を付けていますが適宜外してください。

SELECT
  CAST(created_at AS DATE) day,
  COUNT(DISTINCT user_id)
FROM
  posts
GROUP BY
  day
ORDER BY
  day

週次で集計

週次の集計は DATE_TRUNC 関数で WEEK を指定し日付の丸め込みを行います。

cloud.google.com

日曜始まりで週次集計する場合

SELECT
  DATE_TRUNC(CAST(created_at AS DATE), WEEK) AS week,
  COUNT(DISTINCT company_id)
FROM
  posts
GROUP BY
  week
ORDER BY
  week

月曜始まりで週次集計する場合

SELECT
  DATE_TRUNC(CAST(created_at AS DATE), WEEK(MONDAY)) AS week,
  COUNT(DISTINCT company_id)
FROM
  posts
GROUP BY
  week
ORDER BY
  week

月次で集計する場合

SELECT
  DATE_TRUNC(CAST(created_at AS DATE), MONTH) AS month,
  COUNT(DISTINCT company_id)
FROM
  posts
GROUP BY
  month
ORDER BY
  month