BigQueryで日次・週次・月次集計する

以前PostgreSQLで日次・週次・月次集計する記事を投稿しました。
それのBigQuery版です。(StandardSQLを用います)

blog.frogdusk.com

テストデータ

2019/1/1から90日にわたって、日時がばらばらなデータを10,000行用意します。

SELECT
  DATETIME_ADD(
    DATETIME_ADD(
      DATETIME('2019-01-01'),
      INTERVAL CAST(ROUND(RAND() * 90) AS INT64) DAY
    ),
    INTERVAL CAST(ROUND(RAND() * 24) AS INT64) HOUR
  ) AS created_at
FROM UNNEST(GENERATE_ARRAY(1,10000))

RAND関数でランダムな値を発生させ、それを日付や時間として扱える範囲内のINTに変換し、DATETIME_ADDを2回しています。

日次

テストデータでのcreated_atはDATETIME型です。
DATETIME型は時間情報まで持っているので、日付までしか持っていないDATE型に変換してしまいます。

WITH base_data AS (
  SELECT
    DATETIME_ADD(
      DATETIME_ADD(
        DATETIME('2019-01-01'),
        INTERVAL CAST(ROUND(RAND() * 90) AS INT64) DAY
      ),
      INTERVAL CAST(ROUND(RAND() * 24) AS INT64) HOUR
    ) AS created_at
  FROM UNNEST(GENERATE_ARRAY(1,10000))
)
SELECT
  DATE(created_at),
  count(*)
FROM
  base_data
GROUP BY
  1
ORDER BY
  1

週次

BigQueryの場合は、PostgreSQLに比べて簡単に週次集計を実現できます。

DATETIME_TRUNC, DATE_TRUNCという、指定した粒度で日時を切り詰める関数があります。

この関数でどの曜日でも週次になるよう切り詰めて集計することができます。

WITH base_data AS (
  SELECT
    DATETIME_ADD(
      DATETIME_ADD(
        DATETIME('2019-01-01'),
        INTERVAL CAST(ROUND(RAND() * 90) AS INT64) DAY
      ),
      INTERVAL CAST(ROUND(RAND() * 24) AS INT64) HOUR
    ) AS created_at
  FROM UNNEST(GENERATE_ARRAY(1,10000))
)
SELECT
  DATE_TRUNC(DATE(created_at), WEEK(MONDAY)),
  count(*)
FROM
  base_data
GROUP BY
  1
ORDER BY
  1

月次

週次の場合と同様に、月で切り詰めて集計することで実現できます。

また日次でも同様のことが可能ですが、 FORMAT関数で月だけString型として取り出すことでも実現できます。

WITH base_data AS (
  SELECT
    DATETIME_ADD(
      DATETIME_ADD(
        DATETIME('2019-01-01'),
        INTERVAL CAST(ROUND(RAND() * 90) AS INT64) DAY
      ),
      INTERVAL CAST(ROUND(RAND() * 24) AS INT64) HOUR
    ) AS created_at
  FROM UNNEST(GENERATE_ARRAY(1,10000))
)
SELECT
  DATE_TRUNC(DATE(created_at), MONTH),
  count(*)
FROM
  base_data
GROUP BY
  1
ORDER BY
  1