如何在SQL中实现带时间范围的SUM窗口函数并处理重复时间戳行

问题场景 #

我需要用SQL的SUM窗口函数计算60秒滚动总和，但遇到重复时间戳时，RANGE子句会将所有相同时间戳的行归为同一窗口，导致结果不符合逐行计算的预期。

尝试的SQL语句 #

SUM(volume) OVER (
    PARTITION BY ID
    ORDER BY td.timestamp
    RANGE BETWEEN INTERVAL '60' SECOND PRECEDING AND CURRENT ROW
) AS total_volume

核心问题 #

• 存在重复时间戳时，RANGE会将同时间戳的所有条目纳入同一窗口，无法实现逐行累加
• 要求同一时间戳内的行按顺序逐行计算滚动总和

约束条件 #

不能修改时间戳列（如添加微小差值），避免影响时间窗口的精确性

输入数据 #

当前错误结果 #

期望输出 #

解决方案 #

通过给同一时间戳内的行分配唯一序号，结合关联查询实现时间窗口内的逐行累加：

WITH ordered_rows AS (
    SELECT
        ID,
        timestamp,
        volume,
        -- 给同一ID、同一时间戳的行分配唯一序号
        ROW_NUMBER() OVER (PARTITION BY ID, timestamp ORDER BY (SELECT NULL)) AS row_num
    FROM your_table
)
SELECT
    o1.timestamp,
    o1.volume,
    SUM(o2.volume) AS RollVolume
FROM ordered_rows o1
JOIN ordered_rows o2
    ON o1.ID = o2.ID
    -- 限定60秒时间窗口
    AND o2.timestamp >= o1.timestamp - INTERVAL '60' SECOND
    -- 确保同时间戳内只累加当前行及之前的行
    AND (
        o2.timestamp < o1.timestamp
        OR (o2.timestamp = o1.timestamp AND o2.row_num <= o1.row_num)
    )
GROUP BY o1.ID, o1.timestamp, o1.row_num, o1.volume
ORDER BY o1.timestamp, o1.row_num;

逻辑说明 #

1. 先用ROW_NUMBER()给同一ID、同一时间戳的行分配唯一序号，解决同时间戳行的顺序问题
2. 通过自关联筛选出符合60秒时间窗口，且在当前行之前（含同时间戳内的前序行）的记录
3. 对筛选出的记录求和，得到逐行计算的滚动总和

如果需要排除时间窗口边界的行（如示例中08:01:00的行不纳入08:02:00的窗口），只需将时间条件改为AND o2.timestamp > o1.timestamp - INTERVAL '60' SECOND即可。

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