窗口函数能替代大部分自连接，前提是满足「单表、分组、行间计算」三要素；漏写ORDER BY或缺少对应索引时盲目替换反而更慢。
窗口函数能替代大部分自连接，但前提是问题满足「单表、分组、行间计算」三要素；盲目替换反而更慢，尤其当漏写 ORDER BY 或缺少对应索引时。
ROW_NUMBER() 替代找每组最新/最高记录
自连接查“每个用户最新订单”要对每条订单扫描同用户所有其他订单，复杂度 O(n²)；ROW_NUMBER() 只需一次扫描 + 分区内排序，复杂度 O(n log n)。
- 必须写
PARTITION BY user_id，否则变成全表排序，性能崩盘 -
ORDER BY created_at DESC, id DESC是标配：时间相同时靠id保序，避免结果不可复现 - 如果已有
(user_id, created_at)复合索引，排序可能被跳过，执行计划里看不到Sort节点 - MySQL 8.0+、PostgreSQL、SQL Server 2012+ 支持；旧版 MySQL 或 SQLite 需确认版本
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() 替代按 ID 或时间关联上下行
用 JOIN orders o1 ON o1.id = o2.id - 1 假设主键连续，实际中极易出错；LAG() 直接按业务字段（如 login_date）排序后取前一行，语义清晰且稳定。
-
LAG(value, 1, 0)第三个参数是默认值，防止首行返回NULL污染后续计算 -
ORDER BY login_date必须显式写出，否则 “上一行” 完全无定义 - 若
login_date含NULL，MySQL 8.0 行为可能和 PostgreSQL 不一致，建议先WHERE login_date IS NOT NULL - 不要用
RANGE BETWEEN INTERVAL '1 day' PRECEDING——它无法走索引，每行都得重扫范围
SELECT user_id, login_date,
login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS days_since_last
FROM user_logins;
COUNT() OVER + CASE 替代 JOIN 统计类自连接
想查“每个部门薪资高于本部门平均值的员工数”，传统写法是子查询套自连接，至少两轮全表扫描；COUNT() OVER 一次搞定，且保持原始行数便于后续过滤。
- 别写
COUNT(*)，必须用COUNT(CASE WHEN salary > avg_salary THEN 1 END)，否则统计的是总人数 - 窗口内聚合不能直接引用同级计算列（如
avg_salary），得先用 CTE 或子查询算出均值再引用 - 如果只查某几个部门（如
WHERE department_id IN (1,5,8)），自连接可能更快——窗口函数被迫处理全量数据，而自连接可借力索引快速定位小集合
为什么有时改了反而更慢？关键看执行路径
窗口函数不是自动加速器，性能倒退几乎都源于执行计划失控：
-
ROW_NUMBER() OVER (PARTITION BY dept)漏掉ORDER BY：PostgreSQL 直接报错，MySQL/SQL Server 返回随机序，rn = 1结果不可控 - 写了
ORDER BY created_at但字段只有秒级精度：多条记录时间相同 → 窗口内顺序不确定 → 每次执行结果可能不同 - 原自连接带强过滤条件（如
WHERE user_id = 123），而窗口函数没加WHERE就直接开窗 → 全表扫描不可避免 - 内存不足时，数据库把窗口排序刷到磁盘，IO 成瓶颈；而索引驱动的自连接（
ref类型）可能更轻量
真正决定快慢的，不是语法是否“高级”，而是 PARTITION BY 和 ORDER BY 字段有没有对应索引，以及你是否在开窗前就用 WHERE 把数据集压到最小。
