sql – 从日期时间表中获取包含起始值和结束值的列表

DeviceID      Timestamp            Value
----------------------------------------
Device1       1.1.2011 10:00:00    3
Device1       1.1.2011 10:00:01    4
Device1       1.1.2011 10:00:02    4
Device1       1.1.2011 10:00:04    3
Device1       1.1.2011 10:00:05    4
Device1       1.1.2011 14:23:14    8
Device1       1.1.2011 14:23:15    7
Device1       1.1.2011 14:23:17    4
Device1       1.1.2011 14:23:18    2

如您所见,来自具有给定时间戳的设备的某些值(列类型为datetime).

问题是设备可以在任何点启动和停止,并且数据中没有发生启动或停止的直接信息.但是从给定的时间戳列表中可以很容易地判断何时发生了启动和停止,因为每当两行的时间戳在五秒内时,它们属于相同的测量值.

现在我想从这个数据中得到这样一个列表：

DeviceID      Started              Ended
Device1       1.1.2011 10:00:00    1.1.2011 10:00:05
Device1       1.1.2011 14:23:14    1.1.2011 14:23:18

那么任何想法如何以快速的方式做到这一点？我能想到的就是使用某种光标并手动比较每个日期时间对.但我认为这将变得非常慢,因为我们必须检查每一行中的每个值.

那么有没有更好的SQL解决方案不适用于游标？

更新

目前我测试了所有给出的答案.通过阅读,他们看起来都很好,并有一些有趣的方法.不幸的是,所有这些(到目前为止)在真实数据上都失败了.最大的问题似乎是数据的质量(目前它们在表中大约有350万个条目).仅在一小部分上执行给定查询会产生预期结果,但将查询滚动到整个表上只会导致非常糟糕的性能.

我必须进一步测试并检查我是否可以对数据进行分块并仅将部分数据传递给其中一个给定的算法以使这个事情滚动.但也许你们其中一个人有另一个明智的想法,可以让结果更快一些.

更新(有关结构的更多信息)

好的,这些信息也可能有所帮助：
目前,该表中有大约350万条参赛作品.这里是给定的列类型和indizes：

> _ID

> int
>主键
>分组索引
>在我的示例中没有提到此列,因为此查询不需要它

> DeviceID

> int
>不是空的
>索引

>时间戳

> datetime
>不是空的
>索引

>价值

>几个不带索引的不同类型的列(int,real,tinyint)
> all都可以为null

也许这有助于改善您已经(或新)的解决方案以解决给定问题.

解决方法

-- Table var to store the gaps
declare @T table
(
  DeviceID varchar(10),PrevPeriodEnd datetime,NextPeriodStart datetime
)

-- Get the gaps
;with cte as 
(
  select *,row_number() over(partition by DeviceID order by Timestamp) as rn
  from data
)
insert into @T
select
  C1.DeviceID,C1.Timestamp as PrevPeriodEnd,C2.Timestamp as NextPeriodStart
from cte as C1
  inner join cte as C2
    on C1.rn = C2.rn-1 and
       C1.DeviceID = C2.DeviceID and
       datediff(s,C1.Timestamp,C2.Timestamp) > 5

-- Build islands from gaps in @T
;with cte1 as
(
  -- Add first and last timestamp to gaps
  select DeviceID,PrevPeriodEnd,NextPeriodStart
  from @T
  union all
  select DeviceID,max(TimeStamp) as PrevPeriodEnd,null as NextPeriodStart
  from data
  group by DeviceID
  union all
  select DeviceID,null as PrevPeriodEnd,min(TimeStamp) as PrevPeriodEnd
  from data
  group by DeviceID
),cte2 as
(
  select *,row_number() over(partition by DeviceID order by PrevPeriodEnd) as rn
  from cte1
)
select
  C1.DeviceID,C1.NextPeriodStart as PeriodStart,C2.PrevPeriodEnd as PeriodEnd
from cte2 as C1
  inner join cte2 as C2
    on C1.DeviceID = C2.DeviceID and
       C1.rn = C2.rn-1
order by C1.DeviceID,C1.NextPeriodStart

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!