怎样在SQL中利用窗口函数替代复杂的Self Join自连接？

当需按字段分组比较组内行间值时，应优先用窗口函数替代自连接；ROW_NUMBER()、RANK()、DENSE_RANK()行为不同，选错将导致Top N结果错误；LAG()/LEAD()可高效获取前后行数据，但须注意PARTITION BY和ORDER BY；聚合类窗口函数需套子查询才能过滤。

什么时候该用窗口函数替代自连接

当你要对同一张表里“按某个字段分组后，比较组内不同行之间的值”时，比如找每个部门工资第二高的员工、计算相邻订单的时间差、统计累计销量——这些场景下硬写 JOIN 不仅逻辑绕，还容易漏数据或重复计数。窗口函数不是万能的，但它能直接在单次扫描中完成原本需要两遍扫描+笛卡尔积的操作。

ROW_NUMBER() vs RANK()：选错就拿不到想要的“第二名”

想取每组 Top N，最常踩的坑是混淆排序函数行为：ROW_NUMBER() 强制给每行唯一编号（哪怕值相同），RANK() 对相同值赋予相同排名、跳过后续序号，DENSE_RANK() 则不跳号。比如两个员工工资并列第一，用 ROW_NUMBER() 会标成 1 和 2，用 RANK() 是 1 和 1，下一个是 3；而你真正要的是“工资排第二的人”，就得看业务定义——是“第二高薪者（可能多人）”还是“严格第二顺位（仅一人）”。

示例：查每个部门工资第二高的员工（允许并列）

SELECT dept, name, salaryFROM (  SELECT dept, name, salary,         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk  FROM employees) tWHERE rnk = 2;

用 LAG() / LEAD() 替代“自己连自己找上一条”

自连接常用来获取前/后一行的数据，比如对比当前订单和上一个订单的金额变化。这时 LAG() 直接返回同组内前 n 行的指定列值，LEAD() 同理向前看。关键点在于：PARTITION BY 必须明确分组依据（否则跨组拉数据），ORDER BY 决定行序（无序则结果不可靠），且默认取前 1 行，要取前 2 行得显式写 LAG(sales, 2)。

• 没写 PARTITION BY？整个表被当成一组，LAG() 只在全局排序下生效
• ORDER BY created_at 但存在相同时间戳？结果顺序不确定，建议加二级排序如 ORDER BY created_at, id
• 首行调用 LAG() 返回 NULL，别忘了用 COALESCE() 处理

示例：计算每个用户连续两次登录的时间差

SELECT user_id,       login_time,       COALESCE(login_time - LAG(login_time) OVER (         PARTITION BY user_id ORDER BY login_time       ), INTERVAL '0' DAY) AS gap_daysFROM user_logins;

聚合类窗口函数不能直接过滤，得套子查询

像 SUM() OVER(...) 或 COUNT() OVER(...) 这类函数，返回的是“当前行所在窗口的聚合结果”，它本身不是筛选条件。你想只保留累计销量超 1000 的记录？不能写 WHERE SUM(sales) OVER (...) > 1000，SQL 会报错。必须先用子查询或 CTE 把窗口结果算出来，再在外层过滤。

常见错误写法：SELECT * FROM orders WHERE SUM(amount) OVER (PARTITION BY customer_id) > 1000 → 报错 column "amount" must appear in the GROUP BY clause

正确做法：

SELECT *FROM (  SELECT *,         SUM(amount) OVER (PARTITION BY customer_id) AS total_by_cus  FROM orders) tWHERE total_by_cus > 1000;

窗口函数的执行时机晚于 WHERE，早于 ORDER BY，这个顺序决定了它不能参与初始行过滤，但能参与最终排序或分页。