SQLite如何实现10小时滚动窗口SUM聚合查询？

可行性说明 #

完全可以通过窗口函数实现该需求，SQLite 3.25.0及以上版本 原生支持标准窗口语法，无需额外扩展即可完成10小时滚动窗口的SUM聚合计算。

核心注意点 #

SQLite的窗口RANGE边界仅支持整数数值比较，因此需要先将DATETIME类型的Time字段转换为Unix时间戳（秒级整数），10小时对应的秒数为36000，以此作为窗口范围的计算依据。

具体实现代码 #

窗口函数方案（性能最优） #

SELECT
  Time,
  Value,
  SUM(Value) OVER (
    -- 按时间戳升序排序，保证时间更早的行排在窗口前列
    ORDER BY strftime('%s', Time)
    -- 窗口范围：当前行时间往前推10小时 到 当前行时间
    RANGE BETWEEN 36000 PRECEDING AND CURRENT ROW
  ) AS SumValueLast10Hour
FROM Ticks
ORDER BY Time;

语法说明：

• strftime('%s', Time)：将DATETIME类型的时间转换为Unix时间戳，单位为秒
• RANGE BETWEEN 36000 PRECEDING AND CURRENT ROW：定义窗口包含所有时间落在「当前行时间往前10小时」到「当前行时间」区间内的所有行，即使存在时间完全相同的行也能正确纳入计算，比基于行号的ROWS窗口更适合时间滚动场景。

兼容低版本SQLite的替代方案 #

如果使用的SQLite版本低于3.25不支持窗口函数，可以用关联子查询实现，兼容性更好但数据量大时性能低于窗口函数方案：

SELECT
  t1.Time,
  t1.Value,
  (
    SELECT SUM(t2.Value)
    FROM Ticks t2
    WHERE t2.Time BETWEEN DATETIME(t1.Time, '-10 hours') AND t1.Time
  ) AS SumValueLast10Hour
FROM Ticks t1
ORDER BY t1.Time;

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