如何利用SQL窗口函数解决孤岛与跨度问题_Gaps and Islands解析-SQL-PHP中文网

gaps and islands问题指识别连续记录组（islands）及其中断区间（gaps）；group by无法感知连续性，需用窗口函数如row_number()与字段做差构造岛标识，再通过lead()定位空隙。

如何利用sql窗口函数解决孤岛与跨度问题_gaps and islands解析

什么是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）：

Find Skills

一款AI工具，主要用于帮助用户在提出类似以下问题时发现并安装 Agent 技能：，适合需要提升相关任务效率的用户。

下载

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 <h3>性能与边界情况提醒</h3><p>大表上运行这类查询容易慢，因为涉及多次排序和窗口计算。实际部署前必须确认三点：</p>

用于排序的字段（如date或id）有索引，且索引顺序匹配ORDER BY子句
避免在ROW_NUMBER()中使用DISTINCT或复杂表达式，会导致排序开销陡增
NULL值必须提前清理：ROW_NUMBER()和LEAD()在遇到NULL时行为不一致，MySQL可能跳过，PostgreSQL默认置为NULL，引发差值错乱
跨年/跨月边界（如2023-12-31→2024-01-01）不是问题，只要用整数天数差，但时区混用会导致隐式转换失败