如何在SQL Server中使用窗口聚合替换自连接查询-SQL-PHP中文网

窗口聚合能替代自连接，因其单次扫描、分组计算、不生成笛卡尔积；如AVG() OVER(PARTITION BY dept)替代标量子查询，COUNT(*) OVER替代LEFT JOIN+GROUP BY，避免N次重复扫描与多层嵌套。

如何在sql server中使用窗口聚合替换自连接查询

为什么窗口聚合能替代自连接

自连接在需要“每行对比自身所在组内其他行”的场景中很常见，比如查每个员工的部门平均薪资、每个订单的客户历史订单数。但它本质是生成笛卡尔积子集，JOIN条件一旦松散或缺失索引，就会触发大量逻辑读和内存排序。窗口聚合把计算压到单次扫描内完成：不增行、不联表、不重复访问数据页，执行计划里通常只出现一个 Clustered Index Scan 或 Index Seek，后续全是流式计算。

用 AVG() OVER 替代关联子查询求部门平均工资

常见错误写法是用标量子查询或自连接算每个员工所在部门的平均工资：

SELECT e1.name, e1.department_id, e1.salary,
  (SELECT AVG(e2.salary) FROM employees e2 
   WHERE e2.department_id = e1.department_id) AS dept_avg
FROM employees e1;

这种写法对每行都执行一次子查询，N 行就是 N 次独立扫描。换成窗口函数后：

SELECT name, department_id, salary,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

PARTITION BY department_id 等价于按部门分组，但不压缩行数
所有非 NULL salary 参与计算，NULL 值自动忽略（和 AVG() 聚合行为一致）
如果部门字段有 NULL，它们会被归为同一分区——这不是 bug，是预期行为；需加 WHERE department_id IS NOT NULL 过滤

用 COUNT(*) OVER 替代 LEFT JOIN + GROUP BY 统计频次

想查每个用户最近一条订单的同时，附带该用户总订单数，有人会这么写：

SELECT u.user_id, u.name, o.order_id, o.order_date, t.cnt
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.order_date = (
  SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.user_id = u.user_id
)
LEFT JOIN (SELECT user_id, COUNT(*) cnt FROM orders GROUP BY user_id) t 
  ON u.user_id = t.user_id;

三层嵌套，执行计划里至少两个 Hash Match。更直接的做法是：

SELECT user_id, name, order_id, order_date,
  COUNT(*) OVER (PARTITION BY user_id) AS total_orders,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

再在外层 WHERE rn = 1 OR rn IS NULL 就能拿到“每人最新订单 + 总单数”，且只扫 orders 表一次。

容易被忽略的 NULL 和类型陷阱

窗口函数不会隐式过滤掉参与 PARTITION BY 的 NULL 值，也不会跳过 ORDER BY 中的 NULL（默认排在最前）。例如：

SELECT product_id, category, price,
  AVG(price) OVER (PARTITION BY category) AS avg_by_cat
FROM products;

如果 category 有 5 行为 NULL，它们会组成一个分区，avg_by_cat 是这 5 行的平均值
price 为 NULL 的行不参与 AVG 计算，但该行仍会输出结果，avg_by_cat 显示的是其余非 NULL 行的均值
若 price 是 FLOAT，平均值可能带长小数；建议用 DECIMAL 字段或显式 CAST(AVG(price) AS DECIMAL(10,2))