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?

Answer 1

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.