SQL窗口函数如何发现序列号断档

在检测序列断档方面，LAG()无疑是最为可靠的，它仅仅依赖于相邻行ID的差值，而不预设数据是连续的，也不假定起始值。LEAD()则更适合用于定位缺口区间。ROW_NUMBER()可就不适合用于真实断档的检测了，因为它生成的是序号，而不是真实的ID。此外，对于空表、NULL以及边界值，都需要进行显式的处理。

用 LAG() 直接比上一行值最可靠

序列断档的本质是当前 id 与前一个 id 不满足 +1 关系，LAG() 就是干这事的。它不依赖数据是否连续，也不假设最小值从 1 开始，只看相邻行差值。

• LAG(id) OVER (ORDER BY id) 必须配 ORDER BY id，否则结果无意义；没索引时会全表扫描
• 首行的 LAG() 返回 NULL，对应差值也是 NULL，这是正常现象，别用 WHERE gap IS NOT NULL 过滤——漏掉首行断档（比如数据从 100 开始）
• 写法示例：SELECT id, id - LAG(id) OVER (ORDER BY id) AS gap FROM t，只要 gap > 1 就说明缺号

LEAD() 更适合定位缺口区间

当序列跨度大（比如 MIN(id)=1、MAX(id)=1000000），硬生成全部数字再左连接会爆内存。LEAD() 可以只算出“缺哪一段”，再按段补。

• LEAD(id) OVER (ORDER BY id) 拿到下一行 id，和当前 id 相减减 1，得到中间缺几个号：LEAD(id) OVER (ORDER BY id) - id - 1 AS missing_count
• 真正要补号时，用 id + 1 AS start_gap 和 LEAD(id) OVER (ORDER BY id) - 1 AS end_gap 定义每段缺口边界
• PostgreSQL 可用 generate_series(start_gap, end_gap)；MySQL 需用递归 CTE 或数字表，别用变量模拟——易出错且不可靠

ROW_NUMBER() 不能用来检测真实断档

ROW_NUMBER() OVER (ORDER BY id) 生成的是“第几条记录”的序号，不是“本该是什么 ID”。它对重复、跳号、带前缀的业务 ID 完全无效。

• 原始数据是 1,2,4,7，ROW_NUMBER() 给出 1,2,3,4，你拿这个去跟 id 比，会误判 4 是“正确”的（因为 4 == 4），但其实缺了 3 和 5–6
• 如果 id 是字符串如 'ORD-2024-001'，ROW_NUMBER() 更是毫无参考价值
• 唯一能用 ROW_NUMBER() 的场景：你明确知道“合法 ID 集合 = 1 到 N 的整数”，且数据里只有缺失，没有重复或非法值

空表、NULL、边界值必须显式处理

生产环境里，MIN()/MAX() 遇到空表返回 NULL，generate_series(NULL, NULL) 直接报错；字段含 NULL 时，ORDER BY 行为在不同数据库也不一致。

• 查 MIN(id) 前先加 WHERE id IS NOT NULL，或用 COALESCE(MIN(id), 1) 防空
• ORDER BY id NULLS LAST 显式控制 NULL 排在哪——否则 PostgreSQL 默认 NULLS FIRST，MySQL 不支持该语法，结果不一致
• 补缺逻辑外层套一层 WHERE start_gap ，避免 LEAD() 在末行返回 NULL 导致区间计算出负数

实际运行时，真正容易卡住的地方，往往并非函数的编写方式，而是未能清晰界定“断档”的具体含义：它究竟是指物理存储上的间隙，还是业务规则所定义的合法值集合的缺失。对于前者，使用 LAG()/LEAD() 函数就足够了；而对于后者，则需要先明确那个集合的生成逻辑，然后再进行比对。