BigQueryで任意のintervalごとの集計を行う
仕組みとしては, unixtimeを使用してテーブルの各行のtimestampをtime_intervalで割った商を計算し, それごとにグループを取る感じ.
以下のSQLで実現できる. 処理データ量がかなり大きくなってるかもしれない.
code:sql
SELECT
TIMESTAMP_SECONDS(CAST(UNIX_SECONDS(timestamp) / {interval_seconds} AS INT64) * {interval_seconds}) as interval_start_datetime,
TIMESTAMP_SECONDS(CAST(UNIX_SECONDS(timestamp) / {interval_seconds} AS INT64) * {interval_seconds} + {interval_seconds}) as interval_end_datetime,
count(distinct(CONCAT(person_id, CAST(UNIX_SECONDS(timestamp) / {interval_seconds} AS INT64)))) as num_people
FROM {os.environ['GOOGLE_PROJECT_ID']}.{database_name}.{table_name}
WHERE timestamp BETWEEN @start_time AND @end_time
GROUP BY interval_start_datetime, interval_end_datetime
ORDER BY interval_start_datetime