sql server中解决孤岛与缝隙问题，核心是用row_number()构造分组键识别连续段（如logindate-row_number()恒定即属同一孤岛），或用lead()/lag()定位断点（如lead(id)-id>1标识缝隙起始）；须先去重、统一日期类型、避免null干扰，且窗口函数需在cte或子查询中预计算再分组过滤。
直接说结论：SQL Server 中解决孤岛（islands）与缝隙（gaps）问题，核心不是选哪个函数，而是用 ROW_NUMBER() 或 LEAD()/LAG() 构造分组键或断点标识——前者适合找连续段，后者适合定位缺失起点。
用 ROW_NUMBER() + 日期/数值差生成孤岛分组键
连续登录、连续ID、连续订单号这类“孤岛”，本质是原始序列减去其有序位置后余值恒定。比如日期列 LoginDate，对每个用户按日期排序后，LoginDate - ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDate) 的结果相同，就属于同一孤岛。
- 必须先去重：同一天多次登录只留一条，否则
ROW_NUMBER()会把同一天拆成多行，破坏连续性 - 日期计算要显式转为
DATE类型，避免DATETIME的时分秒干扰差值 - 若原始字段是整数 ID，直接用
ID - ROW_NUMBER() OVER (ORDER BY ID)，但注意 ID 必须单调递增且无重复 - SQL Server 不支持
DATEADD(day, -RN, LoginDate)这种写法直接参与PARTITION BY，得用 CTE 或子查询先算出该差值列再分组
用 LEAD() 定位缝隙起始位置
缝隙检测的关键是发现“当前值和下一个值之间跳变超过预期步长”。对整数序列，步长为 1；对日期，步长为 1 天；对自定义步长（如每5分钟一条记录），需相应调整判断条件。
-
LEAD(ID) OVER (ORDER BY ID)必须搭配ORDER BY ID，否则结果无序、不可靠 - 常见错误是写成
WHERE LEAD(ID) - ID > 1却没处理首行的LEAD()返回NULL，应改为WHERE LEAD(ID) OVER (ORDER BY ID) - ID > 1并确保 WHERE 在窗口计算之后生效（即不能在同一个查询层级直接引用未定义别名） - 若要返回完整缝隙区间（如缺失 98–99），需用
LEAD(ID) OVER (...) - 1 AS gap_end和ID + 1 AS gap_start，再过滤gap_start - 性能上，1000 万行数据做一次
LEAD()窗口计算约 27 秒（实测），但若没在ID上建索引，可能卡在排序阶段而非计算本身
为什么不用 NTILE() 或 DENSE_RANK()？
这两个函数不反映物理顺序的连续性。NTILE(4) 是强行把数据均分成 4 组，跟“是否连着”无关；DENSE_RANK() 对重复值给相同排名，但无法识别中间空缺——比如 ID 序列为 [1,2,4,5]，DENSE_RANK() 返回 [1,2,3,4]，看不出 3 缺失了。
-
ROW_NUMBER()是唯一能稳定映射“逻辑位置”的函数，它不看值本身，只看排序后的位置 -
LAG()/LEAD()是唯一直接访问相邻行值的函数，适合做差值比对 - 所有其他窗口聚合函数（如
SUM()、MAX()）在此类问题中只能辅助打标，不能替代上述两个核心函数的角色
真正容易被忽略的是数据预处理环节：没去重的登录日志、含 NULL 的 ID 列、未强制排序的原始表，都会让 ROW_NUMBER() 和 LEAD() 输出错乱。别急着写复杂 CTE，先用 SELECT TOP 10 * FROM t ORDER BY key 确认数据真的“看起来连续”。
