窗口聚合能替代自连接，因其单次扫描、分组计算、不生成笛卡尔积；如AVG() OVER(PARTITION BY dept)替代标量子查询，COUNT(*) OVER替代LEFT JOIN+GROUP BY，避免N次重复扫描与多层嵌套。
为什么窗口聚合能替代自连接
自连接在需要“每行对比自身所在组内其他行”的场景中很常见，比如查每个员工的部门平均薪资、每个订单的客户历史订单数。但它本质是生成笛卡尔积子集，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))
真正难调试的不是语法，而是分区边界和 NULL 处理逻辑是否和业务语义对齐——比如“未分类商品”该不该计入某个品类均值，得看报表定义，不能只依赖数据库默认行为。
