如何在SQL中通过窗口函数实现类似于Python Pandas的滚动计算

移动平均必须用ROWS BETWEEN显式定义窗口范围，因SQL无Pandas式自动rolling语法，默认UNBOUNDED PRECEDING AND CURRENT ROW是累积计算而非滚动；ROWS按物理行偏移确保“最近N行”准确，而RANGE在重复或非连续排序键下易漂移。

窗口函数的 ROWS BETWEEN 必须显式指定范围

SQL 没有 Pandas 那种自动识别 rolling(3) 的语法，所有滚动窗口都得靠 ROWS BETWEEN 或 RANGE BETWEEN 明确写出来。不写就默认是 UNBOUNDED PRECEDING AND CURRENT ROW，也就是从第一行累加到当前行——这其实是累积计算，不是滚动。

常见错误是直接套用 AVG(price) OVER (ORDER BY date)，结果发现值越算越大，根本不是最近3天的均值。

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 等价于 Pandas 的 .rolling(3).mean()
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING → 中心对齐的3点滑动窗口（类似卷积）
• 用 RANGE 要小心：如果时间字段有重复值，RANGE 可能吞掉多行，ROWS 更可控

ORDER BY 是强制要求，且必须匹配业务时间逻辑

没有 ORDER BY，窗口函数无法确定“前几行”是谁。但更隐蔽的问题是排序字段选错：比如用 id 排序代替 event_time，会导致滚动窗口按插入顺序而非业务时间滑动，数据含义完全失真。

如果存在并行事件（同一秒多个记录），建议组合排序：ORDER BY event_time, id，避免窗口行为不稳定。

立即学习“Python免费学习笔记（深入）”；

• MySQL 8.0+、PostgreSQL、SQL Server、BigQuery 都支持多字段 ORDER BY 窗口排序
• SQLite 3.25+ 支持窗口函数，但不支持 RANGE，只能用 ROWS
• 排序字段类型必须支持比较（不能是 JSON 或 BLOB）

NULL 值会中断滚动窗口计算，需提前处理

Pandas 的 .rolling().mean() 默认跳过 NaN，但 SQL 窗口函数中，只要窗口内任一 price 为 NULL，AVG() 就返回 NULL（除非你用 COALESCE 或过滤）。

这不是 bug，是 SQL 标准行为：聚合函数遇到 NULL 默认忽略参与计算，但若整个窗口都是 NULL 或无有效值，结果就是 NULL。

• 安全做法：AVG(COALESCE(price, 0)) OVER (...) —— 但注意：填 0 会扭曲均值
• 更合理：AVG(price) FILTER (WHERE price IS NOT NULL) OVER (...)（PostgreSQL）
• 通用兼容写法：AVG(CASE WHEN price IS NOT NULL THEN price END) OVER (...)

性能陷阱：大表上 ROWS BETWEEN 可能比预期慢

看起来只是“看前三行”，但数据库实际要为每一行重新扫描窗口内数据。当分区很大（比如百万级用户各自滚动）、又没索引时，执行计划可能退化成嵌套循环。

关键优化点不在函数本身，而在底层数据组织：

• 确保 ORDER BY 字段上有索引（如 (user_id, event_time) 复合索引）
• 避免在 SELECT 中对同一列多次调不同窗口（如同时算 rolling_avg 和 rolling_sum），可复用子查询或 CTE
• 某些引擎（如 ClickHouse）对 ROWS 窗口做了向量化优化，但 PostgreSQL 对超宽窗口仍较慢

滚动计算真正难的从来不是语法，而是确认“滚动单位”是否和业务一致——是按自然日？交易序号？还是严格时间戳？一旦定义模糊，SQL 写得再漂亮，结果也救不回来。