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。

再给窗口函数思路。

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

简化版

详细版