当相关子查询仅做聚合且分组依据与外层表主键/唯一键一致时，可用窗口函数替代；需确保PARTITION BY与WHERE条件严格对齐、处理NULL、添加唯一性排序兜底，并通过执行计划验证性能。
什么时候该用窗口函数替代相关子查询
当相关子查询只做聚合计算（如 COUNT、AVG、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 AVG(price) FROM products p2 WHERE p2.category = p1.category) 改成窗口函数，核心是让 PARTITION BY 与子查询 WHERE 条件完全对齐：
- 错误写法：
AVG(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 字段没索引，窗口函数的开销可能比走索引的相关子查询高一个数量级。
