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

Question

目前我有这样的桌子

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万个条目。这是给定的列类型和索引：

• _ID

  • int
  • Primary Key
  • Grouped Index
    didn’t mentioned this column in my example, cause it isn’t needed for this query
    

• DeviceID

  • int
  • not null
  • Index

• Timestamp

  • datetime
  • not null
  • Index

• Value

  • several not indexed columns of different types (int, real, tinyint)
  • all can be null

也许这有助于改善您针对给定问题的（或新的）解决方案。

Answer 1

– 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