时间戳

首页 > 解决方案 > SQL - 获取设备连续正常运行时间

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

问题描述

设备正常运行时间序列表

如果设备每天启动（状态 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

请指教。

标签： sqlgaps-and-islands

解决方案

这是一个差距和孤岛问题。您可以通过行号的不同来解决此版本：

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;

为什么这行得通有点难以解释。如果您盯着子查询的结果，您将看到两个行号值之间的差异如何定义您想要的相邻值。

推荐阅读