SQL中如何用窗口函数替代关联子查询提升运行速度-SQL-PHP中文网

窗口函数能替代多数关联子查询，但性能提升取决于partition by和order by是否准确；写错会导致结果错误或性能更差，如标量子查询应改用avg() over(partition by dept)，且需对齐null处理与字段语义。

sql中如何用窗口函数替代关联子查询提升运行速度

窗口函数能直接替代绝大多数关联子查询，但不是“套上 OVER 就变快”，关键在 PARTITION BY 和 ORDER BY 写对没——写错结果可能全错，性能反而更差。

AVG() OVER(PARTITION BY) 替代标量子查询

常见错误是把 (SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept) 这类标量子查询留在 SELECT 里：每行都触发一次独立执行，数据量一过万就明显变慢。

正确写法是直接用 AVG(salary) OVER (PARTITION BY dept)，数据库单次扫描完成全部计算。

必须确保 PARTITION BY dept 和子查询里的 WHERE e2.dept = e1.dept 语义完全对应：字段名、大小写、NULL 处理都要一致
如果 dept 字段有 NULL，PARTITION BY dept 会把所有 NULL 归为同一组；而传统子查询中 e2.dept = e1.dept 对 NULL 比较结果为 UNKNOWN，不匹配——行为不等价，需提前用 COALESCE(dept, 'UNKNOWN') 对齐
MySQL 8.0+、PostgreSQL、SQL Server 2005+ 都支持；旧版 MySQL（5.7）不支持，别在线上硬套

ROW_NUMBER() OVER() 替代 LEFT JOIN + 子查询求最新记录

比如查每个用户的最新订单，有人写 LEFT JOIN orders o2 ON o1.user_id = o2.user_id AND o2.created_at > o1.created_at WHERE o2.id IS NULL，逻辑绕、难读，且 created_at 重复时结果不确定。

改用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC)，再外层 WHERE rn = 1。

ORDER BY created_at DESC, id DESC 是硬性要求：时间相同时靠 id 保证排序稳定，否则同秒多单会导致每次执行结果不同
如果表上没有 (user_id, created_at, id) 复合索引，这个窗口函数会强制磁盘排序，比原 JOIN 还慢——先看执行计划里有没有 Sort 节点
别在 WHERE 里直接用 rn：必须包一层子查询或 CTE，否则 SQL Server 报错 Invalid column name 'rn'

窗口函数不能出现在 WHERE 或 HAVING 中

别想着直接写 WHERE COUNT(*) OVER (PARTITION BY dept) > 5——语法报错：window functions are not allowed here。

正确做法是两步：先在子查询或 CTE 里算出窗口值，再在外层过滤。

例如：

SELECT * FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY dept) AS dept_size
  FROM emp
) t WHERE dept_size > 5

这不是“嵌套子查询回归”，因为窗口计算只做一次，外层只是简单过滤；而传统写法 WHERE dept IN (SELECT dept FROM emp GROUP BY dept HAVING COUNT(*) > 5) 要额外扫一遍表
注意空 OVER() 是性能陷阱：COUNT(*) OVER () 在 PostgreSQL 中会触发隐式全表排序，千万级表慎用