窗口函数在多数“组内行间计算”场景下更快，因其仅需一次表扫描和一次排序，而自连接每行可能触发独立子查询，成本近O(n²)；但需满足有复合索引、数据库版本支持、且正确书写PARTITION BY与ORDER BY。
什么时候窗口函数比自连接快
窗口函数在多数“组内行间计算”场景下更快，核心原因是它只扫描一次表、只排序一次；而自连接对每行都可能触发独立子查询或嵌套循环，执行成本接近 O(n²)。比如查每个用户最新订单，ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) 在 100 万行数据上通常比 LEFT JOIN 或 NOT EXISTS 快 2–3 倍。
但前提是：
-
PARTITION BY和ORDER BY字段有复合索引（如(user_id, created_at DESC, id)），否则窗口排序会落盘，反而更慢 - 原自连接没做有效过滤（比如
WHERE user_id IN (1,2,3)后再 JOIN），窗口函数却要处理全量数据 - 数据库版本支持且优化器能识别窗口路径（MySQL 8.0+、PostgreSQL 11+、SQL Server 2012+）
哪些自连接逻辑根本不能换
窗口函数只作用于单表分组内的行，跨表、多跳关联、非确定性匹配都无法替代。典型不可替换场景包括：
- 需要
JOIN orders o ON u.id = o.user_id AND o.status = 'shipped'这类带复杂条件的关联——窗口函数无法加WHERE条件进OVER() - 查“用户下单后 7 天内是否退款”，涉及两个时间点的范围判断，
ROWS BETWEEN无法表达这种非连续、非对齐的时间窗口 - 递归结构（如组织架构向上找上级），必须用
WITH RECURSIVE，窗口函数无递归能力 - 关联结果要聚合后再 Join 回主表（如先算部门平均薪资，再和员工表比），窗口函数只能同层计算，不能跨层引用
ORDER BY 不写就是硬伤
几乎所有翻车都源于漏写 ORDER BY。例如 ROW_NUMBER() OVER (PARTITION BY dept_id) 在 PostgreSQL 直接报错，在 MySQL 和 SQL Server 可能返回随机序号——因为数据库不保证物理存储顺序稳定。
正确写法必须显式指定：
- 时间字段精度要够：用
created_at::timestamp(6)而不是DATE(created_at)，避免同天多行乱序 - 补二级排序字段：
ORDER BY created_at DESC, id DESC，确保相同时间戳下顺序可复现 - NULL 值处理要主动：
ORDER BY created_at DESC NULLS LAST，不同数据库默认不同 -
LAG()、LEAD()、ROW_NUMBER()全部强制要求ORDER BY，缺了就报错或结果错
性能倒退时先看执行计划
换完反而变慢，大概率不是函数本身问题，而是执行路径没对齐。重点盯三个地方：
-
EXPLAIN里有没有Sort节点？如果有且没走索引，说明排序成了瓶颈 - 窗口函数的
OVER子句是否无意中变成全表排序（漏PARTITION BY）？这时成本可能超过带索引的自连接 - 原自连接是否用了
Index Nested Loop，而窗口函数被迫走Materialize + Sort？尤其在内存不足时，窗口排序刷磁盘比索引查找还重 - 小结果集（
真正难的是判断“该不该换”，而不是“怎么写”。同一需求下，窗口函数和自连接可能各有优势，得看数据分布、索引、过滤时机这三点。
