窗口函数能避开自连接性能陷阱，因其单次扫描完成分组计算，避免笛卡尔积与重复读表；关键在于明确声明partition by、order by及over子句，不可混用group by。
为什么窗口函数能避开自连接的性能陷阱
自连接常用来做行间比较或累计计算，比如“每个员工工资比部门平均高多少”，但会引发笛卡尔积膨胀。窗口函数在单次扫描中完成分组内计算，避免重复读表和临时结果集膨胀，执行计划里通常少一个 Nested Loop 或 Hash Join 节点。
关键区别在于：自连接把同一张表当两张逻辑表用，而窗口函数明确声明「按某列分组、在组内排序、再应用聚合」，数据库引擎可直接复用扫描缓存。
AVG()、SUM() 窗口版怎么写才不报错
常见错误是漏写 PARTITION BY 或误写 GROUP BY —— 窗口函数不能和普通 GROUP BY 混用，除非外层再套一层查询。
正确写法必须包含 OVER 子句，且根据需求选择是否加 ORDER BY：
- 要部门平均工资：用
AVG(salary) OVER (PARTITION BY dept_id)，不需要ORDER BY - 要累计奖金：用
SUM(bonus) OVER (PARTITION BY dept_id ORDER BY hire_date)，ORDER BY决定累计顺序 - 如果只写
OVER()（空括号），等价于全表范围，容易偏离预期
替代自连接时，LAG() 和 LEAD() 比聚合更合适
当目标是“和上一条记录对比”（比如环比增长、登录间隔），用 LAG() 直接取前一行值，比自连接 + ROW_NUMBER() 再关联干净得多。
示例：查每个用户上次登录时间
SELECT user_id, login_time, LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time) AS last_login FROM user_log;
注意点：
-
LAG(col, 2)可以跳过1行取前第2行；第三个参数可设默认值，如LAG(amount, 1, 0) - 如果
ORDER BY值相同，数据库可能任意排序，建议加上唯一键（如id）保序 - MySQL 8.0+、PostgreSQL、SQL Server 2012+ 支持；SQLite 3.25+ 也支持，但旧版不认
哪些场景窗口函数救不了，还得回来自连接
窗口函数无法处理跨分组的动态条件匹配，比如“找工资最接近当前员工的其他员工”，因为 OVER 的范围是静态划分的，没法表达“对每一行，从全表中找满足 abs(salary - this.salary) 最小的那条”。
这类问题本质是 Top-N per group 的变体，窗口函数配合 ROW_NUMBER() 有时能绕，但语义复杂、可读性差。更稳妥的做法是保留自连接，再加索引优化：
- 确保连接字段（如
dept_id、user_id）有索引 - 用
LIMIT 1或TOP 1配合ORDER BY ... LIMIT子查询（PostgreSQL/MySQL）控制膨胀 - 某些场景改用
LATERAL JOIN（PostgreSQL）或APPLY（SQL Server）更清晰
窗口函数不是银弹，真正难的是判断哪部分逻辑属于“组内确定性计算”，哪部分依赖行与行之间非对称关系——后者往往藏在业务描述的“比……最……”“除自己外……”这类措辞里。
