连续区间问题是指识别某字段值在物理顺序上连续出现的起止位置，不能用GROUP BY直接解决，因其不感知行序且无法区分被隔开的相同值；需用窗口函数如ROW_NUMBER()差值法或LAG()累积标记断点来构造连续组标识。
什么是连续区间问题，为什么不能用GROUP BY直接解决
连续区间问题典型场景是：查出某字段值连续出现的起止位置（比如连续登录天数、连续订单号、连续状态为1的记录）。直接用 GROUP BY 会把所有相同值强行归为一组，但“连续”强调的是物理顺序+值一致+相邻无断点，而 GROUP BY 不感知行序，也不区分“相同值但被隔开”的情况。
关键在于识别“断点”——当前行和上一行的值不同时，就产生新区间。窗口函数能通过 LAG() 或 ROW_NUMBER() 构造辅助分组标识。
用 ROW_NUMBER() 差值法构造连续组ID
核心思路：对全表按时间/序号排序后，分别计算两个 ROW_NUMBER()：
- 按整体顺序编号：
ROW_NUMBER() OVER (ORDER BY dt) - 按值分组再编号：
ROW_NUMBER() OVER (PARTITION BY status ORDER BY dt)
两者相减结果相同的行，就属于同一连续段。
SELECT dt, status,
ROW_NUMBER() OVER (ORDER BY dt)
- ROW_NUMBER() OVER (PARTITION BY status ORDER BY dt) AS grp_id
FROM login_log
ORDER BY dt;
这个 grp_id 就是连续区间的唯一标识。后续可 GROUP BY status, grp_id 求每段的 MIN(dt) 和 MAX(dt)。
注意点：
- 排序字段必须严格唯一（否则
ROW_NUMBER()结果不稳定），建议加id作为次级排序 -
PARTITION BY字段不能为NULL，否则整组会被跳过 - 差值法在 PostgreSQL / SQL Server / Oracle 都适用，但 SQLite 3.25+ 才支持窗口函数
用 LAG() + 条件累积标记断点
更直观的方式是用 LAG() 判断当前行是否为新区间起点：
SELECT dt, status,
CASE WHEN LAG(status) OVER (ORDER BY dt) = status THEN 0 ELSE 1 END AS is_start
FROM login_log;
然后对 is_start 做累计求和：SUM(is_start) OVER (ORDER BY dt)，结果就是连续段编号。
优点是逻辑直白，适合调试；缺点是多一层嵌套，部分引擎优化不如差值法高效。
常见错误：
- 忘记
ORDER BY导致LAG()返回随机值 - 在
WHERE中提前过滤掉某些状态，破坏了原始序列连续性判断 - 把
LAG(status)写成LAG(status, 1, 'default')时默认值类型与 status 不兼容，引发隐式转换错误
性能与边界情况提醒
连续区间问题本质是全表扫描+多次排序，数据量大时务必确认：
- 排序字段有索引（如
dt或复合索引(status, dt)） - 避免在子查询里嵌套三层以上窗口函数，MySQL 8.0 对嵌套深度敏感
- 当存在大量重复时间戳时，
ORDER BY dt, id比单用dt更可靠
最易被忽略的是“空值处理”：如果 status 允许为 NULL，PARTITION BY status 会把所有 NULL 归为一组，但 LAG() 比较时 NULL = NULL 返回 UNKNOWN，导致断点判断失效。需要显式用 COALESCE(status, -1) 或 IS NULL 单独处理。
