如何高效实现SQL滑动5分钟时间窗口的payload求和？

针对几十万条数据且无法调整索引的场景，滑动窗口函数是比构造时间范围表关联更高效的原生方案——它不需要生成笛卡尔积式的重复数据，仅通过单次有序遍历即可完成计算。以下分主流SQL方言给出实现：

PostgreSQL 实现 #

PostgreSQL原生支持基于时间间隔的RANGE滑动窗口，直接适配需求：

基于每条记录的滑动窗口（窗口起始为数据实际dt） #

SELECT
  dt AS window_start,
  dt + INTERVAL '5 minutes' AS window_end,
  SUM(payload) OVER (
    ORDER BY dt
    RANGE BETWEEN CURRENT ROW AND INTERVAL '5 minutes' FOLLOWING
  ) AS payload_sum
FROM #tmstmp
ORDER BY dt;

严格分钟粒度的滑动窗口（窗口起始为整点整分，如12:00、12:01） #

如果需要窗口严格按每分钟起始，先将时间向下取整到分钟：

SELECT
  date_trunc('minute', dt) AS window_start,
  date_trunc('minute', dt) + INTERVAL '5 minutes' AS window_end,
  SUM(payload) OVER (
    ORDER BY date_trunc('minute', dt)
    RANGE BETWEEN CURRENT ROW AND INTERVAL '5 minutes' FOLLOWING
  ) AS payload_sum
FROM #tmstmp
GROUP BY date_trunc('minute', dt)
ORDER BY window_start;

MySQL 8.0+ 实现 #

MySQL 8.0及以上支持窗口函数，需将时间转换为UNIX时间戳（秒级）来使用RANGE窗口：

基于每条记录的滑动窗口 #

SELECT
  dt AS window_start,
  DATE_ADD(dt, INTERVAL 5 MINUTE) AS window_end,
  SUM(payload) OVER (
    ORDER BY UNIX_TIMESTAMP(dt)
    RANGE BETWEEN CURRENT ROW AND 300 FOLLOWING  -- 5分钟=300秒
  ) AS payload_sum
FROM #tmstmp
ORDER BY dt;

严格分钟粒度的滑动窗口 #

SELECT
  DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS window_start,
  DATE_ADD(DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00'), INTERVAL 5 MINUTE) AS window_end,
  SUM(payload) OVER (
    ORDER BY UNIX_TIMESTAMP(DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00'))
    RANGE BETWEEN CURRENT ROW AND 300 FOLLOWING
  ) AS payload_sum
FROM #tmstmp
GROUP BY DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00')
ORDER BY window_start;

SQL Server 实现 #

SQL Server支持基于日期函数的RANGE滑动窗口：

基于每条记录的滑动窗口 #

SELECT
  dt AS window_start,
  DATEADD(MINUTE, 5, dt) AS window_end,
  SUM(payload) OVER (
    ORDER BY dt
    RANGE BETWEEN CURRENT ROW AND DATEADD(MINUTE, 5, dt) FOLLOWING
  ) AS payload_sum
FROM #tmstmp
ORDER BY dt;

严格分钟粒度的滑动窗口 #

SELECT
  DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt), 0) AS window_start,
  DATEADD(MINUTE, 5, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt), 0)) AS window_end,
  SUM(payload) OVER (
    ORDER BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt), 0)
    RANGE BETWEEN CURRENT ROW AND DATEADD(MINUTE, 5, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt), 0)) FOLLOWING
  ) AS payload_sum
FROM #tmstmp
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, dt), 0)
ORDER BY window_start;

补全空窗口的优化方案（如需无数据的窗口也显示） #

如果要求即使某分钟没有数据，也要生成对应窗口（如12:00-12:05即使无数据也要返回0），可以先生成时间维度表，再结合聚合后的滑动求和：

以PostgreSQL为例：

WITH time_windows AS (
  -- 生成从最早数据到最晚数据的所有分钟级窗口
  SELECT generate_series(
    date_trunc('minute', MIN(dt)),
    date_trunc('minute', MAX(dt)),
    INTERVAL '1 minute'
  ) AS window_start
  FROM #tmstmp
),
minute_agg AS (
  -- 按分钟聚合原始数据，减少后续计算量
  SELECT
    date_trunc('minute', dt) AS window_start,
    SUM(payload) AS minute_sum
  FROM #tmstmp
  GROUP BY date_trunc('minute', dt)
)
SELECT
  tw.window_start,
  tw.window_start + INTERVAL '5 minutes' AS window_end,
  COALESCE(SUM(ma.minute_sum) OVER (
    ORDER BY tw.window_start
    RANGE BETWEEN CURRENT ROW AND INTERVAL '5 minutes' FOLLOWING
  ), 0) AS payload_sum
FROM time_windows tw
LEFT JOIN minute_agg ma ON tw.window_start = ma.window_start
ORDER BY tw.window_start;

性能说明 #

滑动窗口函数通过对有序数据集的单次遍历完成计算，避免了构造时间表关联带来的笛卡尔积爆炸，即使几十万条数据也能高效运行。如果dt列已有索引，性能会进一步提升，但即使无索引，也远优于关联法。

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