这是一种差距与孤岛的形式。您可以使用不同的行号来获得孤岛：
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;
