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

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

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

select device_id, speed, count(*) as num_timesfrom (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     ) tgroup by device_id, speed, (seqnum - seqnum_s);

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

select device_id, speed, num_timesfrom (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)     ) dswhere seqnum = 1;