SQL：对每个设备集连续出现相同值的所有记录进行计数，并返回最高计数

Question

我想找出某个特定分区连续出现某个特定值的次数，然后显示该分区的较高计数。

例如，如果下表是：

Device ID        speed             DateTime
--------------------------------------------------
07777778999       34               18-12-2016 17:15
07777778123       15               18-12-2016 18:10
07777778999       34               19-12-2016 19:30
07777778999       34               19-12-2016 12:15
07777778999       20               19-12-2016 13:15
07777778999       20               20-12-2016 11:15
07777778123       15               20-12-2016 9:15
07777778128       44               20-12-2016 17:15
07777778123       15               20-12-2016 17:25
07777778123       12               20-12-2016 17:35
07777778999       34                20-12-2016 17:45
07777778999       34               20-12-2016 17:55
07777778999       34               20-12-2016 18:50
07777778999       34               20-12-2016 18:55

我想知道每个设备连续出现相同速度的最高次数。

因此，如果我按设备ID对它们进行分区，我将获得belo表

Device ID        speed             DateTime
--------------------------------------------------
07777778999       34               18-12-2016 17:15
07777778999       34               19-12-2016 19:30
07777778999       34               19-12-2016 12:15
07777778999       20               19-12-2016 13:15
07777778999       20               20-12-2016 11:15
07777778999       34                20-12-2016 17:45
07777778999       34               20-12-2016 17:55
07777778999       34               20-12-2016 18:50
07777778999       34               20-12-2016 18:55
07777778123       15               18-12-2016 18:10
07777778123       15               20-12-2016 9:15
07777778123       15               20-12-2016 17:25
07777778123       12               20-12-2016 17:35
07777778128       44               20-12-2016 17:15
-----------------------------------------------------------------

所以我需要的输出是

Device ID        speed             highcount
--------------------------------------------------
07777778999       34               4
07777778123       15               3

请注意，由于没有连续重复的值，因此未显示07777778128

实现这一目标的可能方法是什么。我能够获得每个设备的所有连续值的计数，但是然后它没有给出最高的计数，而是给出了所有这些连续组的计数

Answer 1

这是一种差距与孤岛的形式。您可以使用不同的行号来获得孤岛：

select device_id, speed, count(*) as num_times
from (select t.*,
             row_number() over (partition by device_id order by datetime) as seqnum,
             row_number() over (partition by device_id, speed order by datetime) as seqnum_s
      from t
     ) t
group by device_id, speed, (seqnum - seqnum_s);

然后，要获得最大值，请使用另一层窗口函数：

select device_id, speed, num_times
from (select device_id, speed, count(*) as num_times,
             row_number() over (partition by device_id order by count(*) desc) as seqnum
      from (select t.*,
                   row_number() over (partition by device_id order by datetime) as seqnum,
                   row_number() over (partition by device_id, speed order by datetime) as seqnum_s
            from t
           ) t
      group by device_id, speed, (seqnum - seqnum_s)
     ) ds
where seqnum = 1;