sql - 重写查询以使用分析函数

Question

我有一个表事件记录 我插入， U 日期和 D 事件列表。
看她的 MWE:http://sqlfiddle.com/#!4/6c2cb1/1

DDL 声明

CREATE TABLE "EVENTS" 
   (
    "EVENT_ID" VARCHAR2(30 CHAR), --Name of the Event
    "EVENT_LOCATION" VARCHAR2(60 CHAR), --Location on which the event occured
    "EVENT_TRIGGER" VARCHAR2(2 CHAR),  --Trigger which protocolled the event (I,U or D)
    "EVENT_CHANGE_ID" NUMBER,  --Unique Sequence Number
    "EVENT_CHANGE_DATE" DATE DEFAULT SYSTIMESTAMP
   );

INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT1','LOC1','I',1,SYSTIMESTAMP-1);
INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT1','LOC2','U',11,SYSTIMESTAMP-1);
INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT1','LOC4','U',117,SYSTIMESTAMP-1);
INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT1','LOC7','D',1430,SYSTIMESTAMP-1);

INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT2','LOC1','I',2,SYSTIMESTAMP-1/48);
INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT2','LOC2','U',131,SYSTIMESTAMP-1/48);
INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT2','LOC5','D',11337,SYSTIMESTAMP-1/48);
INSERT INTO EVENTS (EVENT_ID,EVENT_LOCATION,EVENT_TRIGGER,EVENT_CHANGE_ID,EVENT_CHANGE_DATE) 
VALUES ('EVENT2','LOC7','D',14430,SYSTIMESTAMP-1/48);

我想确定已发生的事件数量 我插入 LOC1 和 D 在 LOC7 被选中而没有任何 D 之间的选举。

SELECT COUNT(*) AS QTY, TRUNC(A.EVENT_CHANGE_DATE) AS DAY
FROM (
    SELECT EVENT_ID, EVENT_CHANGE_ID, EVENT_CHANGE_DATE FROM EVENTS WHERE EVENT_TRIGGER = 'I' AND EVENT_LOCATION = 'LOC1'
    ) A,
    (SELECT EVENT_ID, EVENT_CHANGE_ID, EVENT_CHANGE_DATE FROM EVENTS WHERE EVENT_TRIGGER = 'D' AND EVENT_LOCATION = 'LOC7')
    B
WHERE B.EVENT_CHANGE_ID > A.EVENT_CHANGE_ID AND A.EVENT_ID = B.EVENT_ID
    AND not exists (SELECT EVENT_ID, EVENT_CHANGE_ID, EVENT_CHANGE_DATE FROM EVENTS WHERE EVENT_TRIGGER = 'D' AND EVENT_CHANGE_ID > A.EVENT_CHANGE_ID AND EVENT_CHANGE_ID < B.EVENT_CHANGE_ID and EVENT_ID = A.EVENT_ID) 
group by TRUNC(A.EVENT_CHANGE_DATE)
ORDER BY TRUNC(A.EVENT_CHANGE_DATE);

我的天真方法有效，但是我想知道是否可以使用分析函数重写此查询。
原始表包含多达 100 万条记录，3 倍全表扫描在执行时间和性能方面是不可行的。

甚至可以使用分析功能使此查询更有效吗？

Answer 1

这看起来很适合 SQL 模式匹配:

select * from events
match_recognize (
  partition by event_id
  order by event_change_date
  measures 
    count ( ins.* ) ins_count,
    min ( event_change_date ) dt
  pattern ( ins upd* del )
  define 
    ins as event_trigger = 'I' and event_location = 'LOC1',
    upd as event_trigger = 'U',
    del as event_trigger = 'D' and event_location = 'LOC7'
);

INS_COUNT    DT                     
           1 16-MAR-2020 12:33:58 

这会在 LOC1 处搜索 I(nserts)，然后在 LOC7 处搜索 D(elete)，中间有任意数量的 U(pdates)。