孤岛与空缺问题指识别连续记录段（孤岛）及其间断裂区间（空缺），因group by不保序且无法感知行间关系，故无法直接解决；必须依赖窗口函数——用row_number()差值法分组识别孤岛，或用lag/lead定位空缺边界。
什么是孤岛与空缺问题，为什么不能只用 GROUP BY
孤岛（islands）指连续的、满足某条件的记录段；空缺（gaps）则是这些段之间的断裂区间。比如用户连续登录7天是一段孤岛，中间断开2天就是空缺。这类问题无法靠 GROUP BY 直接解决——因为连续性依赖行序，而 GROUP BY 不保序，也不感知相邻行关系。
窗口函数是唯一能同时访问当前行和邻近行（通过 LAG/LEAD）或按逻辑分组重排序（通过 ROW_NUMBER() 差值法）的 SQL 机制。
用 ROW_NUMBER() 差值法识别孤岛
核心思路：对有序数据打上自然序号 ROW_NUMBER()，再减去业务字段（如日期、ID）的归一化序号，相同差值即属同一孤岛。
- 假设表
logins有字段user_id和login_date，需找每个用户连续登录段 - 先按
user_id,login_date排序，生成行号：ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 再将
login_date转为距某基准日的天数（如login_date - '2000-01-01'::DATE），该值本身也随连续日期线性增长 - 二者相减得到“岛标识”：
(login_date - '2000-01-01'::DATE) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 同一
user_id下该差值相同的行，就构成一个孤岛
示例片段：
SELECT
user_id,
MIN(login_date) AS island_start,
MAX(login_date) AS island_end,
COUNT(*) AS days
FROM (
SELECT *,
(login_date - '2000-01-01'::DATE) -
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS island_id
FROM logins
) t
GROUP BY user_id, island_id;
用 LAG/LEAD 找空缺边界
空缺本质是当前行与前一行（或后一行）在关键字段上的不连续。用 LAG() 可直接拿到前一行的值，从而判断是否中断。
- 对
login_date排序后，用LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)获取上一次登录日 - 若
login_date - LAG(...) > 1，说明中间至少空缺1天，此处就是空缺起点 - 同理，
LEAD(login_date)可定位空缺终点：若LEAD(...) - login_date > 1，则当前行是空缺前最后一日 - 注意：空缺区间需两行配合推导，单靠
LAG只能标记“中断发生点”，要输出完整空缺范围得做自连接或再套一层窗口
简易中断标记示例：
SELECT user_id, login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date, login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS gap_days FROM logins WHERE login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) > 1;
常见坑：ORDER BY 必须明确，NULL 和边界值要提前过滤
窗口函数的 ORDER BY 子句不是可选的——尤其在 LAG/LEAD 和差值法中，缺了它结果完全不可控。另外几个高频翻车点：
-
LAG()对首行返回NULL，直接参与减法会令整行变NULL；务必用COALESCE(LAG(...), ...)或在外层WHERE过滤掉首行 - 日期字段若含时分秒，
login_date::DATE强转不彻底会导致“同一天不同时间”被误判为空缺 - 差值法中，若业务字段非整型（如字符串 ID），需先映射为单调递增整数，否则差值无意义
- PostgreSQL 中
ROW_NUMBER()和RANK()在并列时行为不同，孤岛问题必须用ROW_NUMBER()，否则相同日期会共享序号，破坏差值唯一性
真正难的不是写出第一个正确查询，而是当数据里混着重复日期、跨年、多用户交叉、时区偏移时，差值表达式是否还稳。
