孤岛指连续有业务意义的记录（如用户连续3天登录），间隙是孤岛间的中断（如第4天未登录）；本质是通过时间/序号差值识别连续性，常用ROW_NUMBER()+差值法或LAG()定位。
什么是孤岛与间隙问题
孤岛（Island）指一组连续的、有业务意义的记录，比如用户连续 3 天登录；间隙（Gap）则是孤岛之间的中断，比如第 4 天没登录，第 5 天又登录了——中间那个“断点”就是间隙。这类问题本质是识别“连续性”，但原始数据没有显式标记连续或中断，得靠时间/序号字段的差值规律来推断。
用 ROW_NUMBER() + 差值法识别孤岛边界
核心思路是：对每组数据按时间（或序号）排序后，给每行打一个 ROW_NUMBER()，再用时间字段减去这个序号。同一孤岛内的差值会恒定，不同孤岛差值不同——这个恒定值就是“追赶指标”。
- 必须先
PARTITION BY user_id（或其他分组键），再ORDER BY login_date，否则跨用户混算 - 日期字段要转成可计算的整数（如
DATE(login_date)或TO_DAYS(login_date)），避免隐式类型转换失败 - 如果存在重复日期，
ORDER BY必须加二级排序（如ORDER BY login_date, id），否则ROW_NUMBER()结果不唯一 - 差值列建议用
COALESCE(TO_DAYS(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date), 0)避免 NULL 干扰分组
示例：
SELECT user_id, login_date,
TO_DAYS(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS island_key
FROM user_login;
用 LAG() 判断单次中断并定位间隙
当只需要知道“哪两次登录之间断了一天”，而不是完整划分孤岛时，LAG() 更轻量。它能直接拿到上一行的登录时间，和当前行做差判断是否为间隙。
-
LAG(login_date, 1)默认返回前 1 行值，无需写第二个参数 - 差值计算必须用日期函数（如
DATEDIFF(login_date, LAG(login_date) OVER (...))），不能直接减字符串 - 首行
LAG()返回NULL，DATEDIFF会得NULL，需用CASE WHEN过滤或跳过 - 若业务定义“间隔 >1 天才算间隙”，条件写成
DATEDIFF(...) > 1，别漏掉等号逻辑
示例：
SELECT user_id, login_date,
DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) AS gap_days
FROM user_login
WHERE DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) > 1;
聚合孤岛并取最长连续段时的性能陷阱
在 island_key 基础上再套一层 GROUP BY user_id, island_key 求连续天数，看似自然，但容易触发数据倾斜——某个活跃用户产生几千个连续登录日，全压在一个分区里。
- 别直接
GROUP BY user_id, island_key后MAX(COUNT(*))，COUNT(*) 窗口函数在多数引擎中不支持 - 改用两阶段：第一阶段生成
island_key和每段起止时间；第二阶段用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY island_length DESC)取 Top 1 - PostgreSQL 中可用
MIN(login_date) OVER (...) AS island_start+MAX(login_date) OVER (...) AS island_end配合ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，但注意该帧子句在某些版本下开销大 - MySQL 8.0+ 不支持
ROWS BETWEEN的无界窗口聚合，得用变量或 CTE 拆解
真正难的不是写出孤岛逻辑，而是当 user_id 出现长尾分布、且 island_key 计算涉及高精度时间戳时，执行计划里悄悄冒出的单点 shuffle 和内存溢出。
