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

gaps-and-islands, oracle, oracle11g, sql

Solution

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.

Problem

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.

Original source

Related problems