计算数据集的持续时间

Snooks 发表于 Dev

斯诺克

我在SQL中有一组数据，如下所示：

╔═══════════╦═══════╗
║ TimeStamp ║ State ║
╠═══════════╬═══════╣
║  7:10 AM  ║   A   ║
║  7:11 AM  ║   A   ║
║  7:12 AM  ║   A   ║
║  7:13 AM  ║   B   ║
║  7:14 AM  ║   B   ║
║  7:15 AM  ║   A   ║
║  7:16 AM  ║   A   ║
║  7:17 AM  ║   C   ║
║  7:18 AM  ║   C   ║
╚═══════════╩═══════╝

我正在尝试计算每个状态的持续时间。但是，我想分离每个状态序列，并分别计算它们之间的差异，并分离重复的状态。因此，我希望以上数据返回类似以下内容的内容：

╔═══════╦════════════════════╗
║ State ║ Duration (minutes) ║
╠═══════╬════════════════════╣
║   A   ║         2          ║
║   B   ║         1          ║
║   A   ║         1          ║
║   C   ║         1          ║
╚═══════╩════════════════════╝

有人可以帮忙吗？如何编写返回此数据的SQL查询？

谢谢！

Used_By_Already

好吧，我认为需要的是MS SQL Server。

为了达到预期的结果（其中C的持续时间为1）：

select    
    state, MIN(TimeStamp) StartsAt, EndsAt, datediff(minute,MIN(TimeStamp),EndsAt) DurationMinutes
from (
        select
                t1.state, t1.TimeStamp
                , ISNULL(ca.EndsAt, (select max(timestamp) from table1) ) EndsAt
        from table1 t1
        outer apply (
                  select top (1) t2.timestamp as EndsAt
                  from table1 t2
                  where t1.state <> t2.state and t1.TimeStamp < t2.TimeStamp
                  order by t2.TimeStamp
              ) ca
    ) as derived
group by     
    state, EndsAt

对于数据样本，人们可能会说C的持续时间未知，因为状态尚未改变。在这种情况下，它要简单一些：

select    
    state, MIN(TimeStamp) StartsAt, EndsAt, datediff(minute,MIN(TimeStamp),EndsAt) DurationMinutes
from (
        select
                t1.state, t1.TimeStamp ,ca.EndsAt
        from table1 t1
        outer apply (
                  select top (1) t2.timestamp as EndsAt
                  from table1 t2
                  where t1.state <> t2.state and t1.TimeStamp < t2.TimeStamp
                  order by t2.TimeStamp
              ) ca
    ) as derived
group by     
    state, EndsAt

http://sqlfiddle.com/#!6/f0dd7e/9

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。