在SQL开发中处理“按字段分组后比较组内行间值”这类需求时，很多开发者首先想到的是写自连接——把自己和自己关联起来，再繁琐地加条件去重。但说实话，这种方式不仅逻辑绕，还容易漏数据或重复计数。窗口函数才是更优雅的选择：它能在单次扫描里完成原本需要两遍扫描加笛卡尔积的操作。什么时候该用？简单说，凡是“找每个部门工资第二高的员工”、“算相邻订单的时间差”、“统计累计销量”这些场景，窗口函数都比自连接简洁高效得多。
什么时候该用窗口函数替代自连接
核心判断标准只有一个：当你需要“按某个字段分组后，比较组内不同行之间的值”时，窗口函数就是最佳方案。比如找每个部门工资第二高的员工、计算相邻订单的时间差、统计累计销量——这些场景下硬写 JOIN 不仅逻辑绕，还容易漏数据或重复计数。窗口函数虽然不是万能的，但它能直接在单次扫描中完成原本需要两遍扫描加笛卡尔积的操作，大幅提升查询效率。
ROW_NUMBER() 与 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 子句，这个顺序决定了它不能参与初始行过滤，但能参与最终排序或分页处理。记住这条关键规则，就能避开大部分常见的用法陷阱。
