SQL Query to get count of cycles if it matches value of column in consecutive rows

Question

I have table named vehicledata which consists 3 columns: id, Veh No, and Veh Mode.

My data looks like this:

id  Veh No  Veh Mode
1   KA03-003    IDLE
2   KA03-003    IDLE
3   KA03-003    IDLE
4   KA03-003    DRIVE
5   KA03-003    DRIVE
6   KA03-003    DRIVE
7   KA03-003    DRIVE
8   KA03-003    DRIVE
9   KA03-003    IDLE
10  KA03-003    IDLE
11  KA03-003    IDLE
12  KA03-003    DRIVE
13  KA03-003    DRIVE
14  KA03-003    DRIVE
15  KA03-003    DRIVE
16  KA05-005    IDLE
17  KA05-005    IDLE
18  KA05-005    IDLE
19  KA05-005    DRIVE
20  KA05-005    DRIVE
21  KA05-005    DRIVE
22  KA05-005    DRIVE
23  KA05-005    DRIVE
24  KA05-005    IDLE
25  KA05-005    IDLE
26  KA05-005    IDLE
27  KA05-005    DRIVE
28  KA05-005    DRIVE
29  KA05-005    DRIVE
30  KA05-005    DRIVE

In the above table there are 2 Veh No i.e KA03-003 & KA05-005. From id 4 to 8 & 12 to 15 there are two DRIVE cycles for KAO3-003 and From id 19 to 23 & 27 to 30 there are two DRIVE cycles for KAO5-005 For these Vehicles i need to get count of 'DRIVE' cycles occured in a table & result should look as below:

Veh No    No.of.Drive Cycles
KA03-003       2 
KA05-005       2

I am not able to write query.Kindly help me in solving this. Thanks in advance.

I removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff, May 21 '20 at 10:32

Gordon Linoff · Answer 1 · 2020-05-21T10:46:22.240

1

You can just count the number of rows that have 'DRIVE' where the previous row has a different mode or NULL. For that, uselag()`:

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;

Here is a db<>fiddle (which happens to use MySQL but the code should work in any database).

edited May 21 '20 at 10:46

answered May 21 '20 at 10:32

Gordon Linoff

1,242,037
58
646
786

I tried this and i got the solution.Thank you Gordan. – Pradeep Gn May 21 '20 at 11:11
@PradeepGn . . . The polite thing to do on the site is to accept the (one) answer that you consider to be the best. – Gordon Linoff May 21 '20 at 13:52

Tim Biegeleisen · Answer 2 · 2020-05-21T11:05:54.023

1

One approach might be to approach this as a gaps and islands problem, and use the difference in row numbers method:

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;

Demo

This assumes that every 2 islands corresponds to one driving cycle. A vehicle having IDLE/DRIVE/IDLE would therefore be counted as having only 1 cycle, since 3 / 2 truncates to 1 in integer division.

edited May 21 '20 at 11:05

answered May 21 '20 at 10:35

Tim Biegeleisen

502,043
27
286
360

Hi your query looks promising and i am implementing to get result. – Pradeep Gn May 21 '20 at 13:38

score 1 · Answer 3 · answered May 21 '20 at 11:30

@Pradeep - The logic to build this query will be to check only rows where vehicle is currently in drive mode and he was IDLE before that ROW. You don't have to worry about IDLE modes and also the continuous DRIVE modes. You only need to check the rows where a vehicle has transitioned from IDLE to DRIVE. I have used a case statement and a LAG windows function to mark only these records as 1 and everything else as 0. And then I just took a sum of this column for each vehicle.

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

@PradeepGn - Glad it helped. Please consider marking it as an answer( if it helped) and voting up. Thanks. — VTi, May 21 '20 at 13:53

SQL Query to get count of cycles if it matches value of column in consecutive rows

3 Answers3

Demo

Linked

Related