第 12.8 课 · 阅读时间：约 7 分钟

间隙与岛屿（gaps and islands）是一类关于序列的问题：找出连续值组成的片段（岛屿）以及它们之间的空缺（间隙）。典型问题有：“每个用户最长的连续登录天数”“传感器持续发送数据的时间段”“缺失的发票编号”。这是 SQL 面试中备受青睐的进阶题。在本课中，你将学习用 ROW_NUMBER 把连续日期分组成岛屿的技巧，用 LEAD 找出间隙，并计算每位客户最长的连续天数。

SQL 间隙与岛屿

岛屿是一段没有空缺的值：连续的日期、连续的数字。间隙是两个岛屿之间缺失的一段值。难点在于 SQL 处理的是集合，而“连续”是行与行之间的关系。借助窗口函数，这个问题只需一条简短的查询。

示例在 Sakila 示例数据库（MySQL 8.0）上运行。Sakila 中的租赁记录是按一周一周的时间段录入的，所以岛屿非常明显。

核心技巧：日期减去行号

取去重后的日期并排序编号。在一段连续的日期中，日期每次加一天，行号每次加一，因此日期 − 行号保持不变。当某一天缺失时，日期会向前跳，而行号不会，于是差值发生变化。这个差值就是岛屿的键。

来看客户 42 在 2005 年 7 月 13 日之前的租赁日期：

WITH customer_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
    WHERE customer_id = 42
      AND rental_date < '2005-07-13'
)
SELECT rental_day,
       ROW_NUMBER() OVER (ORDER BY rental_day) AS rn,
       DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (ORDER BY rental_day) DAY) AS island_key
FROM customer_days
ORDER BY rental_day;

结果：9 天，组成 4 个岛屿；例如，7 月 9 日到 12 日的键都是 2005-07-03。

键本身没有含义，只需在同一个岛屿内相同、在不同岛屿间不同即可。7 月 8 日缺失，所以 7 月 7 日单独构成一个岛屿。

岛屿：有租赁的时间段

现在按键分组，取每个岛屿的第一天和最后一天：

WITH rental_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
),
grouped AS (
    SELECT rental_day,
           DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (ORDER BY rental_day) DAY) AS island_key
    FROM rental_days
)
SELECT MIN(rental_day) AS island_start,
       MAX(rental_day) AS island_end,
       COUNT(*) AS days
FROM grouped
GROUP BY island_key
ORDER BY island_start;

结果：6 个岛屿：2005 年的五个各含 8 个连续日期的时间段，以及单独的一天——2006 年 2 月 14 日。

第一个 CTE 中的 DISTINCT 必不可少。如果同一天出现两次，它会得到两个行号，差值就会把岛屿拆开。对于整数序列，用同样的方法计算 value - ROW_NUMBER() 即可。

间隙：没有租赁的时间段

要找间隙，就用 LEAD() 把每一天与下一天比较。如果下一天比当前日期晚一天以上，它们之间就有空缺：

WITH rental_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
),
with_next AS (
    SELECT rental_day,
           LEAD(rental_day) OVER (ORDER BY rental_day) AS next_day
    FROM rental_days
)
SELECT DATE_ADD(rental_day, INTERVAL 1 DAY) AS gap_start,
       DATE_SUB(next_day, INTERVAL 1 DAY) AS gap_end,
       DATEDIFF(next_day, rental_day) - 1 AS missing_days
FROM with_next
WHERE DATEDIFF(next_day, rental_day) > 1
ORDER BY gap_start;

结果：5 个间隙：2005 年各时间段之间有四个 13 天的间隙，还有一个 174 天的间隙，从 2005 年 8 月 24 日到 2006 年 2 月 13 日。

同样的查询换成数字就能找出缺失的 id，例如被跳过的发票编号：把 DATEDIFF(next_day, rental_day) 换成 next_id - id。

每位客户的连续天数

面试版本：“求出每位客户连续租赁的最长天数”。加上 PARTITION BY customer_id，让每位客户有自己的编号，然后聚合两次：先求每位客户的岛屿，再取最长的岛屿：

WITH customer_days AS (
    SELECT DISTINCT customer_id, DATE(rental_date) AS rental_day
    FROM rental
),
grouped AS (
    SELECT customer_id, rental_day,
           DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY rental_day) DAY) AS island_key
    FROM customer_days
),
streaks AS (
    SELECT customer_id, island_key, COUNT(*) AS streak_days
    FROM grouped
    GROUP BY customer_id, island_key
)
SELECT customer_id, MAX(streak_days) AS longest_streak
FROM streaks
GROUP BY customer_id
ORDER BY longest_streak DESC, customer_id
LIMIT 5;

结果：客户 42、181、290、306 和 373 都连续 8 天租赁过电影；总共有 10 位客户有 8 天的连续记录。

这里最长的连续天数只能是 8 天，因为租赁店本身只在 8 天的时间段内有业务。换成真实的活动数据，同样的查询可以找出连续登录天数、连续工作日或传感器连续传输数据的时间段。

练习

• 间隙与岛屿问题

要点：

• 岛屿是连续值组成的片段；间隙是它们之间缺失的片段。
• 在一段连续日期中，date - ROW_NUMBER() 是常数：按它分组即可得到岛屿。
• 先用 DISTINCT 去重，否则同一天会得到多个行号，岛屿就会被拆开。
• LEAD() 把每个值与下一个值比较，从而找出间隙。
• PARTITION BY 让每个对象拥有自己的岛屿；第二次聚合求出最长的连续天数。

常见问题

SQL 中的间隙与岛屿问题是什么？

这是一类关于值序列的问题：岛屿是没有空缺的连续值片段（日期、数字），间隙是它们之间缺失的值。例如：连续登录天数、持续活跃的时间段、缺失的发票编号。

如何在 SQL 中找出连续的日期？

用 ROW_NUMBER() OVER (ORDER BY day) 为去重后的日期编号，再从日期中减去这个天数。连续的日期会得到相同的结果，因此按这个值 GROUP BY，并使用 MIN(day)、MAX(day) 和 COUNT(*)，就能得到每一段连续日期。

如何在 SQL 中查找缺失的日期或编号？

用 LEAD(value) OVER (ORDER BY value) 取得每一行的下一个值，保留差值大于一个步长的行。间隙从当前值加一个步长开始，到下一个值减一个步长结束。要列出每一个缺失的日期，请连接一个生成的日历（第 6.6 课）。

如何求出每个用户最长的连续天数？

用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) 按用户把连续日期分成岛屿，统计每个岛屿的天数，再对每个用户取这些天数的 MAX。