SQL 使用窗口函数计算滑动窗口统计

滑动窗口必须显式定义ROWS或RANGE子句；仅ORDER BY默认为RANGE UNBOUNDED PRECEDING TO CURRENT ROW（累积统计），非滑动；需用ROWS BETWEEN n PRECEDING AND CURRENT ROW实现行数滑动；RANGE按值滑动易因重复值扩大窗口；NULL和排序键重复会导致行为异常，应添加唯一字段或ROW_NUMBER()兜底；多窗口函数不共享排序，建议复用winDOW子句或裁剪数据；时间范围滑动需用自连接等替代方案。

滑动窗口统计必须显式定义 ROWS 或 RANGE 子句

不写 ORDER BY 就用不了滑动窗口，写了 ORDER BY 却没写 ROWS/RANGE，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW —— 这不是你想要的“滑动”，而是累积统计。比如想算最近 3 行的平均值，AVG(sales) OVER (ORDER BY date) 实际算的是从第一行到当前行的均值。

正确写法必须明确行数范围：

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：含当前行共 3 行（前两行 + 当前行）
• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：当前行前后各 1 行，共 3 行居中窗口
• RANGE 按值而非行数滑动，遇到重复排序值会意外扩大窗口，生产环境慎用

NULL 值和排序键重复会导致窗口行为异常

当 ORDER BY 字段存在 NULL，不同数据库处理方式不同：postgresql 默认把 NULL 排在最前，mysql 8.0+ 默认排最后，SQL Server 需显式写 NULLS LAST 才可控。一旦排序不可靠，ROWS 的“前 N 行”就失去意义。

更隐蔽的问题是排序键重复：比如按 date 排序，但多条记录同一天。此时 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 可能跳过部分同天记录（因物理顺序不确定），或把所有同天记录都拉进窗口（RANGE 下）。解决方法只有两个：

• 在 ORDER BY 中加入唯一字段兜底，例如 ORDER BY date, id
• 用 ROW_NUMBER() OVER (...) 生成确定性序号，再基于该序号做窗口

性能敏感场景下避免在大结果集上套多层窗口函数

每个窗口函数都会触发一次独立的排序扫描。如果写成 select AVG(x) OVER w, SUM(y) OVER w, count(*) OVER w FROM t WINDOW w AS (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)，多数引擎仍会为每个函数重跑一遍窗口逻辑，而非复用一次排序结果。

实际优化建议：

• 优先合并同类操作，例如用 AVG() 而非 SUM()/COUNT() 手动计算均值
• 若需多个统计量且窗口定义一致，确认所用数据库是否支持 WINDOW 子句复用（PostgreSQL、MySQL 8.0+ 支持；SQL Server 不支持，必须重复写）
• 对千万级以上表，先用 WHERE 或分区裁剪缩小数据集，再开窗口

时间范围滑动不能只靠 ORDER BY date，得用 INTERVAL 或自连接模拟

真正按“过去 7 天”而非“过去 7 行”统计时，标准窗口函数无能为力——ROWS 是行数，RANGE 在时间字段上仅支持 UNBOUNDED 或 CURRENT ROW，不支持 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW（除 BigQuery 外，主流 SQL 引擎都不支持该语法）。

可行替代方案：

• PostgreSQL：用 GENERATE_SERIES + LATERAL JOIN 或子查询关联时间范围
• MySQL 8.0+：用 JOIN 关联自身，条件为 t2.ts >= t1.ts - INTERVAL 7 DAY AND t2.ts
• 更稳妥的做法是预计算时间分桶（如每日汇总），再用行式滑动，避开实时时间范围匹配的性能黑洞

窗口函数写起来简洁，但滑动逻辑是否真按预期执行，得盯住排序稳定性、NULL 处理、行 vs 值语义，还有数据库版本对 RANGE 的实际支持程度。这些地方一松懈，结果就 quietly 错了。