Grouping the records on a specific criteria and to find the maximum value

Question

I have a veh_speed table with the fields vid, date_time, speed, status. My objective is to get the duration(start_date_time and end_date_time) of the vehicle with speed greater than 30. Currently I am generating the report using PL/SQL. Is it possilble to do with an SQL. Also it would be great if it is possible to get the max_speed between the range.

My table is as follows:

VID  START_DATE_TIME        SPEED  STATUS
---  -------------------    -----  ------
1   15/01/2014 10:00:05     0      N
1   15/01/2014 10:00:10    10      Y 
1   15/01/2014 10:00:15    30      Y
1   15/01/2014 10:00:20    35      Y
1   15/01/2014 10:00:25    45      Y
1   15/01/2014 10:00:27    10      Y
1   15/01/2014 10:00:29     0      Y
1   15/01/2014 10:00:30    20      Y
1   15/01/2014 10:00:35    32      Y
1   15/01/2014 10:00:40    33      Y
1   15/01/2014 10:00:45    35      Y
1   15/01/2014 10:00:50    38      Y
1   15/01/2014 10:00:55    10      Y

And I would like to get the following output:

VID   START_DATE_TIME          END_DATE_TIME          MAX_SPEED
---   ---------------          -------------          ---------
1    15/01/2014 10:00:15     15/01/2014 10:00:25      45
1    15/01/2014 10:00:35     15/01/2014 10:00:50      38

Here is the table creation script:

CREATE TABLE veh_speed(vid NUMBER(3), 
             date_time DATE, 
             speed NUMBER(3), 
             status CHAR(1));

INSERT ALL
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:05', 'dd/mm/yyyy hh24:mi:ss'),  0,  'N')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:10', 'dd/mm/yyyy hh24:mi:ss'), 10, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:15', 'dd/mm/yyyy hh24:mi:ss'), 30, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:20', 'dd/mm/yyyy hh24:mi:ss'), 35, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:25', 'dd/mm/yyyy hh24:mi:ss'), 45, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:27', 'dd/mm/yyyy hh24:mi:ss'), 10, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:29', 'dd/mm/yyyy hh24:mi:ss'),  0, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:30', 'dd/mm/yyyy hh24:mi:ss'), 20, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:35', 'dd/mm/yyyy hh24:mi:ss'), 32, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:40', 'dd/mm/yyyy hh24:mi:ss'), 33, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:45', 'dd/mm/yyyy hh24:mi:ss'), 35, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:50', 'dd/mm/yyyy hh24:mi:ss'), 38, 'Y')
     INTO veh_speed VALUES(1, to_date('15/01/2014 10:00:55', 'dd/mm/yyyy hh24:mi:ss'), 10, 'Y')
SELECT * FROM dual;

I hope I made my question clear.

Thanks in advance.

Basting

What is you criteria here for max speed? I can see that VID is same for all the rows. How would you define a group from which you will extract max value? – Obligato

I think 0 speed acts as a group delimiter – Nascent

@San, max_speed is the maximum speed between the start_time and end_time. From the sample data the maximum speed b/w the time 15/01/2014 10:00:15 and 5/01/2014 10:00:25 is 45. – Basting

@Dba, what is your input to this query? – Nascent

@beck03076, No. Speed 0 means the vehicle is in idle state(not moving). Here my search criteria is 30. – Basting

You dont understand what San is asking and what Im asking. What is the input to your query and if you want to find the max_speed, in which group of rows you want to find – Nascent

@beck03076, as i have said, My input criteria is just the speed only, which is 30. Max_speed is just an additional information. b/w that time range. – Basting

Oh i see! You want to get the maximum speed before someone is slowing and it is greater then 30? Like 10,20,30,31,18,43,10,53. output: 31,43,53? – Deneendenegation

@PeterRing, My main requirement is to get the start_date_time of vehicle when speed greater than 30 and the end_time once the speed is below 30. – Basting

Answer 1

You can use analytic functions to group your records into blocks where the speed is 30 or more:

select vid, date_time, speed, status,
  case when speed >= 30 then 30 else 0 end as speed_limit,
  row_number() over (partition by vid order by date_time)
    - row_number() over (
      partition by vid, case when speed >= 30 then 30 else 0 end
      order by date_time) as chain
from veh_speed;

      VID DATE_TIME                SPEED STATUS SPEED_LIMIT      CHAIN
---------- ------------------- ---------- ------ ----------- ----------
         1 15/01/2014 10:00:05          0 N                0          0 
         1 15/01/2014 10:00:10         10 Y                0          0 
         1 15/01/2014 10:00:15         30 Y               30          2 
         1 15/01/2014 10:00:20         35 Y               30          2 
         1 15/01/2014 10:00:25         45 Y               30          2 
         1 15/01/2014 10:00:27         10 Y                0          3 
         1 15/01/2014 10:00:29          0 Y                0          3 
         1 15/01/2014 10:00:30         20 Y                0          3 
         1 15/01/2014 10:00:35         32 Y               30          5 
         1 15/01/2014 10:00:40         33 Y               30          5 
         1 15/01/2014 10:00:45         35 Y               30          5 
         1 15/01/2014 10:00:50         38 Y               30          5 
         1 15/01/2014 10:00:55         10 Y                0          7 

I can't take credit for the trick using two row_number() calls to generate chains of records, unfortunately, I picked that up somewhere (possibly here). The actual value of chain doesn't matter, just that they are unique within each vid and the same for all records in a contiguous block of records matching your criteria.

You're only interested in the chains of related records where the 'speed limit' was 30 (and that could just as easily be a Y/N flag or whatever), so you can use that and filter out those where the chain's speed was less than 30; and then use normal aggregate functios to get what you want:

select vid,
  min(date_time) as start_date_time,
  max(date_time) as end_date_time,
  max(speed) as max_speed
from (
  select vid, date_time, speed, status,
    case when speed >= 30 then 30 else 0 end as speed_limit,
    row_number() over (partition by vid order by date_time)
      - row_number() over (
        partition by vid, case when speed >= 30 then 30 else 0 end
        order by date_time) as chain
  from veh_speed
)
where speed_limit = 30
group by vid, chain
order by vid, start_date_time;

       VID START_DATE_TIME     END_DATE_TIME        MAX_SPEED
---------- ------------------- ------------------- ----------
         1 15/01/2014 10:00:15 15/01/2014 10:00:25         45 
         1 15/01/2014 10:00:35 15/01/2014 10:00:50         38 

SQL Fiddle.

Teheran

Very nice to use this trick about row_number, thanks for showing me that ! – Levanter

Great solution with two row_number().! You explained it well. Thanks a lot Alex. :) – Basting

Great logic Alex!! :) – Atony

Answer 2

This problem is well-known as start-of-group, you can google this. Generic approach is a) identify criteria to differ rows satisfied criteria from others c) sort them in correct order d) making a group column for each period to split them in time e) group them.

Just as example for particular case:

SQL> select vid, min(date_time) start_time, max(date_time) end_time, max(speed) max_speed
  2  from (
  3  select vid, date_time,
  4  date_time - (row_number() over(partition by vid order by date_time))*speed_sign*5/24/3600 group_time, speed_sign, speed
  5  from (
  6  select vid, date_time, decode(sign(speed-30),0,1,sign(speed-30)) speed_sign , speed
  7  from veh_speed order by date_time
  8  )) where speed_sign > 0
  9  group by vid, group_time
 10  /


   VID START_TIME          END_TIME             MAX_SPEED                   

---

     1 15.01.2014 10:00:15 15.01.2014 10:00:25         45                   
     1 15.01.2014 10:00:35 15.01.2014 10:00:50         38                                             

Pueblo

Answer 3

I used sub requests in order to group things (but I guess this is not as clear as Alex's explanations):

select z.vid, min(z.date_time) start_time, z.end_time, max(z.speed) max_speed
from
(
  with w as
  (
    select y.vid, y.date_time, y.speed, y.status, y.over_30, y.next_time, decode(y.next_time_over_30, y.next_time, 'N', 'Y') end_of_block
    from
    (
      select x.vid, x.date_time, x.speed, x.status, x.over_30, x.next_time, lead(x.date_time, 1, null) over (partition by x.vid order by x.date_time) next_time_over_30
      from
      (
        select vs.vid, vs.date_time, vs.speed, vs.status, case when vs.speed >= 30 then 'Y' else 'N' end over_30, lead(vs.date_time, 1, null) over (partition by vs.vid order by vs.date_time) next_time
        from veh_speed vs
      ) x 
      where x.over_30 = 'Y'
    ) y
  )
  select w1.vid, w1.date_time, w1.speed, w1.status, w1.over_30, w1.next_time, w1.end_of_block, min(w2.date_time) end_time
  from w w1, w w2
  where w2.end_of_block = 'Y'
    and w2.date_time >= w1.date_time
  group by w1.vid, w1.date_time, w1.speed, w1.status, w1.over_30, w1.next_time, w1.end_of_block
  order by w1.vid, w1.date_time
) z
group by z.vid, z.end_time
;

This gives:

VID     START_TIME              END_TIME                MAX_SPEED
1       Jan-15-2014 10:00:35    Jan-15-2014 10:00:50    38
1       Jan-15-2014 10:00:15    Jan-15-2014 10:00:25    45

Levanter