Gaps and Islands问题指识别连续记录组（Islands）及其中断区间（Gaps）；GROUP BY无法感知连续性，需用窗口函数如ROW_NUMBER()与字段做差构造岛标识，再通过LEAD()定位空隙。
什么是Gaps and Islands问题，为什么不能只用GROUP BY
“孤岛（Islands）”指连续的、按时间或序号排列的相邻记录组；“空隙（Gaps）”则是这些组之间的中断区间。典型场景如：用户连续登录天数、设备连续在线时段、订单号连续段识别。直接用GROUP BY会失效——它按字段值分组，但无法感知“连续性”。比如日期列2024-01-01、2024-01-02、2024-01-04，GROUP BY YEAR(date), MONTH(date)会把它们全归进同一月，却无法指出2024-01-03是空隙。
窗口函数的核心价值在于引入行间逻辑关系：ROW_NUMBER()生成严格递增序号，再与原始字段做差，相同差值即构成一个“孤岛”。
用ROW_NUMBER()构造岛标识：关键差值法
对有序字段（如date或id），计算ROW_NUMBER() OVER (ORDER BY date)，再用该字段本身（转为整数）减去序号。连续记录的差值恒定，断点处差值突变。
- 日期型：先用
TO_DAYS(date)（MySQL）或DATE_PART('day', date::timestamp - '1970-01-01'::date)（PostgreSQL）转为整数天数 - 整数ID型：直接用
id - ROW_NUMBER() OVER (ORDER BY id) - 注意排序必须严格一致：
ORDER BY子句在ROW_NUMBER()和后续GROUP BY中要完全相同，否则差值失去意义
示例（PostgreSQL）：
SELECT MIN(date) AS island_start,
MAX(date) AS island_end,
COUNT(*) AS length
FROM (
SELECT date,
date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY date) AS island_id
FROM login_log
WHERE user_id = 123
) t
GROUP BY island_id;识别Gaps：用LEAD()定位下一个起点
空隙本质是当前记录最大值与下一条记录最小值之间的间隔。用LEAD()取下一行值，再与当前行比较即可。
- 对孤岛结果集（已含
island_end），再套一层查询，用LEAD(island_end) OVER (ORDER BY island_end) - Gap起始 =
island_end + 1，Gap结束 =LEAD(island_end) - 1 - 需过滤掉
LEAD()返回NULL的末尾行，避免产生无效空隙 - 若原始数据含时间戳，注意单位：加
INTERVAL '1 second'还是'1 day'取决于业务粒度
示例（MySQL 8.0+）：
WITH islands AS (
SELECT MIN(dt) AS start_dt, MAX(dt) AS end_dt
FROM (
SELECT dt, dt - INTERVAL ROW_NUMBER() OVER (ORDER BY dt) DAY AS grp
FROM events
) t
GROUP BY grp
)
SELECT end_dt + INTERVAL 1 DAY AS gap_start,
LEAD(start_dt) OVER (ORDER BY start_dt) - INTERVAL 1 DAY AS gap_end
FROM islands
WHERE LEAD(start_dt) OVER (ORDER BY start_dt) IS NOT NULL
AND end_dt < LEAD(start_dt) OVER (ORDER BY start_dt) - INTERVAL 1 DAY;性能与边界情况提醒
大表上运行这类查询容易慢，因为涉及多次排序和窗口计算。实际部署前必须确认三点：
- 用于排序的字段（如
date或id）有索引，且索引顺序匹配ORDER BY子句 - 避免在
ROW_NUMBER()中使用DISTINCT或复杂表达式，会导致排序开销陡增 - NULL值必须提前清理：
ROW_NUMBER()和LEAD()在遇到NULL时行为不一致，MySQL可能跳过，PostgreSQL默认置为NULL，引发差值错乱 - 跨年/跨月边界（如
2023-12-31→2024-01-01）不是问题，只要用整数天数差，但时区混用会导致隐式转换失败
最易被忽略的是排序稳定性：如果原始数据存在并列值（如多条记录date = '2024-01-01'），ROW_NUMBER()分配的序号是不确定的，可能导致同一组数据在不同执行中被拆成多个岛。此时必须在ORDER BY里追加唯一字段，例如ORDER BY date, id。
