从时间戳列中查看具有重复启动/停止记录的启动/停止事件的会话

Question

我有以下案例更改记录：

021-04-12 04:39:36.42467000

id
1		2		3		4		5		6	100	审查
7		8		9		10		11		12

这相当复杂。首先基于"0"的计数添加分组；等待"；以及"；"关闭"——但只有当它们改变值时：

select t.*,
sum(case when (state <> next_state or next_state is null) and
state in ('WAITING', 'CLOSED')
then 1 else 0
end) over (partition by caseid order by time_created desc) as grouping
from (select t.*,
lead(state) over (partition by caseid order by time_created) as next_state
from t
) t

然后，您可以聚合：

with cte as (
select t.*,
sum(case when (state <> next_state or next_state is null) and
state in ('WAITING', 'CLOSED')
then 1 else 0
end) over (partition by caseid order by time_created desc) as grouping
from (select t.*,
lead(state) over (partition by caseid order by time_created) as next_state
from t
) t
)
select caseid, min(id), max(id),
min(case when status = 'REVIEW_NEEDED' then time_created end),
min(case when status = 'REVIEW' then time_created end),
max(time_created)
from cte
group by grouping, caseid;