SQL中如何利用窗口函数简化复杂的子查询

窗口函数可替代需“每行对比聚合结果”的子查询，如计算工资占比、取每组最新记录、top-n排序等；avg() over(partition by...)替代关联子查询求均值，row_number() over(...)替代not exists取top-n。

sql中如何利用窗口函数简化复杂的子查询_代码重构技巧

窗口函数能替代哪些典型子查询场景

多数需要“每行对比聚合结果”的子查询，比如计算每个员工工资占部门平均工资的比例、找出每组最新一条记录、或给连续登录天数排序，都可以用窗口函数直接替代。关键在于：只要不改变原表行数，且需引用同组内其他行的聚合值，OVER() 就比 JOIN 或相关子查询更简洁可靠。

`AVG() OVER(PARTITION BY ...)` 替代关联子查询求组内均值

常见错误是写成：SELECT name, salary, (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept) AS dept_avg FROM emp e1——性能差、不可复用、嵌套深。改用窗口函数后逻辑扁平：

SELECT name, salary, 
       AVG(salary) OVER(PARTITION BY dept) AS dept_avg
FROM emp;

PARTITION BY dept 定义分组边界，等价于子查询里的 WHERE 条件
结果保留原始行数，无需 GROUP BY，也不影响其他字段输出
若需四舍五入，直接套 ROUND(AVG(salary) OVER(...), 2)，不用额外 ROUND 子查询

用 `ROW_NUMBER() OVER(ORDER BY ...)` 替代 `NOT EXISTS` 取每组 Top-N

想查每个部门薪资前 3 的员工？传统写法常依赖 NOT EXISTS 或自连接计数，易出错且难读。窗口函数一行搞定：

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM emp
) t
WHERE rn

ROW_NUMBER() 保证严格排序后编号，相同薪资不会并列；如需并列用 RANK() 或 DENSE_RANK()
必须用子查询或 CTE 包裹，因为 WHERE 无法直接过滤窗口函数结果
注意 ORDER BY 里字段要明确，避免因隐式排序导致结果不稳定

窗口函数和子查询混用时最容易忽略的执行顺序

窗口函数在 SQL 执行顺序中晚于 FROM/WHERE，但早于 GROUP BY 和 HAVING。这意味着：

不能在 WHERE 中直接引用窗口函数结果（会报错 column "rn" does not exist）
可以在 SELECT 和 ORDER BY 中使用，也能用于后续计算，比如 salary / AVG(salary) OVER(...)
如果先 GROUP BY 再开窗，必须确保 OVER 子句里的分区字段在 GROUP BY 列表中，否则 PostgreSQL 会报错，MySQL 8.0+ 则允许但语义可能偏离预期