窗口函数能替代多数关联子查询，但性能提升取决于PARTITION BY和ORDER BY是否准确；写错会导致结果错误或性能更差，如标量子查询应改用AVG() OVER(PARTITION BY dept)，且需对齐NULL处理与字段语义。
窗口函数能直接替代绝大多数关联子查询，但不是“套上 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 中会触发隐式全表排序，千万级表慎用
为什么有时窗口函数反而更慢？
不是语法对了就一定快。多个不同 PARTITION BY 或不同 ORDER BY 的窗口函数共存时，PostgreSQL 和 SQL Server 往往无法复用排序结果，会分别排序两次甚至三次。
实测千万级表下，耗时可能翻倍。尤其当 ORDER BY 字段无索引时，每个分组都要单独排序——这才是真正的性能瓶颈，不是函数本身的问题。
最容易被忽略的点：你看到的是“一行代码替换了三行子查询”，但背后是否引入了未察觉的排序开销，得看执行计划里的 Sort 节点数量和数据量级。
