mysql - 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;