Oracle SQL - Calculating Number of Concurrent Events

Question

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,

"The number of concurrent events that occurred in a day."
"The number of concurrent events that occurred in an hour."

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

Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) This is the best explanation regarding date overlap — Juan Carlos Oropeza, Nov 12 '15 at 21:36
@ShannonSeverance It means that the event hasn't ended yet. These events basically represent the consumption of user licenses and I'm wanting to keep track of how many licenses are being consumed at any given time. — Chiefwarpaint, Nov 12 '15 at 22:08

score 2 · Accepted Answer · answered Nov 12 '15 at 22:07

2

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.

answered Nov 12 '15 at 22:07

Gordon Linoff

1,242,037
58
646
786

Thanks for the post Gordon. Question though. For some reason when I run with your example, I'm getting records with START_TM values that don't actually exist in the Db. `"TM" "NUMSTARTS" "NUMSTOPS" "NUMCONCURRENT" 12-JUN-15 11.16.42.811000000 AM 2 0 2 12-JUN-15 11.17.15.001000000 AM 2 0 4 12-JUN-15 12.01.30.957000000 PM 2 0 6 12-JUN-15 12.07.55.972000000 PM 0 2 4 12-JUN-15 12.51.57.551000000 PM 0 2 2 12-JUN-15 01.27.01.938000000 PM 2 0 4 12-JUN-15 01.30.45.121000000 PM 2 0 6 ` Any idea on how that could happen? I'll post the SQL I wrote in the next comment. SO is limiting me. :) – Chiefwarpaint Nov 12 '15 at 23:17
`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 e1.START_TM as tm, 1 as isstart, 0 as isstop from EVENTS e1 ) union all (select e2.END_TM, 0 as isstart, 1 as isstop from EVENTS e2 ) ) e where tm BETWEEN TO_DATE('2015-JUN-11', 'YYYY-MON-DD') AND TO_DATE('2015-JUN-13', 'YYYY-MON-DD') group by tm order by tm;` – Chiefwarpaint Nov 12 '15 at 23:19
This is a very interesting technique. Not simple to get i, but I like it. – Florin Ghita Nov 13 '15 at 06:59
@Chiefwarpaint . . . That is correct. This produces a record for each time, both start and stop times. It will have the concurrent counts whenever they change. – Gordon Linoff Nov 14 '15 at 13:26
@GordonLinoff Thanks again for your assistance. Maybe I'm completely dense, but I still don't understand how the query is pulling back records with a START_TM equal to values that don't exist in the DB. Maybe there is some implied arithmetic occurring in this SQL that I'm missing? – Chiefwarpaint Nov 16 '15 at 16:20
Actually @GordonLinoff I should study my own data a little better. Those values do exist! Thank you for your assistance, (and patience). :) – Chiefwarpaint Nov 16 '15 at 16:46

are · Answer 2 · 2015-11-12T23:23:04.000

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

In the OP's data the time portion includes AM/PM designations. You dropped those in transcribing the sample data making event 2 end before it began. — Sentinel, Nov 12 '15 at 23:02

Sentinel · Answer 3 · 2015-11-12T22:08:55.273

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.

Oracle SQL - Calculating Number of Concurrent Events

3 Answers3