窗口函数（日期范围）：重复状态下的状态时长计算问题

我现在需要计算不同状态的持续时长，多数场景下现有逻辑可行，但遇到状态来回切换的情况就会出错。

数据表情况 #

我的数据表大致如下：

原SQL及问题 #

针对id=102这类状态不重复切换的场景，我用下面的SQL能正确计算时长：

with ab as (
          select id,
                 status,
                 max(updated_time) as end_time,
                 min(updated_time) as updated_time
          from "Table"
          group by id, status
)

select *,
        lead(updated_time) over (partition by id order by updated_time) - updated_time as duration,
        extract(epoch from duration) as duration_seconds
from ab

但遇到id=101这种状态反复切换的情况，原SQL会把两次IN_PROGRESS合并成一条，导致时长计算错误。我需要的正确结果是：

修正后的SQL #

问题出在原SQL的分组逻辑——直接按id和status分组会忽略状态切换的顺序，把同一id下所有相同状态的记录合并。需要先给连续的相同状态标记分组（也就是“状态孤岛”），再计算时长：

-- 第一步：标记连续相同状态的分组
with status_groups as (
    select 
        id,
        status,
        updated_time,
        -- 当当前状态与上一条不同时，分组编号+1
        sum(case when status = lag(status) over (partition by id order by updated_time) then 0 else 1 end) 
            over (partition by id order by updated_time) as group_id
    from "Table"
),
-- 第二步：按分组获取每个状态段的起止时间
status_windows as (
    select 
        id,
        status,
        min(updated_time) as updated_time,
        max(updated_time) as end_time
    from status_groups
    group by id, status, group_id
)
-- 第三步：计算每个状态段的持续时长
select 
    *,
    lead(updated_time) over (partition by id order by updated_time) - updated_time as duration,
    extract(epoch from (lead(updated_time) over (partition by id order by updated_time) - updated_time)) as duration_seconds
from status_windows
order by id, updated_time;

逻辑说明 #

1. status_groups 用lag()函数对比当前行与上一行的状态，给连续相同状态分配同一个group_id，这样两次IN_PROGRESS会被分成不同分组；
2. status_windows 按id、status和group_id分组，得到每个连续状态段的开始和结束时间；
3. 最后用lead()函数获取下一个状态段的开始时间，计算当前状态的持续时长，得到正确的分段结果。

内容的提问来源于stack exchange，提问作者Nitya