窗口函数能直接替代多数自关联场景，如分组 Top N、累计聚合、行间对比、分组占比等；关键在正确使用 PARTITION BY 和 ORDER BY，三类排序函数语义不同，性能取决于索引覆盖与数据有序性。
窗口函数能直接替代哪些自关联场景
多数需要自关联的查询，本质是想对当前行和同组其他行做比较或聚合——比如“查每个部门工资最高的员工”“算用户连续登录天数”。窗口函数把这类操作压进单次扫描，不用 JOIN 自身，也不用临时表。
典型可替代场景包括：
– 同分组内排序取 Top N（ROW_NUMBER() / RANK()）
– 累计求和、移动平均（SUM() OVER () / AVG() OVER (ORDER BY …… ROWS BETWEEN ……)）
– 当前行与上 / 下一行对比（LAG() / LEAD()）
– 分组内占比（COUNT(*) OVER (PARTITION BY ……) / COUNT(*) OVER ()）
写错 PARTITION BY 和 ORDER BY 会彻底跑偏
窗口函数结果是否合理，90% 取决于这两个子句的组合。漏写 PARTITION BY 就变成全表范围计算；错写 ORDER BY（比如用无序时间戳）会导致 LAG() 拿到错误的“上一行”。
实操建议：
– 先确认业务逻辑中的“组”是什么（用户？日期？品类？），再写 PARTITION BY
– ORDER BY 必须是确定性排序，时间字段要带精度（避免 DATE 级别导致同天多行顺序不可控）
– 如果只需要分组聚合不关心顺序，ORDER BY 可省略，但 ROWS BETWEEN 类帧定义必须配合 ORDER BY
例如查每个用户最近一笔订单：SELECT user_id, order_id, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn FROM orders
之后加 WHERE rn = 1 即可——比 JOIN 子查询简洁且易读。
ROW_NUMBER()、RANK()、DENSE_RANK() 别混用
三者都排序编号，但空缺处理不同，直接影响“Top 3”这类需求的结果：
ROW_NUMBER()：严格递增，相同值也给不同序号（1,2,3,4）RANK()：相同值同号，跳过后续（1,2,2,4）DENSE_RANK()：相同值同号，不跳号（1,2,2,3）
常见踩坑：
– 用 RANK() 做 Top 3 时，实际可能返回 5 行（因为第 2 名有 3 人，序号全是 2，然后跳到 5）
– 想取“并列第 1 名之后的下一个名次”，该用 DENSE_RANK()
– 排重去重场景（如取每组首条），必须用 ROW_NUMBER()，否则无法保证唯一性
性能陷阱：窗口函数不是万能加速器
它省掉自关联，但没省掉排序。如果 OVER 子句里 ORDER BY 字段没索引，大表上会触发磁盘排序，比带索引的自关联还慢。
关键判断点：
– 查看执行计划里是否有 Sort 节点，以及是否走索引 Index Scan
– 复合索引要覆盖 PARTITION BY + ORDER BY 字段（如 (user_id, created_at)）
– 对超大数据集，若只取 Top 1，有时 GROUP BY + correlated subquery 配合索引反而更快（尤其 MySQL 8.0 以前）
– PostgreSQL 中 WINDOW 定义可复用，避免重复写相同 OVER 子句，提升可读性和优化器识别率
真正难的不是写对语法，而是想清楚：这个“窗口”在物理数据分布上是否天然有序？如果答案是否定的，那就得先解决索引或数据预处理问题。
