如何用SQL窗口函数替换关联子查询以提升性能

窗口函数能替代多数关联子查询，关键在正确使用partition by和order by；写错会导致结果错误或性能更差，需注意null处理、排序稳定性、索引支持及帧定义语义。

如何用sql窗口函数替换关联子查询以提升性能_实战改写join案例

窗口函数能直接替代绝大多数关联子查询，关键不是“能不能写”，而是“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）和业务周期错位，优化就变成负优化。