SQL 中如何查询连续区间？Gaps and Islands 问题怎么解？

简化版

Gaps and Islands 是查询连续区间的问题，例如连续登录天数、连续订单日期、连续编号段。常见解法是先排序，再用窗口函数生成行号，利用“日期减行号”或“编号减行号”在连续段内保持不变的特点分组。最后按这个分组求最小值、最大值和长度。

详细版

连续区间问题的难点是 SQL 天然按集合处理，不像程序循环那样逐行判断前后关系。窗口函数提供了排序后的行号、前一行值等能力，可以把连续性转换成可分组的 key。

如果是连续日期，常用 date - row_number * interval；如果是连续整数，常用 id - row_number()。同一个连续段内，这个差值不变；一旦中间断开，差值就变化。

面试时要注意先去重，否则同一天多条记录会破坏行号。

完整版教学

一、典型业务题

查连续登录 7 天的用户。

查连续缺货的日期区间。

查连续订单编号段。

查设备连续异常时间段。

这些题都可以归到 gaps and islands。

二、连续整数的基本解法

WITH t AS (
  SELECT id, id - ROW_NUMBER() OVER (ORDER BY id) AS grp
  FROM numbers
)
SELECT MIN(id) AS start_id, MAX(id) AS end_id, COUNT(*) AS len
FROM t
GROUP BY grp;

连续的 id 中，id - row_number 保持不变。

断开后，差值会发生变化。

三、连续日期的解法

WITH login_days AS (
  SELECT DISTINCT user_id, login_date
  FROM user_logins
),
t AS (
  SELECT
    user_id,
    login_date,
    login_date - ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) * INTERVAL '1 day' AS grp
  FROM login_days
)
SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days
FROM t
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;

不同数据库日期运算语法不同，但思路一致。

四、LAG 解法

也可以用 LAG 取上一行，判断当前行是否开启新分组。

然后对新分组标记做累计求和，得到区间编号。

这种写法更直观，也更适合复杂连续规则。

例如日期间隔不超过 5 分钟也算连续，就可以用 LAG 判断差值。

五、两种方式对比

解法	适合场景	特点
值减行号	严格连续整数或日期	简洁高效
`LAG` + 累计分组	连续规则复杂	可读性更强
自连接	无窗口函数时	SQL 较复杂
应用层处理	数据量小或规则复杂	失去数据库集合优势

六、常见边界

同一天多次登录要先去重。

日期是否按自然日、业务日、时区计算要明确。

缺失日期要判断是无记录还是值为 0。

连续 7 天是至少 7 天，还是刚好 7 天，也要看题意。

连续区间题最容易错在数据预处理，而不是窗口函数本身。

七、误区和追问

误区：直接 GROUP BY 用户就能算连续天数。 GROUP BY 只能聚合总量，不能识别中间是否断开。
误区：有 7 条登录记录就是连续 7 天。 7 条记录可能分散在不同日期。
误区：日期连续不需要去重。 同一天多条记录会影响 ROW_NUMBER。
追问：不用窗口函数能做吗？ 可以用自连接或递归，但复杂度和可读性通常更差。
追问：间隔不超过 30 分钟算连续怎么做？ 用 LAG 比较前后时间差，再累计生成分组。
追问：如何找 gaps 而不是 islands？ 用 LAG 找相邻值之间的差距大于期望间隔的地方。

八、面试收束

先说明这是 gaps and islands。

再给窗口函数思路。

最后强调去重、排序、分区和业务连续规则。

九、常见误区与追问

误区：只记住 SQL 中如何查询连续区间？Gaps and Islands 问题怎么解？的结论就够了。 数据库题通常还要解释索引、事务、锁、执行计划或一致性边界，否则很容易被追问打穿。
误区：能查出结果就说明 SQL 或设计没问题。 还要看数据量扩大到 100 万行后是否仍能走合适索引、是否产生临时表或锁等待。
误区：所有场景都追求强一致。 读写分离、缓存、异步任务都可能牺牲一部分实时性，关键是说明业务是否允许。
追问：线上变慢时你先看什么？ 先看慢 SQL、执行计划、扫描行数、锁等待和连接池，再判断是 SQL 写法、索引还是并发问题。
追问：如何证明这个方案可落地？ 给出一个小数据例子，再补充约束、失败场景和回滚方案，避免只停留在概念层。

十、加强记忆

记 SQL 中如何查询连续区间？Gaps and Islands 问题怎么解？时，把它压成“语义正确、执行高效、并发安全”三件事。先说清这个知识点解决什么数据库问题，再用 1 个带数字的小例子说明数据量一大为什么会出差异，最后补上索引、锁、事务或执行计划里的易错点。这样面试官无论追问 SQL 写法、线上慢查，还是并发一致性，你都能沿着同一条线继续展开。

简化版

详细版