SQL 窗口函数如何替代子查询？

窗口函数可直接在查询中计算聚合、排序或累计结果，无需嵌套子查询，代码更简洁且性能更优；通过OVER(PARTITION BY …)实现分组计算，保持原表行数不变，并支持ROW_NUMBER()、RANK()等灵活排序。

窗口函数能直接在当前查询中计算聚合或排序结果，不用嵌套子查询，代码更简洁、性能通常更好。

用 OVER() 替代关联子查询

比如要查每个员工的工资，同时显示其部门平均工资。传统写法常靠子查询或 JOIN：

SELECT name, dept, salary,   (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept) AS dept_avg FROM emp e1;

改用窗口函数，一行 AVG() OVER(PARTITION BY dept) 就搞定：

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

关键点：

• PARTITION BY 相当于子查询里的 WHERE 关联条件
• 结果与原表行数一致，不改变数据结构
• 避免多次执行相同子查询，数据库可优化执行计划

用 ROW_NUMBER() / RANK() 替代 TOP-N 子查询

查每个部门薪资最高的员工，常见子查询写法较绕：

SELECT * FROM emp e1 WHERE salary = (   SELECT MAX(salary) FROM emp e2 WHERE e2.dept = e1.dept );

用窗口函数更直观：

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 = 1;

注意区别：

• ROW_NUMBER() 保证唯一编号，适合取“一个”代表
• RANK() 处理并列时跳号，DENSE_RANK() 不跳号，按需选用
• 子查询可能因并列返回多行或漏行，窗口函数逻辑更可控

累计计算场景：替代自连接或递归子查询

比如算每日销售额的累计值，不用自连接求和：

SELECT date, amount,   (SELECT SUM(amount) FROM sales s2     WHERE s2.date <= s1.date) AS cum_sum FROM sales s1;

直接用 SUM() OVER(ORDER BY ...)：

SELECT date, amount,   SUM(amount) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) AS cum_sum FROM sales;

要点：

• ROWS UNBOUNDED PRECEDING 明确累计范围（从第一行到当前行）
• 比自连接少一次扫描，大数据量下优势明显
• ORDER BY 必不可少，否则累计无意义

注意事项和限制

窗口函数不是万能替代，需注意：

• 不能在 WHERE 或 GROUP BY 中直接使用（因执行顺序在它们之后），需用子查询或 CTE 包裹
• 某些复杂逻辑（如跨多层嵌套的条件聚合）仍需子查询配合
• 不同数据库对窗口函数支持略有差异（如 mysql 8.0+、postgresql、SQL Server 全面支持；旧版 MySQL 需避开）