ROW_NUMBER()、LAG()/LEAD()、COUNT() OVER 等窗口函数可高效替代自连接，关键需确保ORDER BY稳定（如加id保序）、显式指定默认值及正确嵌套子查询。
ROW_NUMBER() 替换“每组取最新一条”的自连接
直接用 ROW_NUMBER() + 子查询过滤，比 NOT EXISTS 或 LEFT JOIN 自连接更稳、更快。关键不是“能不能写”，而是“排序是否确定”。
- 必须写
PARTITION BY user_id ORDER BY created_at DESC, id DESC：时间相同时靠id保序，否则同一秒多单会导致每次执行结果不一致 - 不能省略
ORDER BY：SQL Server 不报错但按物理存储顺序排，增删数据后rn = 1可能指向不同记录 - 别在
WHERE里直接用rn：必须包一层子查询或 CTE，否则 SQL Server 报错Invalid column name 'rn' - 示例：
SELECT user_id, order_id, amount, created_at FROM ( SELECT user_id, order_id, amount, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn FROM orders ) t WHERE rn = 1;
LAG()/LEAD() 替换“相邻行比较”的自连接
用 LAG() 算时间差、状态跳变、环比，比靠 JOIN ON t1.id = t2.id - 1 可靠得多——ID 连续是幻觉，业务数据从不保证这点。
-
ORDER BY必须显式且稳定：比如ORDER BY login_date, id，否则 “前一行” 完全不可控 - 第三个参数填默认值：如
LAG(amount, 1, 0)，避免NULL导致整个差值列崩掉 - 注意 NULL 处理差异：SQL Server 对
ORDER BY字段含NULL时默认排最前，而 PostgreSQL 可能排最后，业务逻辑要兜底 - 示例：
SELECT user_id, login_date, DATEDIFF(day, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date), login_date) AS gap_days FROM user_logins;
COUNT() OVER + 条件聚合替代多层自连接统计
查“每个部门薪资高于平均值的员工数”，不用两层子查询套自连接，COUNT() 窗口函数一步到位，逻辑直、性能好。
- 先算部门平均值：
AVG(salary) OVER (PARTITION BY dept_id) - 再条件计数：
COUNT(CASE WHEN salary > avg_sal THEN 1 END) OVER (PARTITION BY dept_id) - 别用
RANK()或DENSE_RANK()后再WHERE rank ：并列时可能多返回，下游插入唯一约束表直接失败 - 空
OVER()是性能陷阱：比如COUNT(*) OVER ()在 SQL Server 中会触发隐式全表排序，小表无所谓，千万级表慎用
为什么有时窗口函数反而更慢？
不是语法对了就一定快。窗口函数的性能取决于你让它干了什么活，而不是它“看起来更高级”。
- 没加
PARTITION BY却写ORDER BY created_at：变成全表排序，O(n log n)，比带索引的自连接还重 - 原自连接本身有高效复合索引（如
(user_id, created_at)），而窗口函数触发大排序且内存不足，IO 成瓶颈 - 多个不同
PARTITION BY或ORDER BY的窗口函数共存：SQL Server 和 PostgreSQL 无法复用排序结果，各排各的，耗时翻倍 - 验证方法：看执行计划里的
Sort成本和Nested Loops行数，别只盯着代码行数少
真正落地时最容易被忽略的，是数据语义的隐含假设——比如“时间字段非空”“ID 严格递增”“部门分组不会跨年变更”。这些在窗口函数里不会报错，但会让结果悄悄偏移。
