直接用row_number()无法识别连续组，因其仅按全局排序编号，相同状态被其他状态隔开时编号仍连续递增；需用“行号差”法：全局row_number()减去按状态分组的row_number()，差值恒定即为同一连续段标识grp_id。
为什么直接用 ROW_NUMBER() 无法识别连续组
因为 ROW_NUMBER() 只按全局排序编号，相同状态的记录如果被其他状态“隔开”，编号仍是连续递增的，没法体现“连续性”。比如状态列是 ['A','A','B','A','A']，全局 ROW_NUMBER() 给出 [1,2,3,4,5]，但你要的是把第1–2个 A 和第4–5个 A 拆成两个独立连续段。
用“行号差”构造连续组 ID 的原理
核心思路：对整个表按时间/顺序列排序后，分别计算两个 ROW_NUMBER() —— 一个按全部顺序，一个按状态分组再排序。二者相减，同一连续状态段内的差值恒定。
-
ROW_NUMBER() OVER (ORDER BY ts)：全局序号（假设ts是时间戳或序号列） -
ROW_NUMBER() OVER (PARTITION BY status ORDER BY ts)：每种状态内部的局部序号 - 二者相减 → 同一连续块中，这个差值不变，可作为
grp_id
示例（简化数据）：
ts | status | rn_all | rn_by_status | grp_id = rn_all - rn_by_status 1 | A | 1 | 1 | 0 2 | A | 2 | 2 | 0 3 | B | 3 | 1 | 2 4 | A | 4 | 3 | 1 5 | A | 5 | 4 | 1
可见 status = 'A' 被自然拆成两组：grp_id = 0 和 grp_id = 1。
实际写法：嵌套子查询 or CTE
不能在同一个 SELECT 中直接用列别名参与计算，需用子查询或 CTE 暴露两个行号。
- 推荐 CTE，逻辑清晰、可读性强
-
status和排序字段（如ts或id）必须明确指定，否则结果不可靠 - 注意 NULL 状态值会自成一组，如有需要提前过滤或
COALESCE(status, '_NULL')
简短示例（PostgreSQL / SQL Server / BigQuery 均适用）：
WITH numbered AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY ts) AS rn_all,
ROW_NUMBER() OVER (PARTITION BY status ORDER BY ts) AS rn_by_status
FROM events
)
SELECT *,
rn_all - rn_by_status AS grp_id
FROM numbered;
后续聚合与常见陷阱
得到 grp_id 后，就能按 (status, grp_id) 分组统计连续段长度、起止时间等。但要注意：
- 排序字段必须严格单调且无重复，否则
ROW_NUMBER()结果不稳定；有重复时加id作为次级排序：ORDER BY ts, id - 某些数据库（如 MySQL 8.0+）支持窗口函数，但旧版 MySQL 不行，得换变量模拟，复杂度陡增
- 性能上，两个
ROW_NUMBER()都需全表扫描 + 排序，大数据量时注意索引：建议在(ts)或(status, ts)上建复合索引
连续段边界识别本身不难，难的是排序依据是否真正反映业务顺序——比如日志里 ts 精度不足、设备时钟不同步，grp_id 就会错乱。
