Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mohammed .

Asked: August 11, 2020 - 5:52 am UTC

Last updated: August 11, 2020 - 1:24 pm UTC

Version: 11G

Viewed 1000+ times

You Asked

i want to make rankning of trucks exit based on break more than 1 hour

like eg below


TRUCK EXIT
T1 10:00 PM
T2 10:05 PM
T3 12:00 PM
T4 12:05 PM
T5 12:10 PM
T6 12:20 PM

Result should be like...... leaving break more than 1 hours gaps

10:00 10:05 1
12:00 12:20 2




with LiveSQL Test Case:

and Chris said...

I can't get your Live SQL link to work - could you check this please?

Also it's not clear what a break of more than 1 hour means:

* 1 hour since the last exit time?
* 1 hour since the first exit time of the group?

Either way, from 12c this is simple - use pattern matching!

I'm assuming you mean 1 hour since the last exit. In which case you want to group together all the rows where:

The current exit time < previous exit time + 1 hour

We can define this as the pattern variable:

within_hour as exit_time < prev ( exit_time ) + 1/24


If you want the time since the first exit in the group, change prev -> first.

There can be any number of rows in this group. So you want to search for any row followed by zero or more within_hour rows:

pattern ( init within_hour* )


Giving:

create table t (
  truck     varchar2(2),
  exit_time date
);

insert into t values ( 'T1', to_date ( '2020-08-11 10:00 AM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T2', to_date ( '2020-08-11 10:05 AM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T3', to_date ( '2020-08-11 12:00 PM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T4', to_date ( '2020-08-11 12:05 PM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T5', to_date ( '2020-08-11 12:10 PM', 'yyyy-mm-dd hh:mi am' ) );
insert into t values ( 'T6', to_date ( '2020-08-11 12:20 PM', 'yyyy-mm-dd hh:mi am' ) );

commit;

select * from t
  match_recognize (
    order by exit_time
    measures
      first ( exit_time ) as start_time,
      last ( exit_time ) as end_time,
      match_number() as grp
    pattern ( init within_hour* )
    define
      within_hour as exit_time < prev ( exit_time ) + 1/24
  );

START_TIME              END_TIME                GRP   
11-AUG-2020 10:00:00    11-AUG-2020 10:05:00         1 
11-AUG-2020 12:00:00    11-AUG-2020 12:20:00         2 


I see you're on 11g, so this won't work :( There are various other ways you can tackle this, such as using recursive with:

with rws as (
  select t.*,
         row_number () over ( 
           order by exit_time
         ) rn
  from   t
), grps ( exit_time, start_time, end_time, rn, grp ) as (
  select exit_time, exit_time, exit_time, rn, 1
  from   rws
  where  rn = 1
  union all
  select r.exit_time, 
         case
           when r.exit_time > g.end_time + 1/24 then r.exit_time
           else g.start_time
         end, 
         r.exit_time, r.rn, 
         case
           when r.exit_time > g.end_time + 1/24 then grp + 1
           else grp
         end 
  from   grps g
  join   rws r
  on     g.rn + 1 = r.rn
)
  select start_time, max ( end_time ), grp 
  from   grps
  group  by grp, start_time
  order  by grp;
  
START_TIME              MAX(END_TIME)           GRP   
11-AUG-2020 10:00:00    11-AUG-2020 10:05:00         1 
11-AUG-2020 12:00:00    11-AUG-2020 12:20:00         2 

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Wow that works like charm

A reader, August 11, 2020 - 12:04 pm UTC

awesome query chris thanks alooooooooooooot
Chris Saxon
August 11, 2020 - 1:24 pm UTC

You're welcoooooommmmmeeee

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.