如何将SQL相关子查询改写为窗口函数

当相关子查询仅做聚合且分组依据与外层表主键/唯一键一致时，可用窗口函数替代；需确保PARTITION BY与WHERE条件严格对齐、处理NULL、添加唯一性排序兜底，并通过执行计划验证性能。

什么时候该用窗口函数替代相关子查询

要是相关子查询只是进行聚合计算（像 COUNT、A VG、MAX 这些），而且分组依据和外层表的主键或唯一键是一致的，那基本上就可以放心改写。像“查每个用户最新订单时间”“统计每条记录在其部门内的排名”这类需求，就是典型场景。不过呢，如果子查询里有 WHERE 条件依赖外层的非分组字段（比如说 WHERE o.status = u.preferred_status），或者涉及到多表JOIN逻辑，那窗口函数往往就没办法直接替代啦。

ROW_NUMBER() 和 RANK() 在去重场景下的选择

用窗口函数替代 “取每个分组第一条” 类子查询（如 (SELECT TOP 1 ... FROM orders o2 WHERE o2.user_id = u.id ORDER BY o2.created_at DESC)）时，必须明确排序和重复处理策略：

• ROW_NUMBER() 保证严格递增序号，适合“只取一条”，哪怕 created_at 相同也强制拆分
• RANK() 对相同排序值赋予相同排名，后续跳过空位；DENSE_RANK() 不跳位 —— 如果业务允许并列第一且都要保留，就得用 RANK()，否则结果会漏数据
• 排序字段务必包含唯一性兜底，例如 ORDER BY created_at DESC, id DESC，避免因时间精度丢失导致窗口内顺序不可控

聚合类子查询改写时的 PARTITION BY 易错点

把 (SELECT A VG(price) FROM products p2 WHERE p2.category = p1.category) 改成窗口函数，核心是让 PARTITION BY 与子查询 WHERE 条件完全对齐：

• 错误写法：A VG(price) OVER (PARTITION BY category_id) —— 如果外层表 products 的 category 是字符串而关联字段名实际叫 category_name，就会分区错乱
• 必须确认分区字段类型和值域一致：比如子查询用 WHERE p2.category = 'electronics'，窗口函数就必须 PARTITION BY p1.category，不能误写成 PARTITION BY p1.category_id（除非二者严格一一映射）
• 注意 NULL 处理：PARTITION BY 遇到 NULL 默认单独成一组，若原子查询 WHERE 条件不匹配 NULL，则窗口结果里会出现额外的一组，需提前 WHERE category IS NOT NULL

性能差异和执行计划验证方法

窗口函数不是银弹。改写后反而变慢的常见原因是：原相关子查询因索引高效，而窗口函数触发全表扫描再排序。验证是否真优化，得看执行计划：

• PostgreSQL：用 EXPLAIN (ANALYZE, BUFFERS) 对比两者，重点关注 WindowAgg 节点的 Actual Total Time 和 Shared Hit Blocks
• SQL Server：检查是否出现 Window Spool 算子，以及其 EstimateRows 是否远超实际行数（暗示内存压力）
• MySQL 8.0+：确认 EXPLAIN FORMAT=TREE 中是否有 window_function，并观察 rows 列是否比原子查询的 filtered 值大得多

真正影响性能的往往是排序成本 —— 如果 ORDER BY 字段没索引，窗口函数的开销可能比走索引的相关子查询高一个数量级。