查询累计一段时间内的状态持续时间，不会重叠

Question

我正在尝试创建一个在给定时间段内的条形图，该条形图使用SQL Server查询的结果显示给定状态的累积时间。这些状态的时间不能重叠(一个实体不能同时是两个状态)。时间段不会超过8-10小时。我附上了一张样本数据的图像。在其中，您可以看到“123”实体在2分钟内从状态3变为7。下面的查询接近于我所需要的，但是，它没有考虑与不同状态相关的时间，而只是简单地查看每个状态的“开始”时间和“结束”时间。谁能给我指个方向？谢谢!

SELECT state.status,
       min(state.timestamp) start_time,
       max(state.timestamp) end_time,
       datediff(second, min(state.timestamp),max(state.timestamp)) elapsed_sec,
       convert(varchar, max(state.timestamp) - min(state.timestamp), 108) accumulated_time
FROM state
WHERE state.entity = '123' AND CAST(timestamp AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY state.status;

​

​

Answer 1

with data as (
    select
        timestamp, entity, status,
        row_number() over (partition by entity order by timestamp, id) - 
            row_number() over (partition by entity, status order by timestamp, id) as grp
    from state
)
select entity, status, min(timestamp) as startTime, max(timestamp) as endTime
from data
group by entity, status, grp;