SQL查询问题：如何按过去5分钟间隔对数据求平均？

场景1：滑动窗口（每个时间点对应的过去5分钟平均值） #

如果你需要对每一条数据，计算其当前时间往前推5分钟内所有数据的平均值，可以用窗口函数的滑动窗口实现：

SELECT
  timestamp,
  value,
  -- 计算当前行及过去5分钟内所有数据的平均值
  AVG(value) OVER (
    ORDER BY timestamp
    RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
  ) AS rolling_5min_avg
FROM metrics
ORDER BY timestamp;

原理：RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW 会为每个时间点划定一个从当前时间往前5分钟的窗口，自动聚合窗口内的所有数据求平均，避免了错误的分组逻辑。

场景2：固定5分钟区间（每个区间覆盖连续5分钟的数据） #

如果你需要按每5分钟为一个固定区间，计算该区间内的平均值（比如10:00-10:05、10:05-10:10等），修正后的分组逻辑应该将数据归入其所属的区间，而非下一个5分钟节点：

SELECT
  -- 生成每个数据所属的5分钟区间起始时间
  DATE_TRUNC('minute', timestamp) + INTERVAL '5 minutes' * (EXTRACT(minute FROM timestamp)::integer / 5) AS window_start,
  window_start + INTERVAL '5 minutes' AS window_end,
  AVG(value) AS avg_value
FROM metrics
GROUP BY window_start, window_end
ORDER BY window_start;

错误逻辑分析 #

你的原始查询可能使用了CEIL或类似向上取整的逻辑，将数据强行归入下一个5分钟节点（比如10:03的数据被分到10:05的区间），导致聚合的不是该数据所属的过去5分钟范围。上面的修正方案通过DATE_TRUNC和整数除法，准确将数据映射到其所在的5分钟区间，或者用滑动窗口直接计算时间范围内的平均值。

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