SQL:对每个设备集连续出现相同值的所有记录进行计数，并返回最高计数 [英] SQL: count all records with consecutive occurrence of same value for each device set and return the highest count

问题描述

我想找出特定值在特定分区上连续出现了多少次，然后显示该分区的较高计数.

I want to find out how many times a particular value occured consecutively for a particular partition and then display the higher count for that partition.

例如，下表如下:

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

---

我想为每台设备连续一次出现相同速度的最高次数.

---

I want to know for each device what is highest number of times the same speed appeared consecutively.

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

So if i partition them by device id, i would get the belo table

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.''

note that 07777778128 did not appear as there were no values which repeated consecutively```

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

What would be the possible way to achieve this. i was able to get the the count of all consecutive values for each device but then it doesn't give the highest rather gives count of all such consecutive groups

推荐答案

这是一种空白和孤岛的形式.您可以使用不同的行号来获取孤岛:

This is a form of gaps-and-islands. You can use a difference of row numbers to get the islands:

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

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

Then, to get the max, use another layer of window functions:

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;

这篇关于SQL:对每个设备集连续出现相同值的所有记录进行计数，并返回最高计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！