如何用MATCH_RECOGNIZE查找无重叠ID的事件序列模式？

我有一个事件时间序列表，需要查找其中x事件后接y事件再后接z事件的模式出现次数，且匹配行不能有重叠的event_id。

示例数据 #

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

期望找到2个匹配序列：x1,y5,z7和x2,y6,z8，但使用以下MATCH_RECOGNIZE语句时得到4个结果，不符合预期：

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

基础版修正SQL #

SELECT * FROM (
  SELECT * FROM events ORDER BY tstamp ASC
)
MATCH_RECOGNIZE(
  MEASURES
    MATCH_NUMBER() AS match_number,
    classifier() AS cl,
    event_id AS event_id
  ALL ROWS PER MATCH
  AFTER MATCH SKIP PAST LAST ROW -- 跳过当前匹配的所有行，避免ID重叠
  PATTERN(e1 e2 e3) -- 匹配按时间顺序的x→y→z序列
  DEFINE
    e1 AS event_type = 'x',
    e2 AS event_type = 'y',
    e3 AS event_type = 'z'
)
WHERE cl IN ('E1','E2','E3');

修正说明 #

1. 模式简化：原PATTERN(e1 ANY_ROW* e2 ANY_ROWS* e3)中的ANY_ROW*会匹配任意数量的中间行，导致同一个x可以关联多个y/z，产生冗余结果。改为PATTERN(e1 e2 e3)后，仅匹配按时间顺序连续出现的x→y→z（或最近的后续y/z，无其他干扰事件）。
2. 跳过规则调整：将AFTER MATCH SKIP TO NEXT ROW改为AFTER MATCH SKIP PAST LAST ROW，确保每次匹配完成后，跳过当前匹配的所有行，避免重复使用同一个事件ID参与后续匹配，满足无重叠要求。
3. 度量优化：将FIRST(event_id)改为直接取event_id，能准确获取每个匹配事件的ID，清晰展示完整序列。

支持中间无关事件的版本 #

如果x和y、y和z之间允许存在其他类型的事件，可使用以下SQL：

SELECT * FROM (
  SELECT * FROM events ORDER BY tstamp ASC
)
MATCH_RECOGNIZE(
  MEASURES
    MATCH_NUMBER() AS match_number,
    classifier() AS cl,
    event_id AS event_id
  ALL ROWS PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN(e1 (other*) e2 (other*) e3) -- 允许x/y、y/z之间存在其他非x/y/z事件
  DEFINE
    e1 AS event_type = 'x',
    e2 AS event_type = 'y',
    e3 AS event_type = 'z',
    other AS event_type NOT IN ('x','y','z')
)
WHERE cl IN ('E1','E2','E3');

内容的提问来源于stack exchange，提问作者Aditya