BigQueryで集計

データソース

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

日次で集計

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

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

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

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

※こちらの記事は自ブログからの転載です