用多个设备确定表中设备的某些状态的持续时间

Question

我有一个包含deviceId、设备状态(旁路)和时间戳(Regtime)的表。

+----------+--------+-----------------+
| DeviceId | Bypass |     RegTime     |
+----------+--------+-----------------+
|       14 |      0 | 30-5-2018 02:14 |
|       12 |      0 | 30-5-2018 02:14 |
|       14 |      0 | 30-5-2018 02:15 |
|       14 |      0 | 30-5-2018 02:15 |
|       12 |      0 | 30-5-2018 02:15 |
|       12 |      0 | 30-5-2018 02:15 |
|       14 |      0 | 30-5-2018 02:16 |
|       12 |      0 | 30-5-2018 02:16 |
|       14 |      1 | 30-5-2018 02:17 |
|       12 |      0 | 30-5-2018 02:17 |
|       14 |      1 | 30-5-2018 02:18 |
|       12 |      0 | 30-5-2018 02:18 |
|       14 |      1 | 30-5-2018 02:19 |
|       12 |      0 | 30-5-2018 02:19 |
|       14 |      0 | 30-5-2018 02:20 |
|       12 |      0 | 30-5-2018 02:20 |
|       14 |      0 | 30-5-2018 02:21 |
|       12 |      0 | 30-5-2018 02:21 |
|       14 |      0 | 30-5-2018 02:22 |
|       12 |      1 | 30-5-2018 02:22 |
|       14 |      0 | 30-5-2018 02:23 |
|       12 |      1 | 30-5-2018 02:23 |
|       14 |      0 | 30-5-2018 02:24 |
|       12 |      0 | 30-5-2018 02:24 |
+----------+--------+-----------------+

现在，我需要确定设备处于旁路状态的时间(Bypass = 0)。

我已经尝试过一些使用窗口化、First_Value和Last_Value的方法，但是这里的问题似乎是我只能用DeviceId进行分区。

所以当一个设备在旁路上，然后从它出来时，first_value仍然是所有设备的first_value

Answer 1

如果需要确定设备内旁路1的持续时间，可以这样做：

样本数据

create table #temp
(deviceid int,bypass int, regtime datetime)

insert into #temp 
values
(14 , 0 ,'2018-05-30 02:14'),
(12 , 0 ,'2018-05-30 02:14'),
(14 , 0 ,'2018-05-30 02:15'),
(14 , 0 ,'2018-05-30 02:15'),
(12 , 0 ,'2018-05-30 02:15'),
(12 , 0 ,'2018-05-30 02:15'),
(14 , 0 ,'2018-05-30 02:16'),
(12 , 0 ,'2018-05-30 02:16'),
(14 , 1 ,'2018-05-30 02:17'),
(12 , 0 ,'2018-05-30 02:17'),
(14 , 1 ,'2018-05-30 02:18'),
(12 , 0 ,'2018-05-30 02:18'),
(14 , 1 ,'2018-05-30 02:19'),
(12 , 0 ,'2018-05-30 02:19'),
(14 , 0 ,'2018-05-30 02:20'),
(12 , 0 ,'2018-05-30 02:20'),
(14 , 0 ,'2018-05-30 02:21'),
(12 , 0 ,'2018-05-30 02:21'),
(14 , 0 ,'2018-05-30 02:22'),
(12 , 1 ,'2018-05-30 02:22'),
(14 , 0 ,'2018-05-30 02:23'),
(12 , 1 ,'2018-05-30 02:23'),
(14 , 0 ,'2018-05-30 02:24'),
(12 , 0 ,'2018-05-30 02:24')

SQL脚本

select x.deviceid,x.bypass,regtime,case when x.bypass = rn then DATEDIFF(SS,regtime,maxtime) else 0 end as DurationInSeconds from (
select *,ROW_NUMBER()over(partition by a.deviceid,a.bypass order by regtime) as rn
from #temp a
) x

cross apply (select MAX(regtime) as maxtime,deviceid,bypass from #temp b where  B.bypass = 1 and x.deviceid = B.deviceid   group by deviceid,bypass ) c

如果您只想要设备信息上的信息：

SQL2

select x.deviceid,x.bypass,case when x.bypass = rn then DATEDIFF(SS,regtime,maxtime) else 0 end as DurationInSeconds from (
select *,ROW_NUMBER()over(partition by a.deviceid,a.bypass order by regtime) as rn
from #temp a
) x

cross apply (select MAX(regtime) as maxtime,deviceid,bypass from #temp b where  B.bypass = 1 and x.deviceid = B.deviceid and rn= 1 and x.bypass = 1   group by deviceid,bypass ) c

Answer 2

产出应如下：

DeviceId，BypassStart，BypassEnd，BypassDuration，BypassDuration

|----------|-----------------|-----------------|----------------|

= 12 .=‘2’>2018年.

=.= 14 .

但是当另一次出现旁路=1和DeviceId = 14出现在一些占卜记录时，在Bypass=1发生之间，我应该为这个欺骗得到一个额外的行，包括这些行的总时间。