怎样在SQL中用窗口函数解决断号与连续序列检测-SQL-PHP中文网

LAG()和LEAD()是断号检测第一选择，因能直接获取相邻行值，无需自连接或子查询，性能优、逻辑简明；断号本质是“当前ID与上一行ID差值≠1”，LAG(id)恰好提供上一行ID，配合ORDER BY id ASC（需确保id非空无重复）即可精准识别跳变。

怎样在sql中用窗口函数解决断号与连续序列检测

为什么 `LAG()` 和 `LEAD()` 是断号检测的第一选择

因为它们能直接拿到相邻行的值，无需自连接或子查询，性能好、逻辑直白。断号的本质是「当前 ID 与上一行 ID 的差值 ≠ 1」，而 LAG(id) 正好提供上一行的 id 值。

常见错误是写成 LAG(id, 1) OVER (ORDER BY id) 却忽略 ORDER BY 必须确定且唯一——如果 id 有重复或 NULL，结果会错乱甚至跳过断点。

务必用 ORDER BY id ASC，且确保 id 列非空、无重复（否则先去重或加辅助排序列）
断号判断条件写成 id - LAG(id) OVER (ORDER BY id) > 1，不是 != 1（因为可能跳过多个号，比如从 5 直接到 9）
首行的 LAG() 返回 NULL，需用 WHERE 过滤掉或用 COALESCE 处理，否则 id - NULL 全为 NULL，查不到首段断号

用 `ROW_NUMBER()` 标记连续组别来识别连续序列

连续序列检测的关键不是看绝对值，而是看「序号差是否恒定」。比如数据是 [101, 102, 103, 105, 106, 110]，真正连续的是 101–103、105–106、110 各自成组——这靠 id - ROW_NUMBER() OVER (ORDER BY id) 的稳定值就能区分。

这个差值叫「连续锚点」，同一连续段内它不变；一旦断开，锚点就变。这是最稳定、兼容性最好的方法，所有主流 SQL 引擎（PostgreSQL、SQL Server、MySQL 8+、Doris、StarRocks）都支持。

不要用 RANK() 或 DENSE_RANK() 替代 ROW_NUMBER()，它们对重复值处理不同，会破坏锚点一致性
如果原始数据含 NULL，ORDER BY 会把 NULL 排在最前或最后（依数据库而异），导致锚点错位，建议先 WHERE id IS NOT NULL

示例片段：

SELECT id, id - ROW_NUMBER() OVER (ORDER BY id) AS grp
FROM t
WHERE id IS NOT NULL;

结果中相同 grp 值的行即属同一连续块

如何合并连续范围：用 `MIN()` 和 `MAX()` 配合分组

拿到 grp 后，只需按它分组，取每组最小和最大 id，就是连续区间的起止。但要注意边界语义：如果原表主键是 INT 类型，且业务上允许「单值区间」（如 110–110），那就保留；否则可加 HAVING COUNT(*) > 1 过滤掉孤立值。

容易被忽略的是性能陷阱：如果表很大且没索引，ORDER BY id 可能触发全表排序。此时必须确认 id 列上有索引（尤其是聚集索引或主键索引）。

MySQL 8+ 中，若 id 是主键，ORDER BY id 几乎不额外排序；反之若只有普通索引或无索引，执行计划里会出现 Using filesort
PostgreSQL 需检查 EXPLAIN 是否用了 Index Scan 而非 Seq Scan
输出列建议明确写成 MIN(id) AS start_id, MAX(id) AS end_id，避免依赖隐式别名