Finding occurrences of sequence of rows with no overlapping ids given table of events and a pattern in sql

40 Views Asked by At

I have a time series of events, and I am trying to find the number of occurrences of a pattern in them. The matched rows shouldn't have overlapping ids.

example: I am trying to find the pattern x followed by y followed by z.

CREATE TABLE events (event_type VARCHAR2(10), tstamp DATE, event_id NUMBER);

INSERT INTO events VALUES('x', '01-Apr-11', 1);
INSERT INTO events VALUES('x', '02-Apr-11', 2);
INSERT INTO events VALUES('x', '03-Apr-11', 3);
INSERT INTO events VALUES('x', '04-Apr-11', 4);
INSERT INTO events VALUES('y', '06-Apr-11', 5);
INSERT INTO events VALUES('y', '07-Apr-11', 6);
INSERT INTO events VALUES('z', '08-Apr-11', 7);
INSERT INTO events VALUES('z', '09-Apr-11', 8);

and I am looking for SQL to find 2 occurrences which are x1, y5, z7 and x2, y6, z8

When I try the following match recognize, I get 4 rows instead of 2.

SELECT * FROM (
select  * from events
  order by tstamp ASC
)
  MATCH_RECOGNIZE(
    MEASURES
      MATCH_NUMBER() AS match_number,
      classifier() as cl,
      FIRST(event_id) as first_id
    ALL ROWS PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN(e1 ANY_ROW* e2 ANY_ROWS* e3)
    DEFINE
      ANY_ROW AS TRUE,
      e1 AS event_type = 'x',
      e2 AS event_type = 'y',
      e3 AS event_type = 'z'
  )
where cl in ('E1','E2','E3')

can someone help nudge me fix the match recognise SQL ?

here is dbfiddle: https://dbfiddle.uk/PMOKz7V_

0

There are 0 best solutions below