You do not need MATCH_RECOGNIZE; instead, you can use the LAG analytic function and a CASE expression:
SELECT t.*,
CASE
WHEN part = 'P1'
AND LAG(part) OVER (PARTITION BY split ORDER BY dt) = 'P2'
AND LAG(dt) OVER (PARTITION BY split ORDER BY dt) = dt - 1
THEN 'X'
END AS output
FROM table_name t
Which, for your sample data:
CREATE TABLE table_name (SPLIT, PART, DT) AS
SELECT 'S1', 'P1', DATE '2022-09-21' FROM DUAL UNION ALL
SELECT 'S1', 'P2', DATE '2022-09-20' FROM DUAL UNION ALL
SELECT 'S1', 'P1', DATE '2022-09-19' FROM DUAL UNION ALL
SELECT 'S2', 'P1', DATE '2022-09-21' FROM DUAL UNION ALL
SELECT 'S2', 'P2', DATE '2022-09-19' FROM DUAL UNION ALL
SELECT 'S2', 'P1', DATE '2022-09-19' FROM DUAL;
Outputs:
| SPLIT |
PART |
DT |
OUTPUT |
| S1 |
P1 |
19-SEP-22 |
null |
| S1 |
P2 |
20-SEP-22 |
null |
| S1 |
P1 |
21-SEP-22 |
X |
| S2 |
P1 |
19-SEP-22 |
null |
| S2 |
P2 |
19-SEP-22 |
null |
| S2 |
P1 |
21-SEP-22 |
null |
If you do want to use MATCH_RECOGNIZE then it is more complicated:
SELECT split,
part,
dt,
CASE cls WHEN 'P1' THEN 'X' END AS output
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY split
ORDER BY dt DESC
MEASURES
CLASSIFIER() AS cls
ALL ROWS PER MATCH
PATTERN (p1 p2 | any_row)
DEFINE
p1 AS part = 'P1',
p2 AS part = 'P2' AND dt + 1 = PREV(dt)
)
Which outputs:
| SPLIT |
PART |
DT |
OUTPUT |
| S1 |
P1 |
21-SEP-22 |
X |
| S1 |
P2 |
20-SEP-22 |
null |
| S1 |
P1 |
19-SEP-22 |
null |
| S2 |
P1 |
21-SEP-22 |
null |
| S2 |
P2 |
19-SEP-22 |
null |
| S2 |
P1 |
19-SEP-22 |
null |
fiddle