- Script Name MATCH_RECOGNIZE 1: Syntax intro
- Description Showing some syntax of the MATCH_RECOGNIZE clause bit by bit.
- Category SQL Analytics
- Contributor Stew Ashton
- Created Friday January 27, 2017

- Statement 1In the MATCH_RECOGNIZE clause, only PATTERN and DEFINE are required. However, by default the clause returns no columns!
`select * from dual match_recognize( pattern(a) define a as 1=1 )`

ORA-30732: table contains no user-visible columns - Statement 2We see that a row was returned. The PATTERN says "give me one row that I will call A". The DEFINE says "the input row meets the A condition if 1=1" - in other words, always.
`select count(*) from dual match_recognize( pattern(a) define a as 1=1 )`

COUNT(*) 1 - Statement 3If a column is named in the PARTITION BY clause, it becomes a visible column.
`select * from dual match_recognize( PARTITION BY dummy pattern(a) define a as 1=1 )`

DUMMY X - Statement 4Columns in the MEASURES clause are part of the output.
`select * from dual match_recognize( MEASURES dummy AS visible_column pattern(a) define a as 1=1 )`

VISIBLE_COLUMN X - Statement 5Strangely enough, the column must have an alias. Don't ask me why...
`select * from dual match_recognize( MEASURES dummy -- AS visible_column pattern(a) define a as 1=1 )`

ORA-00905: missing keyword - Statement 6If we say ALL ROWS PER MATCH, we automatically get all the input columns in the output!
`select * from ( select 1 n from dual union all select 2 from dual ) match_recognize( ALL ROWS PER MATCH -- the default is ONE ROW PER MATCH pattern(a) define a as 1=1 )`

N 1 2

2 rows selected. - Statement 7The CLASSIFIER() function tells us what condition the row met. Very useful for debugging.
`select * from ( select 1 n from dual union all select 2 from dual ) match_recognize( measures CLASSIFIER() cl all rows per match pattern(a) define a as 1=1 )`

CL N A 1 A 2

2 rows selected. - Statement 8The MATCH_NUMBER() function tells us, within each partition, which match the row belongs to. Useful for grouping rows together, and for debugging.
`select * from ( select 1 n from dual union all select 2 from dual ) match_recognize( measures classifier() cl, MATCH_NUMBER() mn all rows per match pattern(a) define a as 1=1 )`

CL MN N A 1 1 A 2 2

2 rows selected. - Statement 9Notice MN is always 1. The PATTERN no longer says "give me one row", but "give me as many rows as you can, but at least one" - as long as they all meet the condition and they belong to an uninterrupted series.
`select * from ( select 1 n from dual union all select 2 from dual ) match_recognize( measures classifier() cl, match_number() mn all rows per match PATTERN(A+) define a as 1=1 )`

CL MN N A 1 1 A 1 2

2 rows selected. - Statement 10Back to the default (one row per match). The ORDER BY clause is optional in theory, but in practice everything else depends on it. Logically, we are on the last row of the match so N should be equal to LAST(N).
`select * from ( select 1 n from dual union all select 2 from dual ) match_recognize( ORDER BY n measures classifier() cl, match_number() mn, N as n, first(N) as first_n, last(N) as last_n pattern(a+) define a as 1=1 )`

CL MN N FIRST_N LAST_N A 1 2 1 2 - Statement 11With ALL ROWS PER MATCH, LAST() means the last so far. To get the last of all, say FINAL LAST. (With ONE ROW PER MATCH, we are on the last row anyway so everything is "final".)
`select * from ( select 1 n from dual union all select 2 from dual ) match_recognize( ORDER BY n measures classifier() cl, match_number() mn, first(n) as first_n, last(N) as last_n, FINAL last(n) as final_last_n all rows per match pattern(a+) define a as 1=1 )`

N CL MN FIRST_N LAST_N FINAL_LAST_N 1 A 1 1 1 2 2 A 1 1 2 2

2 rows selected.