无需指定单个ID，如何批量检测各Unique ID序列间隙？

我完全懂你的痛点——翻了一堆案例却找不到能适配大数据量的批量检测方法，每次在WHERE里指定单个ID效率太低了。下面给你一套可以自动遍历所有Unique ID、直接输出每个ID序列间隙的解决方案，结果正好包含你需要的id、gap_starts_at、gap_ends_at字段：

核心思路 #

利用窗口函数LEAD()按每个Unique ID分组，获取当前序列值的下一个序列项，通过判断前后项是否连续来定位间隙，最后计算出间隙的起止范围。

基础版SQL代码（适用于无重复序列值的场景） #

假设你的表名为your_table，Unique ID字段是unique_id，序列字段是sequence_num，代码如下：

WITH sequence_gaps AS (
    SELECT
        unique_id,
        sequence_num,
        -- 按每个Unique ID分组，获取当前序列的下一个值
        LEAD(sequence_num) OVER (PARTITION BY unique_id ORDER BY sequence_num) AS next_sequence
    FROM your_table
)
SELECT
    unique_id AS id,
    sequence_num + 1 AS gap_starts_at,
    next_sequence - 1 AS gap_ends_at
FROM sequence_gaps
-- 排除最后一个序列项（没有下一个值的情况）
WHERE next_sequence IS NOT NULL 
  -- 筛选出前后序列不连续的情况，即存在间隙
  AND next_sequence > sequence_num + 1
-- 按ID和间隙起始值排序，方便查看
ORDER BY unique_id, gap_starts_at;

进阶版SQL代码（处理序列存在重复值的场景） #

如果你的序列字段可能存在重复值，先做去重处理再检测间隙：

WITH deduplicated_sequences AS (
    -- 先去除每个Unique ID下的重复序列值
    SELECT DISTINCT
        unique_id,
        sequence_num
    FROM your_table
),
sequence_gaps AS (
    SELECT
        unique_id,
        sequence_num,
        LEAD(sequence_num) OVER (PARTITION BY unique_id ORDER BY sequence_num) AS next_sequence
    FROM deduplicated_sequences
)
SELECT
    unique_id AS id,
    sequence_num + 1 AS gap_starts_at,
    next_sequence - 1 AS gap_ends_at
FROM sequence_gaps
WHERE next_sequence IS NOT NULL 
  AND next_sequence > sequence_num + 1
ORDER BY unique_id, gap_starts_at;

方案优势 #

• 无需手动指定任何ID，自动遍历表中所有Unique ID
• 基于窗口函数实现，在主流数据库（MySQL 8.0+、PostgreSQL、SQL Server等）中性能优异，适配大数据量场景
• 输出结果完全符合你的需求，清晰展示每个ID对应的所有序列间隙

内容的提问来源于stack exchange，提问作者Kristof Meirlaen