SQL-获取设备连续的正常运行时间

Question

设备正常运行时间时间序列表

有一个设备监视器表记录设备每天是启动(状态1(还是关闭。

DEVICE_ID, STATE, DATE
1          0      2017-10-09
1          1      2017-10-10
1          1      2017-10-11
1          1      2017-10-12
1          0      2017-10-13
1          1      2017-10-14
1          1      2017-10-15
1          0      2017-10-16
1          1      2017-10-17
1          0      2017-10-18
...
2          0      2017-10-10
...

问题

如何计算每个设备启动的日期持续时间？设备1在2017-10-10上升，在2017-10-13下降，因此上升了3天(10、11、12(。然后是从2017-10-14到2017-10-15的2天。

预期结果如下所示。

DEVICE_ID, STATE, DATE
1          3      2017-10-10
1          2      2017-10-14
1          1      2017-10-17

请告知。

Answer 1

这是一个缺口和孤岛问题。你可以用行号的差异来解决这个版本：

select device_id, min(date), max(date), count(*) as num_days
from (select t.*,
row_number() over (partition by device_id order by date) as seqnum,
row_number() over (partition by device_id, state order by date) as seqnum_2
from t
) t
where state = 1
group by device_id, (seqnum - seqnum_2), state;

为什么这样做有点难解释。如果您盯着子查询的结果看，您将看到两个行号值之间的差异是如何定义所需的相邻值的。