ROW_NUMBER差值法通过“真实值减行号”生成恒定分组标识：连续时差值不变，断开时差值突变，从而精准划分孤岛；需严格匹配粒度、规避旧版数据库变量风险。

ROW_NUMBER差值法是识别孤岛与缝隙最稳定、兼容性最好的通用解法，尤其适合没有LEAD/LAG或生成序列能力的旧版数据库（如MySQL 5.7、SQL Server 2000）。

为什么用 ROW_NUMBER() 差值能分出孤岛

连续序列中，真实值与行号同步递增，差值恒定；一旦出现缝隙，行号仍连续，但真实值跳变，差值就变了——这个变化点就是孤岛边界。

比如日期序列 '2026-04-01', '2026-04-02', '2026-04-04'，按日期排序后：

• 第1行：'2026-04-01' − ROW_NUMBER=1 → 差值 = '2026-04-01'
• 第2行：'2026-04-02' − ROW_NUMBER=2 → 差值 = '2026-04-01'
• 第3行：'2026-04-04' − ROW_NUMBER=3 → 差值 = '2026-04-01'？不对，实际是 '2026-04-01'（没错）→ 等等，这里得转成天数计算才对

正确做法是把日期转为整数天数再减：用 DATE_PART('day', login_date::timestamp - '1970-01-01'::date)（PostgreSQL）或 TO_DAYS(login_date)（MySQL），再减 ROW_NUMBER()。差值相同即属同一孤岛。

MySQL 5.7 下必须避开的两个坑

MySQL 5.7 不支持窗口函数，但可以用变量模拟 ROW_NUMBER()，不过有严重风险：

• @rn := @rn + 1 的执行顺序不保证，尤其在 ORDER BY 和变量赋值混用时，结果可能错乱
• 如果查询带 LIMIT 或被优化器重写，变量计数会跳号或重复
• 更稳妥的做法是用自连接统计“比当前值小的记录数”来模拟行号，但性能差，数据量过万就明显卡顿

所以，若必须用 MySQL 5.7，优先考虑业务侧补全缺失日期（如用应用层生成日期序列 LEFT JOIN），而不是硬扛变量方案。

处理重叠或含区间的数据要先归一化

原始数据若含 start_date/end_date 且允许重叠（如设备运行时段），直接套用差值法会漏判缝隙——因为两段重叠的区间本应合并为一个孤岛，但按起始日排序后，ROW_NUMBER 会把它们当独立行处理。

必须前置步骤：

• 用标准区间合并逻辑（如自连接+递归CTE或变量遍历）先压平所有重叠区间
• 得到无重叠的最小覆盖区间集合后，再对每个区间的 start_date 应用差值法找孤岛
• 否则，island_id 会把本该连在一起的时段切成多段

差值类型必须和序列粒度严格匹配

差值不是随便减出来的，它隐含了“单位”假设：

• 对按天连续的日期，必须用“天数差”，不能用字符串相减或默认日期减法（不同DB行为不一致）
• 对整数ID序列，直接用 id - ROW_NUMBER() 即可
• 对小时级时间戳，需先 EXTRACT(EPOCH FROM ts) / 3600 转为整数小时再减，否则小数部分会导致差值不恒定
• PostgreSQL 中 login_date::date - ROW_NUMBER() OVER (...)::int 是安全的；MySQL 中必须用 TO_DAYS(login_date) - @rn

粒度错一点，整个孤岛分组就全垮——这是上线后才发现、最难排查的一类问题。