目前我有一个像这样建立的表
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
那么任何想法如何以快速的方式做到这一点？我能想到的就是使用某种光标并手动比较每个日期时间对.但我认为这将变得非常慢,因为我们必须检查每一行中的每个值.
更新
目前我测试了所有给出的答案.通过阅读,他们看起来都很好,并有一些有趣的方法.不幸的是,所有这些(到目前为止)在真实数据上都失败了.最大的问题似乎是数据的质量(目前它们在表中大约有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
