如何在BigQuery中使用SQL实现每5秒的数据聚合？

数据表结构与示例数据 #

期望输出 #

方案一：使用TIMESTAMP_TRUNC函数（常规时间截断方案） #

利用BigQuery内置的TIMESTAMP_TRUNC函数直接将时间戳截断到5秒间隔，再通过GROUP BY汇总数量：

SELECT
  TIMESTAMP_TRUNC(created_at, SECOND, INTERVAL 5 SECOND) AS timestamp_interval_5sec,
  SUM(quantity) AS total_quantity
FROM
  `your-project.your-dataset.your-table`
GROUP BY
  timestamp_interval_5sec
ORDER BY
  timestamp_interval_5sec;

说明：

• TIMESTAMP_TRUNC(created_at, SECOND, INTERVAL 5 SECOND) 指定将created_at按5秒为单位截断，生成每个记录所属的5秒区间起始时间
• 按截断后的时间区间分组，汇总该区间内的quantity总和

方案二：结合PARTITION BY与GROUP BY实现 #

通过时间戳的秒数计算确定5秒区间，同时使用PARTITION BY按日期分区（可优化大表查询性能），再通过GROUP BY汇总：

SELECT
  timestamp_interval_5sec,
  SUM(quantity) AS total_quantity
FROM (
  SELECT
    quantity,
    -- 计算当前时间戳所属的5秒区间起始时间
    TIMESTAMP_SECONDS(UNIX_SECONDS(created_at) - MOD(UNIX_SECONDS(created_at), 5)) AS timestamp_interval_5sec,
    DATE(created_at) AS date_part
  FROM
    `your-project.your-dataset.your-table`
)
GROUP BY
  date_part, timestamp_interval_5sec
-- 按日期分区，提升大表查询效率
PARTITION BY
  date_part
ORDER BY
  timestamp_interval_5sec;

说明：

1. 子查询中通过UNIX_SECONDS将时间戳转换为秒数，用MOD计算当前秒数与5的余数，减去余数后得到5秒区间的起始秒数，再转回时间戳
2. 外层查询按日期（date_part）分区，同时按5秒区间分组，汇总数量总和
3. PARTITION BY在这里主要用于优化大表查询，让BigQuery按日期并行处理数据，也可以根据实际需求调整分区维度（如小时）

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