“连续相同状态”指按时间排序后相邻行status值相同且未被其他状态打断的序列，需用差值法（ROW_NUMBER()两层相减）生成分组标签grp再聚合，而非直接GROUP BY status。

什么是“连续相同状态”？先看典型错误现象

如果直接写 GROUP BY status，结果会把状态相同的记录一股脑归并到一起。看上去很省事，但问题也恰恰出在这里：像“登录→登出→登录”这样的序列，最终只会被压成两段。可在真实业务场景里，这两次“登录”之间明明被“登出”打断了，不能简单视为同一段连续状态。所谓真正的连续，关键不在于值是否相同，而在于按时间排好序之后，相邻记录的 status 一致，并且中间没有夹着其他状态。

核心解法：用 LAG() 或差值法识别连续段

关键不是分组本身，而是先给每段连续状态打上唯一标签（grp），再按这个标签聚合。

• LAG(status) OVER (PARTITION BY user_id ORDER BY event_time) 可比当前行和上一行状态是否一致，但只能判断“是否变化”，不能直接生成分组ID
• 更稳的做法是：构造一个随“状态切换”而递增的序列，常用差值法：
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) - ROW_NUMBER() OVER (PARTITION BY user_id, status ORDER BY event_time)
  这个差值在连续相同 status 区间内恒定，一变就跳变，天然形成 grp
• PostgreSQL/MySQL 8.0+/SQL Server 都支持；MySQL 5.7 不支持窗口函数，得用变量模拟（易出错，不推荐）

合并时保留首尾时间、持续时长等关键信息

仅用 MIN(event_time) 和 MAX(event_time) 算起止时间最常见，但要注意：

• 如果原始时间是 TIMESTAMP 类型，直接相减可能得秒数（MySQL）或 interval（PostgreSQL），需用 TIMEDIFF() / AGE() / DATEDIFF() 转换
• 想统计“连续登录了几天”，别直接用 COUNT(*)——那是记录条数，应取 DATEDIFF(MAX(event_time), MIN(event_time)) + 1
• 若状态含“进行中”类未结束项，MAX(event_time) 可能失真，建议加 WHERE status != 'pending' 过滤

容易漏掉的边界情况：用户跨天、空状态、多字段联合判断

真实日志里，status 字段可能为空、为 NULL，或需结合 user_id + device_id 才算真正连续——这时 PARTITION BY 必须同步扩展：

• PARTITION BY user_id, device_id ORDER BY event_time，否则同一用户不同设备的登录会串在一起
• NULL 状态默认不参与 LAG() 比较，会导致误判连续，建议提前用 COALESCE(status, 'unknown') 统一处理
• 时间精度问题：毫秒级时间戳若没去重，可能因微小差异把本该连续的两行拆开，可先用 DATE(event_time) 或 FLOOR(UNIX_TIMESTAMP(event_time)/60) 对齐到分钟级

说到底，连续状态合并这件事，关键在于“找断点”，而不是真去“找重复”。断点一旦判断准确，后续的聚合基本就是顺着往下做的执行工作。真正最容易出问题的，几乎总是这几个地方：PARTITION BY 少了字段，时间排序没有补上 ORDER BY，或者对 NULL 的处理不够严谨。

《夸克》非常好用的免费AI浏览器

下载APP查看