窗口函数结合嵌套查询能定位连续段，因ROW_NUMBER()差值可构造稳定Island ID，外层GROUP BY该ID即可聚合每段起止；LAG()则适用于基于时间断点的新岛识别。
为什么直接用窗口函数加嵌套查询能定位连续段
MySQL 8.0 引入了窗口函数后，LAG()、ROW_NUMBER() 和 COUNT() OVER() 这类函数可以按序生成“相对位置标识”，而 Gaps and Islands 的核心就是把物理上不连续的行，通过差值分组映射成逻辑上连续的块。嵌套查询在这里不是炫技，而是必须——外层对内层计算出的“分组键”做 GROUP BY，否则无法聚合出每个 Island 的起止时间或 ID 范围。
用 ROW_NUMBER() - ROW_NUMBER() OVER (PARTITION BY ...) 构造 Island ID
这是最稳定、兼容性最好的构造方式，尤其适合按状态/类别划分连续段的场景（比如连续登录、连续订单、设备在线时段）。关键在于两个 ROW_NUMBER() 的排序逻辑要一致，仅分区条件不同。
常见错误现象：SELECT id, dt, status FROM t ORDER BY dt 看起来有序，但没在窗口函数里显式写 ORDER BY dt，导致 ROW_NUMBER() 结果不可预测；或者内外层 ORDER BY 字段类型不一致（比如用 dt 排序，但 dt 是 VARCHAR），造成隐式转换打乱顺序。
实操建议：
- 内层
ROW_NUMBER() OVER (ORDER BY dt)给所有行全局编号 - 外层
ROW_NUMBER() OVER (PARTITION BY status ORDER BY dt)按状态分组编号 - 两者的差值
rn_all - rn_by_status就是该行所属 Island 的唯一标识 - 把这个差值放在子查询中，外层再按它
GROUP BY即可提取每段的MIN(dt)和MAX(dt)
示例片段：
SELECT
status,
MIN(dt) AS start_dt,
MAX(dt) AS end_dt,
COUNT(*) AS length
FROM (
SELECT
status, dt,
ROW_NUMBER() OVER (ORDER BY dt)
- ROW_NUMBER() OVER (PARTITION BY status ORDER BY dt) AS island_id
FROM events
) t
GROUP BY status, island_id;遇到时间断点（Gaps）时，LAG() + 条件标记比差值法更直观
当目标是识别“中断超过 N 分钟就算新 Island”，比如传感器心跳丢失检测，用差值法会失效，因为断点不体现在 status 变化上，而体现在时间间隔上。这时必须用 LAG() 获取前一行时间，再用 CASE WHEN 打标。
容易踩的坑：
-
LAG(dt) OVER (ORDER BY dt)返回NULL的第一行没处理，导致is_new_island列出现NULL，外层SUM() OVER()累计时断裂 - 时间单位不统一：数据库里
dt是DATETIME，但用TIMESTAMPDIFF(SECOND, ..., ...)计算后没转成分钟，阈值设成5却实际比的是 5 秒 - 没加
ROWS UNBOUNDED PRECEDING，导致SUM(is_new_island) OVER(...)默认使用RANGE语义，在时间值重复时聚合错行
正确写法要点：
- 第一行用
COALESCE(LAG(dt) OVER (...), dt)或直接IS NULL判断，强制设is_new_island = 1 - 时间差用
TIMESTAMPDIFF(MINUTE, prev_dt, dt) > 5显式指定单位 -
SUM(is_new_island) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING)确保逐行累加不跳过
性能和索引注意事项
这类查询天然需要全表扫描+排序，ORDER BY 字段必须有索引，否则 ROW_NUMBER() 和 LAG() 会极慢。如果数据量超百万，别指望单条 SQL 实时返回。
实操建议：
- 组合索引优先建在
(status, dt)或(dt)上，取决于主过滤条件是状态还是时间范围 - 避免在子查询里
SELECT *，只选后续真要用的字段，减少临时表体积 - 如果业务允许近似结果，可先用
WHERE dt >= DATE_SUB(NOW(), INTERVAL 7 DAY)缩小数据集再跑窗口逻辑 -
EXPLAIN FORMAT=TREE看执行计划里有没有Using filesort；有且没走索引，说明排序字段缺失索引
复杂点往往不在逻辑，而在你忘了 ORDER BY 字段没索引，或者时间字段类型是 VARCHAR 导致排序失效——这两处一错，整个嵌套结构就变成全表扫描黑洞。
