CODEWITHSUNDEEP
javasqloracle-database
Chiefwarpaint
Chiefwarpaint

Reputation: 683

Oracle SQL - Calculating Number of Concurrent Events

I'm trying to figure out a way that I can identify when event records in a table occurred at the same time.

For instance, consider that I have a table called EVENTS in an Oracle Database::

|EVENT_UUID|HOST_NAME|START_TM|END_TM|

|1|host1|12-JUN-15 01.31.04.092000000 PM|12-JUN-15 01.55.58.716000000 PM|
|2|host2|15-JUN-15 10.02.45.494000000 AM|15-JUN-15 01.12.18.257000000 PM|
|3|host3|17-JUN-15 03.19.48.506000000 PM|17-JUN-15 03.51.59.874000000 PM|
|4|host4|18-JUN-15 09.24.36.602000000 PM|NULL|
|5|host5|18-JUN-15 12.32.43.109000000 PM|19-JUN-15 01.22.32.412000000 PM|

I know that I can find all of the events that started within a given date range by doing something like this::

SELECT *
FROM EVENTS
WHERE START_TM BETWEEN TO_DATE('2015-JUN-11', 'YYYY-MON-DD') AND TO_DATE('2015-JUN-13', 'YYYY-MON-DD');

But that only gives me all the events that started in that range.

Ultimately, I would like to be able to run reports and check things like the following,

Does anybody know of an approach that would help me identify when events were concurrently happening?

Upvotes: 4

Views: 770

Answers (3)

are
are

Reputation: 2615

I think you need join the table to itself and get all events where the range of 2 events:

event1.start between event2.start and event2.end 
OR
event1.end   between event2.start and event2.end 
OR
event1.start < event2.start and event1 > event2.end
AND CHECK THAT
event.end can be null

to filter the data - see `where` in query below
to count number of events - use count(*) over ()

test data

insert into events(EVENT_UUID,HOST_NAME,START_TM,END_TM)
  values (1,'host1',to_date('12-JUN-15 01.31.04','dd-mon-yy hh24.mi.ss'),to_date('12-JUN-15 01.55.58','dd-mon-yy hh24.mi.ss'))

insert into events(EVENT_UUID,HOST_NAME,START_TM,END_TM)
  values (2,'host2',to_date('15-JUN-15 10.02.45','dd-mon-yy hh24.mi.ss'),to_date('15-JUN-15 11.12.18','dd-mon-yy hh24.mi.ss'));

insert into events(EVENT_UUID,HOST_NAME,START_TM,END_TM)
  values (3,'host3',to_date('17-JUN-15 03.19.48','dd-mon-yy hh24.mi.ss'),to_date('17-JUN-15 03.51.59','dd-mon-yy hh24.mi.ss'));

insert into events(EVENT_UUID,HOST_NAME,START_TM,END_TM)
  values (4,'host4',to_date('18-JUN-15 09.24.36','dd-mon-yy hh24.mi.ss'),null);

insert into events(EVENT_UUID,HOST_NAME,START_TM,END_TM)
  values (5,'host5',to_date('18-JUN-15 12.32.43','dd-mon-yy hh24.mi.ss'),to_date('19-JUN-15 01.22.32','dd-mon-yy hh24.mi.ss'));

insert into events(EVENT_UUID,HOST_NAME,START_TM,END_TM)
  values (6,'host6',to_date('18-JUN-15 12.45.43','dd-mon-yy hh24.mi.ss'),to_date('19-JUN-15 01.01.32','dd-mon-yy hh24.mi.ss'));

insert into events(EVENT_UUID,HOST_NAME,START_TM,END_TM)
  values (7,'host7',to_date('12-JUN-15 01.32.04','dd-mon-yy hh24.mi.ss'),to_date('12-JUN-15 02.55.58','dd-mon-yy hh24.mi.ss'))

query:

select ev1.event_uuid, ev2.event_uuid
     , ev1.start_tm, ev1.end_tm
     , ev2.start_tm, ev2.end_tm
     ,count(*) over () as total_count
 from events ev1
  inner join events ev2
    on ((ev1.start_tm between ev2.start_tm and nvl(ev2.end_tm, sysdate))
     or (nvl(ev1.end_tm,sysdate) between ev2.start_tm and nvl(ev2.end_tm, sysdate))
     or (ev2.start_tm < ev1.start_tm and nvl(ev1.end_tm,sysdate) < nvl(ev1.end_tm, sysdate)))
        and ev1.event_uuid != ev2.event_uuid
  where to_date('18.06.2015 13', 'dd.mm.yyyy hh24') between  ev1.start_tm and nvl(ev1.end_tm,sysdate)
order by 3,4   

    EVENT_UUID  EVENT_UUID  START_TM    END_TM  START_TM    END_TM  TOTAL_COUNT
1   5   4   18/06/2015 12:32:43 19/06/2015 01:22:32 18/06/2015 09:24:36     3
2   6   5   18/06/2015 12:45:43 19/06/2015 01:01:32 18/06/2015 12:32:43 19/06/2015 01:22:32 3
3   6   4   18/06/2015 12:45:43 19/06/2015 01:01:32 18/06/2015 09:24:36     3

Upvotes: 1

Sentinel
Sentinel

Reputation: 6449

Use a predicate similar to the following:

where start_tm <= TO_DATE('2015-JUN-13 23:59:59', 'YYYY-MON-DD HH24:MI:SS')
  and (end_tm is null or TO_DATE('2015-JUN-11 00:00:00', 'YYYY-MON-DD HH24:MI:SS') <= end_tm)

this will pick up all events which started before the end of your reporting period and ended after the start of your reporting period.

It will not pick up events which ended before the start of your reporting period or started after the end of your reporting period.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can calculate the number of concurrent events by using a relatively simple technique: cumulative aggregation. The idea is to count the number of starts and stops. Then the cumulative number is the number of concurrent values.

select tm, sum(isstart) as numstarts, sum(isstop) as numstops,
       (sum(sum(isstart)) over (order by tm nulls last) -
        sum(sum(isstop)) over (order by tm nulls last)
       ) as NumConcurrent
from ((select start_tm as tm, 1 as isstart, 0 as isstop from events
      ) union all
      (select stop_tm, 0 as isstart, 1 as isstop from events
      )
     ) e
group by tm;

This gives you the number of concurrent events for each time in the data (either a start or end time. You can then extract the maximum value for a day or hour using a where clause and order by/fetch first or aggregation.

Upvotes: 3

Related Questions