如何在BigQuery中计算特定时间范围的滚动平均值？

针对你这种记录频率不固定的表，不能用固定行数的滚动窗口，正确的做法是利用BigQuery支持的时间范围窗口函数，直接基于时间区间筛选数据计算平均值，无需手动用LAG或TIMESTAMP_SUB逐个筛选。

实现代码 #

假设你的表名为your_project.your_dataset.your_table，计算每条记录对应过去12小时内所有value的平均值的SQL如下：

SELECT
  timestamp,
  value,
  -- 计算过去12小时的滚动平均值
  AVG(value) OVER (
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL 12 HOUR PRECEDING AND CURRENT ROW
  ) AS rolling_12h_avg
FROM
  `your_project.your_dataset.your_table`
ORDER BY timestamp;

关键说明 #

1. 窗口范围定义：RANGE BETWEEN INTERVAL 12 HOUR PRECEDING AND CURRENT ROW是核心，它会自动匹配当前行timestamp往前推12小时的所有记录，不管这段时间内有多少条数据，完全适配记录频率不固定的场景。
2. 排序要求：ORDER BY timestamp必须存在，确保窗口是按照时间顺序来计算的，避免出现时间乱序导致的错误结果。
3. 对比LAG方法：LAG只能取固定行数的前N条数据，无法动态适配时间范围，比如记录密集时可能只覆盖很短时间，稀疏时又会覆盖超出范围的数据，因此不适合你的场景。

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