在sql中根据业务状态变更多次实现状态聚合,核心做法是利用窗口函数 `lag()` 或 `lead()` 获取前一条或后一条记录的状态,结合条件判断识别状态变化的边界,再通过累积求和生成唯一的“状态组”标识,最后基于该标识进行分组聚合。具体而言,先按时间排序,计算当前状态与前一条状态的差异,当状态发生改变时标记为1,否则为0,对该标记列执行累积求和得到组id,即可将连续相同状态划分为独立区间并进行统计。

如何识别连续相同状态的起始与结束点
识别连续相同状态的起始与结束点,关键在于检测状态发生变化的时刻。通过比较当前行与上一行的状态值,若两者不同则说明发生了状态变更,此时可以标记为一个新组的开始。利用 `LAG()` 函数获取前一行的状态字段,并与当前行状态进行比对,产生一个布尔型或整型的差异标志。这个标志是后续划分状态区间的基础,能够准确捕捉每一次状态跳变的节点,从而为聚合提供精确的时间边界。

WITH status_changes AS (
SELECT
id,
user_id,
status,
changed_at,
LAG(status) OVER (PARTITION BY user_id ORDER BY changed_at) as prev_status
FROM business_logs
),
diff_flags AS (
SELECT
id,
user_id,
status,
changed_at,
CASE
WHEN prev_status IS NULL THEN 1
WHEN status != prev_status THEN 1
ELSE 0
END as is_new_group
FROM status_changes
)
SELECT * FROM diff_flags;
上述代码首先通过 `business_logs` 表获取日志数据,使用 `LAG()` 函数按用户分区并按时间排序获取前一状态。接着在 `diff_flags` 子查询中,通过 `CASE` 语句判断当前状态是否与前一条不同,若不同或为第一条记录则标记为1,表示新状态组的开始。最终输出包含差异标志的结果集,可用于进一步计算组ID。
如何将状态变化转化为可聚合的组ID
将状态变化转化为可聚合的组ID,需要对上一步生成的差异标志进行累积求和。由于每次状态变更时标志为1,其余为0,对这一列执行非窗口式的累积求和(即普通 SUM 配合 ROWS UNBOUNDED PRECEDING),即可为每个连续相同状态段分配一个唯一的整数 ID。该 ID 在同一状态持续期间保持不变,一旦状态改变便递增,从而自然形成分组键。随后只需按用户 ID 和该组 ID 进行 GROUP BY,即可统计每个状态区间的持续时间、起止时间等聚合指标。

WITH status_changes AS (
SELECT
id,
user_id,
status,
changed_at,
LAG(status) OVER (PARTITION BY user_id ORDER BY changed_at) as prev_status
FROM business_logs
),
diff_flags AS (
SELECT
id,
user_id,
status,
changed_at,
CASE
WHEN prev_status IS NULL THEN 1
WHEN status != prev_status THEN 1
ELSE 0
END as is_new_group
FROM status_changes
),
group_ids AS (
SELECT
id,
user_id,
status,
changed_at,
SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY changed_at ROWS UNBOUNDED PRECEDING) as group_id
FROM diff_flags
)
SELECT
user_id,
status,
group_id,
MIN(changed_at) as start_time,
MAX(changed_at) as end_time,
COUNT(*) as duration_steps
FROM group_ids
GROUP BY user_id, status, group_id
ORDER BY user_id, start_time;
该完整程序首先复用前两节的逻辑生成差异标志,然后在 `group_ids` 子查询中使用 `SUM() OVER (...)` 对 `is_new_group` 进行累积求和,生成 `group_id`。最后在主查询中按 `user_id`、`status` 和 `group_id` 分组,计算每个状态区间的开始时间、结束时间和步骤数。此方法适用于任意多次状态变更的场景,能准确聚合出每个状态的连续区间。
当状态快速回退时聚合结果如何体现
当状态快速回退时,例如从 A 变为 B 又立即变回 A,聚合结果会将其视为两个独立的状态区间。因为每次状态值发生变化都会触发新的组 ID 递增,所以即使状态值重复出现,只要中间经历过其他状态,就会被拆分为不同的组。这意味着在最终聚合中,同一个状态可能出现多次,每次对应不同的 `group_id` 和时间区间。这种处理方式符合业务逻辑中对“连续状态”的定义,确保每次状态切换都被单独统计,避免混淆不同时间段内的相同状态表现。










