窗口聚合能替代子查询,因其在单次扫描中完成全部计算,避免相关子查询为外层每行重复执行;例如取最新订单应使用ROW_NUMBER()配合PARTITION BY和完整ORDER BY,而非MAX()组合或OFFSET-FETCH。

为什么窗口聚合能替代子查询
因为子查询在相关场景下会为外层每一行重复执行,而窗口聚合在单次扫描中完成全部计算。比如要查每个用户的最新订单金额,用(SELECT TOP 1 amount FROM orders WHERE user_id = u.id ORDER BY created_at DESC)会在用户表每行触发一次独立查询;换成MAX(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)就只扫一遍orders表——前提是user_id和created_at有联合索引。
ROW_NUMBER() + 外层过滤是最稳的“取最新一行”写法
别信MAX(created_at)配MAX(order_no)这种组合,它们不保证来自同一行。正确姿势是先编号再筛选:
SELECT user_id, order_no, amount, created_at
FROM (
SELECT user_id, order_no, amount, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, order_no DESC) AS rn
FROM orders
) t
WHERE rn = 1;
- 必须把业务上决定“最新”的字段全写进
ORDER BY,比如时间相同还要按单号降序 -
PARTITION BY字段必须和你要关联的外键完全一致,别多加status之类导致分组变细 - SQL Server 2019支持
OFFSET-FETCH但不推荐用于此场景,它无法跳过排序开销
SUM() / COUNT() OVER 配合 FILTER 能省掉大量LEFT JOIN
当主表要带“每个用户的订单总数”“近30天销售额”等指标时,传统做法是LEFT JOIN多个子查询,结果是N次扫描。用窗口聚合+条件过滤更直接:
SELECT u.id, u.name,
COUNT(*) OVER (PARTITION BY u.id) AS total_orders,
SUM(CASE WHEN o.created_at >= DATEADD(day, -30, GETDATE()) THEN o.amount ELSE 0 END)
OVER (PARTITION BY u.id) AS recent_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-
COUNT(*) OVER本身不含WHERE,所以得靠CASE WHEN实现条件聚合 - 如果
orders表很大但users很小,建议改用CTE先聚合再JOIN,避免笛卡尔膨胀 - 注意
LEFT JOIN后o.created_at可能为NULL,CASE里要显式处理,否则recent_amount会算错
容易被忽略的性能陷阱:框架子句和索引失效
默认OVER ()用的是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,在ORDER BY字段有重复值时会拉入更多行,拖慢速度。明确指定ROWS更可控:
- 要累计求和?用
SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS UNBOUNDED PRECEDING) - 要移动平均?必须写清
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW,不然RANGE可能跨天聚合 -
ORDER BY字段没索引?SQL Server 2019不会自动优化窗口排序,会强制Sort操作符,IO翻倍 - 别在
PARTITION BY里用函数,比如PARTITION BY YEAR(created_at)——这会让索引失效,改用计算列或提前归档
真正卡住的往往不是语法,而是窗口定义和底层数据分布没对齐。跑EXPLAIN看执行计划里有没有Window Spool和Sort节点,这两个一出现,基本就是框架或排序字段没走索引。

















