当需按字段分组比较组内行间值时，应优先用窗口函数替代自连接；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, salary
FROM (
SELECT dept, name, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE 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_days
FROM 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
) t
WHERE total_by_cus > 1000;
窗口函数的执行时机晚于 WHERE，早于 ORDER BY，这个顺序决定了它不能参与初始行过滤，但能参与最终排序或分页。
