你只需要数一数有多少行 'DRIVE' 其中前一行具有不同的模式或 NULL. For that, use 滞后（）`：

select veh_no, count(*)
from (select t.*,
             lag(veh_mode) over (partition by veh_no order by id) as prev_veh_mode
      from t
     ) t
where veh_mode = 'DRIVE' and
      (prev_veh_mode <> 'DRIVE' or prev_veh_mode is null)
group by veh_no;

这里有一个db<>fiddle（它碰巧使用mysql，但是代码应该可以在任何数据库中工作）。

一种方法可能是将其视为间隙和孤岛问题，并使用行号差异法：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY VehNo ORDER BY id) rn1,
        ROW_NUMBER() OVER (PARTITION BY VehNo, VehMode ORDER BY id) rn2
    FROM yourTable
)

SELECT VehNo, COUNT(DISTINCT rn1 - rn2) / 2 AS NumCycles
FROM cte
GROUP BY VehNo;

演示

这假设每2个岛对应一个行驶循环。因此，具有怠速/行驶/怠速的车辆将被视为只有1个循环，因为3/2在整数除法中截断为1。

@pradeep—构建此查询的逻辑是只检查车辆当前处于驾驶模式且在该行之前处于空闲状态的行。您不必担心空闲模式和连续驾驶模式。您只需检查车辆从怠速过渡到行驶的行。我使用了case语句和lag windows函数，只将这些记录标记为1，其他所有记录标记为0。然后我把每辆车的这一栏加起来。

------------------------
WITH vehicles(id , Veh_No,  Veh_Mode) AS
( 
SELECT 1 ,  'KA03-003',   'IDLE'  UNION
SELECT 2 ,  'KA03-003',   'IDLE'  UNION
SELECT 3 ,  'KA03-003',   'IDLE'  UNION
SELECT 4 ,  'KA03-003',   'DRIVE' UNION
SELECT 5 ,  'KA03-003',   'DRIVE' UNION
SELECT 6 ,  'KA03-003',   'DRIVE' UNION
SELECT 7 ,  'KA03-003',   'DRIVE' UNION
SELECT 8 ,  'KA03-003',   'DRIVE' UNION
SELECT 9 ,  'KA03-003',   'IDLE'  UNION
SELECT 10,  'KA03-003',   'IDLE'  UNION
SELECT 11,  'KA03-003',   'IDLE'  UNION
SELECT 12,  'KA03-003',   'DRIVE' UNION
SELECT 13,  'KA03-003',   'DRIVE' UNION
SELECT 14,  'KA03-003',   'DRIVE' UNION
SELECT 15,  'KA03-003',   'DRIVE' UNION
SELECT 16,  'KA05-005',   'IDLE'  UNION
SELECT 17,  'KA05-005',   'IDLE'  UNION
SELECT 18,  'KA05-005',   'IDLE'  UNION
SELECT 19,  'KA05-005',   'DRIVE' UNION
SELECT 20,  'KA05-005',   'DRIVE' UNION
SELECT 21,  'KA05-005',   'DRIVE' UNION
SELECT 22,  'KA05-005',   'DRIVE' UNION
SELECT 23,  'KA05-005',   'DRIVE' UNION
SELECT 24,  'KA05-005',   'IDLE'  UNION
SELECT 25,  'KA05-005',   'IDLE'  UNION
SELECT 26,  'KA05-005',   'IDLE'  UNION
SELECT 27,  'KA05-005',   'DRIVE' UNION
SELECT 28,  'KA05-005',   'DRIVE' UNION
SELECT 29,  'KA05-005',   'DRIVE' UNION
SELECT 30,  'KA05-005',   'DRIVE'
)

SELECT veh_no, SUM(count_drive_cycle) AS drive_cycles
FROM (SELECT *,
      CASE WHEN Veh_Mode = 'DRIVE'
            AND LAG(veh_mode,1,'DRIVE') OVER (PARTITION BY Veh_No ORDER BY id) = 'IDLE'   -- check if prev mode is IDLE
           THEN 1
           ELSE 0 END AS count_drive_cycle
      FROM vehicles
     ) A
GROUP BY veh_no