在BigQuery中计算多时间间隔的聚合数据（15分钟/1小时/日）

核心思路 #

利用BigQuery的TIMESTAMP_TRUNC函数，将activity_time截断到目标时间粒度（15分钟/1小时），结合指定日期过滤后分组计算聚合指标。

1. 15分钟时间间隔聚合SQL #

SELECT
  activity,
  userid,
  TIMESTAMP_TRUNC(activity_time, MINUTE, INTERVAL 15 MINUTE) AS interval_start, -- 截断到15分钟周期的起始时间
  SUM(value) AS total_value,
  MIN(value) AS min_value,
  MAX(value) AS max_value
FROM
  `your-project.your-dataset.your-table` -- 替换为你的实际表路径
WHERE
  activity_date = '2023-02-26' -- 替换为你要查询的指定日期
GROUP BY
  activity, userid, interval_start
ORDER BY
  interval_start;

2. 1小时时间间隔聚合SQL #

SELECT
  activity,
  userid,
  TIMESTAMP_TRUNC(activity_time, HOUR) AS interval_start, -- 截断到小时周期的起始时间
  SUM(value) AS total_value,
  MIN(value) AS min_value,
  MAX(value) AS max_value
FROM
  `your-project.your-dataset.your-table` -- 替换为你的实际表路径
WHERE
  activity_date = '2023-02-26' -- 替换为你要查询的指定日期
GROUP BY
  activity, userid, interval_start
ORDER BY
  interval_start;

补充说明 #

• 若activity_time是字符串类型，需先转换为TIMESTAMP：TIMESTAMP(activity_time)
• 如需同时输出多粒度结果，可通过UNION ALL合并两个查询，并添加粒度标识字段区分

内容的提问来源于stack exchange，提问作者Awssylearn