I have this table and I would like to extract, by partition of split, the rows that have a part P1 in their PART column and have a row of PART='P2' in the day before.

SPLIT   PART          DATE
--------------------------
   S1     P1    21/09/2022
   S1     P2    20/09/2022
   S1     P1    19/09/2022
   S2     P1    21/09/2022
   S2     P2    19/09/2022
   S2     P1    19/09/2022

Expected output:

SPLIT   PART          DATE  OUTPUT
----------------------------------
   S1     P1    21/09/2022       X
   S1     P2    20/09/2022    NULL
   S1     P1    19/09/2022    NULL
   S2     P1    21/09/2022    NULL
   S2     P2    19/09/2022    NULL
   S2     P1    19/09/2022    NULL

CodePudding user response：

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:

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:

fiddle

CodePudding user response：

Try the following:

SELECT T.SPLIT, T.PART, T.DT,
       CASE WHEN T.PART='P1' AND EXISTS
                 (SELECT 1 FROM table_name D WHERE D.SPLIT=T.SPLIT AND D.PART='P2' AND D.DT=T.DT-1)
            THEN 'X'
       END OUTPUT
FROM table_name T
ORDER BY T.SPLIT, T.DT

See a demo.