SQL 多窗口函数组合使用的优化策略

窗口函数嵌套、重复排序、RANGE框架、跨分区JOIN易致性能爆炸；应拆解为CTE、复用WINDOW子句、显式指定ROWS、预聚合去重。

窗口函数嵌套导致执行计划爆炸

直接在 SELECT 中对一个窗口函数结果再套另一个窗口函数（比如 ROW_NUMBER() OVER (ORDER BY SUM(x) OVER (PARTITION BY y))），多数数据库会拒绝或生成极低效的执行计划。PostgreSQL 14+ 允许部分嵌套，但实际仍会物化中间结果，内存占用陡增；MySQL 8.0 则直接报错 ERROR 3579 (HY000): Window function is not allowed in this context。

实操建议：

• 把多层逻辑拆到 CTE 或子查询中，显式控制计算顺序，例如先用 CTE 算出 SUM(x) OVER (PARTITION BY y)，再在外层对其排序编号
• 避免在 WHERE 或 JOIN 条件中引用窗口函数别名——它们在逻辑上晚于这些子句执行，必须用子查询包裹才能过滤
• SQL Server 中若需按窗口聚合结果排序分页，优先用 OFFSET-FETCH 配合预计算列，而非在 ORDER BY 里写 AVG(val) OVER (PARTITION BY grp)

ORDER BY 在多个窗口函数中重复声明的开销

当多个窗口函数共用同一排序逻辑（如都需按 ts DESC），但各自写一遍 ORDER BY ts DESC，优化器通常不会自动复用排序结果。尤其在大表上，每个窗口函数可能触发独立的 sort 操作，I/O 和 CPU 成倍增长。

实操建议：

• 统一使用相同 WINDOW 命名子句（PostgreSQL / SQL Server 支持），例如定义 WINDOW w AS (PARTITION BY user_id ORDER BY ts DESC)，后续所有函数调用 ROW_NUMBER() OVER w、LAG(val) OVER w
• MySQL 8.0 不支持命名 WINDOW 子句，此时应确保所有相关窗口函数的 PARTITION BY 和 ORDER BY 字段完全一致且顺序相同，部分版本可借此触发内部排序缓存
• 若 ORDER BY 含表达式（如 ORDER BY DATE(ts)），务必确认该表达式已在索引中覆盖，否则每次窗口计算都会触发全字段计算+排序

UNBOUNDED PRECEDING 和 RANGE vs ROWS 的性能陷阱

默认窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，它按值语义归并相等排序键的行；而 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

是物理行序。当排序字段存在大量重复值（如状态码、日期截断到天），RANGE 框架会导致窗口边界动态扫描，性能可能比 ROWS 差 5–10 倍。

实操建议：

• 除非业务明确要求“同分同排名”（如排行榜并列），否则一律显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• COUNT() OVER (...) 类聚合在 RANGE 下无法利用前缀和优化，而 ROWS 框架下 PostgreSQL 可自动启用 incremental aggregation，MySQL 8.0 也能更好复用临时排序缓冲区
• SQL Server 中 UNBOUNDED PRECEDING 在大偏移量（如 LAG(val, 10000)）时易触发 tempdb spill，改用 ROWS BETWEEN 10000 PRECEDING AND 10000 PRECEDING 反而更稳

跨分区聚合与 JOIN 导致的数据膨胀

用窗口函数算出分区统计值（如每个用户的平均订单额）后，再与原表 JOIN 回填，容易因未去重或关联条件松散引发笛卡尔积。更隐蔽的是：某些写法看似没 JOIN，实则隐含膨胀——比如在 GROUP BY user_id 查询中同时引用 COUNT(*) OVER ()（全表计数）和 AVG(amount) OVER (PARTITION BY user_id)，会导致每组行重复输出多次。

实操建议：

• 优先用 CTE 预聚合再 LEFT JOIN，而非在主查询中混用细粒度行数据和粗粒度窗口结果
• 确认 PARTITION BY 字段是否真的构成业务唯一键；若不是（如日志表中 user_id, event_time 分区），窗口结果会随原始行数线性放大，需提前 DISTINCT ON 或 GROUP BY
• BigQuery 中若需同时访问当前行和跨分区统计，用 ARRAY_AGG(...) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 比多次窗口 + JOIN 更省内存

多窗口组合真正难的不是语法，而是判断哪些计算可以合并、哪些必须隔离——尤其当涉及非确定性排序（如无唯一键的 ORDER BY status）或混合了 RANGE 和 ROWS 框架时，不同数据库的物化策略差异极大，必须看执行计划里的 WindowAgg 节点是否复用排序输入。