以前PostgreSQLで日次・週次・月次集計する記事を投稿しました。
それのBigQuery版です。(StandardSQLを用います)
テストデータ
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