窗口函数能替代多数关联子查询，关键在正确使用PARTITION BY和ORDER BY；写错会导致结果错误或性能更差，需注意NULL处理、排序稳定性、索引支持及帧定义语义。
窗口函数能直接替代绝大多数关联子查询，关键不是“能不能写”，而是“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')对齐
用 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节点
用 COUNT(*) OVER(PARTITION BY ... HAVING ...) 类逻辑？不行，得换思路
窗口函数不能出现在 WHERE 或 HAVING 中，所以别想着直接写 WHERE COUNT(*) OVER (PARTITION BY dept) > 5——语法报错，ERROR: 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)，要额外扫一遍表 - 如果只是想排除小分组，
FILTER（PostgreSQL）或条件聚合（COUNT(CASE WHEN ... THEN 1 END) OVER (...)）有时更轻量
ROWS BETWEEN 比 RANGE BETWEEN 快，但别硬套
看到“过去7天”就下意识写 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW？多数情况下这是性能陷阱。
-
RANGE按值匹配，每行都要重新扫描找时间范围内的所有行，无法利用索引，大数据量时 IO 爆涨 - 更优解：先用
sale_date::date做分区键，配合ORDER BY sale_date+ROWS BETWEEN 6 PRECEDING AND CURRENT ROW，物理行数固定，CPU 友好 - 但前提是数据按日期基本连续——如果某天断更，
ROWS BETWEEN 6 PRECEDING会跳过空缺日，算的是“最近7条记录”而非“最近7天”，业务语义是否接受这点，得和产品对清楚
最常被忽略的点：窗口函数快，是因为它省了多次扫描，而不是“天生快”。一旦 PARTITION BY 字段没索引、ORDER BY 字段含大量重复值、或者帧定义（ROWS/RANGE）和业务周期错位，优化就变成负优化。
