java - Oracle SQL - 计算并发事件数

Question

我正在尝试找出一种方法来识别表中的事件记录何时同时发生。

例如，假设我在 Oracle 数据库中有一个名为 EVENTS 的表::

|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|

我知道我可以通过执行以下操作找到在给定日期范围内开始的所有事件::

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

但这只会给我所有在该范围内开始的事件。

最终，我希望能够运行报告并检查如下内容，

• “一天内发生的并发事件数。”
• “一小时内发生的并发事件数。”

有人知道可以帮助我确定事件何时同时发生的方法吗？

Answer 1

您可以使用一种相对简单的技术来计算并发事件数:累积聚合。这个想法是计算开始和停止的次数。那么累计数就是并发值的个数。

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;

这会为您提供数据中每次并发事件的数量(开始时间或结束时间。然后您可以使用 where 子句和 order by/fetch first 或聚合。