窗口函数能直接替代绝大多数按分组求前N行、累计值、相邻行比较的自关联子查询，前提是子查询仅依赖当前表字段且属行间计算，而非跨表关联或条件聚合。
窗口函数能直接替代哪些自关联子查询场景
绝大多数按分组求前N行、累计值、相邻行比较的自关联子查询，都可以用窗口函数重写。核心判断标准是：子查询只依赖当前表的字段，且逻辑上属于“基于排序或分组的行间计算”，而非跨表关联或条件聚合。
典型可替代场景包括：SELECT * FROM t1 a WHERE a.id = (SELECT MAX(b.id) FROM t1 b WHERE b.group_id = a.group_id)（每组最大ID记录），SELECT a.val - (SELECT b.val FROM t1 b WHERE b.id = a.id - 1)（相邻行差值），以及各类“每组排名后取Top 3”的写法。
ROW_NUMBER() + 外层过滤比自关联更安全
自关联子查询在遇到重复排序键或NULL值时容易返回多行或空结果，而ROW_NUMBER()强制生成唯一序号，配合外层WHERE rn = 1语义清晰、结果确定。
实操建议：
- 用
ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY updated_at DESC, id DESC)代替“找每组最新记录”的相关子查询 - 避免在
ORDER BY中混用ASC/DESC导致排序不稳定；若业务允许并列，改用RANK()但需注意跳名次问题 - 子查询里用
TOP 1或LIMIT 1可能因缺少ORDER BY产生非预期结果，窗口函数天然绑定排序逻辑
LAG/LEAD处理相邻行时必须显式声明默认值
自关联通过a.id = b.id - 1隐式假设ID连续，实际常因删除、事务失败导致断号，LAG(val)则严格按ORDER BY定义的物理顺序取值，更可靠。
但默认行为在首尾行返回NULL，容易引发计算中断或类型错误：
- 写成
LAG(val, 1, 0) OVER (ORDER BY ts)明确补0，避免val - LAG(val)整列为NULL - 时间序列场景慎用
LAG(val) OVER (ORDER BY DATE)——若某天无数据，会跳到更早日期，应先用GENERATE_SERIES补全再LAG - PostgreSQL 14+支持
IGNORE NULLS，但MySQL 8.0和SQL Server不支持，跨库迁移需留意
性能差异集中在索引与执行计划
窗口函数并非总是更快。当原自关联子查询能命中(group_id, updated_at)联合索引时，其执行计划可能是高效的嵌套循环；而窗口函数强制全表扫描+排序，大数据量下反而更慢。
优化关键点：
- 确保
PARTITION BY和ORDER BY字段有合适索引，例如CREATE INDEX idx_group_time ON t1(group_id, updated_at DESC) - 用
EXPLAIN ANALYZE对比两者实际Sort和WindowAgg节点耗时，别只看逻辑简洁性 - 如果仅需单个聚合值（如每组最大值），
MAX(val) OVER (PARTITION BY group_id)比ROW_NUMBER()+ 过滤更轻量
真正难的是动态分组边界或带复杂条件的“自关联”——比如“找每个用户上一笔满足status='paid'的订单”，这时窗口函数仍需配合FILTER子句或先过滤再开窗，不能无脑替换。
