Does match_recognize have an option to keep all original rows and to flag the matches?

Question

I would like to add a flag to the original rows which indicates when a match was found. Is there a better way using only MATCH_RECOGNIZE without the need for the RIGHT JOIN that I've done below? There are 6 matches and I would like to flag these 6 matches by adding a new column to the original table of 60 rows.

WITH match_results AS (
    SELECT *
    FROM Ticker 
    MATCH_RECOGNIZE (
     PARTITION BY symbol
     ORDER BY tstamp
     MEASURES 
      FIRST(down.tstamp) AS start_ts,
      LAST(DOWN.tstamp) AS bottom_ts,
      LAST(UP.tstamp) AS end_ts
     ONE ROW PER MATCH
     AFTER MATCH SKIP PAST LAST ROW
     PATTERN (STRT DOWN+ UP+)
     DEFINE
      DOWN AS price < PREV(price), 
      UP AS price > PREV(price)
    ) MR
    ORDER BY MR.symbol, MR.start_ts
)
SELECT * FROM match_results
RIGHT JOIN ticker 
ON match_results.SYMBOL = ticker.symbol
AND match_results.bottom_ts = ticker.TSTAMP;

Here's the example data:

CREATE TABLE ticker (SYMBOL VARCHAR2(10), tstamp DATE, price NUMBER);

BEGIN
INSERT INTO ticker VALUES('ACME', '01-Apr-11', 12);
INSERT INTO ticker VALUES('GLOBEX', '17-Apr-11', 8);
INSERT INTO ticker VALUES('GLOBEX', '01-Apr-11', 11);
INSERT INTO ticker VALUES('OSCORP', '20-Apr-11', 9);
INSERT INTO ticker VALUES('ACME', '02-Apr-11', 17);
INSERT INTO ticker VALUES('OSCORP', '19-Apr-11', 11);
INSERT INTO ticker VALUES('ACME', '03-Apr-11', 19);
INSERT INTO ticker VALUES('GLOBEX', '03-Apr-11', 13);
INSERT INTO ticker VALUES('OSCORP', '18-Apr-11', 12);
INSERT INTO ticker VALUES('GLOBEX', '02-Apr-11', 12);
INSERT INTO ticker VALUES('ACME', '04-Apr-11', 21);
INSERT INTO ticker VALUES('GLOBEX', '04-Apr-11', 12);
INSERT INTO ticker VALUES('OSCORP', '17-Apr-11', 14);
INSERT INTO ticker VALUES('OSCORP', '15-Apr-11', 12);
INSERT INTO ticker VALUES('OSCORP', '14-Apr-11', 15);
INSERT INTO ticker VALUES('OSCORP', '16-Apr-11', 16);
INSERT INTO ticker VALUES('ACME', '05-Apr-11', 25);
INSERT INTO ticker VALUES('GLOBEX', '05-Apr-11', 11);
INSERT INTO ticker VALUES('ACME', '06-Apr-11', 12);
INSERT INTO ticker VALUES('GLOBEX', '06-Apr-11', 10);
INSERT INTO ticker VALUES('ACME', '07-Apr-11', 15);
INSERT INTO ticker VALUES('GLOBEX', '07-Apr-11', 9);
INSERT INTO ticker VALUES('GLOBEX', '08-Apr-11', 8);
INSERT INTO ticker VALUES('ACME', '08-Apr-11', 20);
INSERT INTO ticker VALUES('OSCORP', '13-Apr-11', 11);
INSERT INTO ticker VALUES('ACME', '13-Apr-11', 25);
INSERT INTO ticker VALUES('ACME', '10-Apr-11', 25);
INSERT INTO ticker VALUES('ACME', '11-Apr-11', 19);
INSERT INTO ticker VALUES('ACME', '09-Apr-11', 24);
INSERT INTO ticker VALUES('GLOBEX', '09-Apr-11', 9);
INSERT INTO ticker VALUES('OSCORP', '12-Apr-11', 12);
INSERT INTO ticker VALUES('GLOBEX', '10-Apr-11', 9);
INSERT INTO ticker VALUES('OSCORP', '11-Apr-11', 15);
INSERT INTO ticker VALUES('GLOBEX', '11-Apr-11', 9);
INSERT INTO ticker VALUES('OSCORP', '10-Apr-11', 15);
INSERT INTO ticker VALUES('ACME', '12-Apr-11', 15);
INSERT INTO ticker VALUES('GLOBEX', '12-Apr-11', 9);
INSERT INTO ticker VALUES('OSCORP', '09-Apr-11', 16);
INSERT INTO ticker VALUES('GLOBEX', '13-Apr-11', 10);
INSERT INTO ticker VALUES('OSCORP', '08-Apr-11', 20);
INSERT INTO ticker VALUES('ACME', '14-Apr-11', 25);
INSERT INTO ticker VALUES('GLOBEX', '14-Apr-11', 11);
INSERT INTO ticker VALUES('OSCORP', '07-Apr-11', 17);
INSERT INTO ticker VALUES('OSCORP', '06-Apr-11', 20);
INSERT INTO ticker VALUES('ACME', '15-Apr-11', 14);
INSERT INTO ticker VALUES('GLOBEX', '15-Apr-11', 12);
INSERT INTO ticker VALUES('ACME', '17-Apr-11', 14);
INSERT INTO ticker VALUES('ACME', '16-Apr-11', 12);
INSERT INTO ticker VALUES('GLOBEX', '16-Apr-11', 11);
INSERT INTO ticker VALUES('OSCORP', '05-Apr-11', 17);
INSERT INTO ticker VALUES('ACME', '18-Apr-11', 24);
INSERT INTO ticker VALUES('GLOBEX', '18-Apr-11', 7);
INSERT INTO ticker VALUES('OSCORP', '04-Apr-11', 18);
INSERT INTO ticker VALUES('OSCORP', '03-Apr-11', 19);
INSERT INTO ticker VALUES('ACME', '19-Apr-11', 23);
INSERT INTO ticker VALUES('GLOBEX', '19-Apr-11', 5);
INSERT INTO ticker VALUES('OSCORP', '02-Apr-11', 22);
INSERT INTO ticker VALUES('ACME', '20-Apr-11', 22);
INSERT INTO ticker VALUES('GLOBEX', '20-Apr-11', 3);
INSERT INTO ticker VALUES('OSCORP', '01-Apr-11', 22);

commit;
END;

And here are my results:

score 2 · Accepted Answer · answered Jun 20 '19 at 16:50

2

You could use ALL ROWS PER MATCH SHOW EMPTY MATCHES:

SELECT *
FROM Ticker 
MATCH_RECOGNIZE (
 PARTITION BY symbol
 ORDER BY tstamp
 MEASURES 
  FIRST(down.tstamp) AS start_ts,
  LAST(DOWN.tstamp) AS bottom_ts,
  LAST(UP.tstamp) AS end_ts,
  CLASSIFIER() AS classifier
 ALL ROWS PER MATCH WITH UNMATCHED ROWS
 AFTER MATCH SKIP PAST LAST ROW
 PATTERN (STRT DOWN+ UP+)
 DEFINE
  DOWN AS price < PREV(price), 
  UP AS price > PREV(price)
) MR
ORDER BY MR.symbol, MR.start_ts;

db<>fiddle demo

answered Jun 20 '19 at 16:50

Lukasz Szozda

162,964
23
234
275

The answer you've provided uses MATCH_RECOGNIZE without a RIGHT_JOIN and that's what I asked for. I'm still having some trouble understanding how it works and realize I didn't know how to specify the question precisely. Now thinking about it more here: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a47bf573e68a6157cfbe3238bb1e625f. You'll see I updated the second to last query. It now marks the last component of the 3 part pattern. I don't understand why the first row is not 08-Apr(START_TS), 09-Apr(BOTTOM_TS), 10-Apr(END_TS). And your method has more results than mine. Why is this? – Bobby Jun 20 '19 at 19:40
1

@Bobby `I don't understand why the first row is not 08-Apr(START_TS), 09-Apr(BOTTOM_TS), 10-Apr(END_TS). ` Pattern is defined as `(STRT DOWN+ UP+)` so it takes as many down as possible – Lukasz Szozda Jun 20 '19 at 19:48

Does match_recognize have an option to keep all original rows and to flag the matches?

1 Answers1