java Oracle SQL计算并发事件数

Question

我试图找出一种方法，可以识别表中的事件记录何时同时发生

例如，考虑在Oracle数据库中有一个名为^ {CD1>}的表：：

|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

使用类似于以下内容的谓词：

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)

这将拾取在报告期结束之前开始和报告期开始之后结束的所有事件

它不会拾取在报告期开始之前结束或在报告期结束之后开始的事件

Answer 2

我认为您需要将表连接到自身，并获取所有事件，其中包括2个事件：

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

测试数据

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

查询：

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

Answer 3

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

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或聚合来提取一天或一小时的最大值