- Script Name MATCH_RECOGNIZE 2: Playing with PATTERN
- Description Simple examples to demonstrate some of the syntax of the PATTERN clause.
- Category SQL Analytics
- Contributor Stew Ashton
- Created Saturday January 28, 2017

- Statement 1Create 4 test cases. Our PATTERN is one 'A' row followed by one 'B' row.
`create table t(test_case, order_by, condition, description) as select 1, 1, 'A', 'One match covers the entire partition' from dual union all select 1, 2, 'B', '' from dual union all select 2, 1, 'A', 'Match 1 starts partition' from dual union all select 2, 2, 'B', '' from dual union all select 2, 3, 'A', 'Match 2 immediately after 1, ends partition' from dual union all select 2, 4, 'B', '' from dual union all select 3, 1, 'A', 'Match 1 starts partition' from dual union all select 3, 2, 'B', '' from dual union all select 3, 3, 'A', 'Second "match" bad: intermediate row' from dual union all select 3, 4, 'X', 'This is the intermediate row' from dual union all select 3, 5, 'B', '' from dual union all select 4, 1, 'X', 'Non-matching row starts partition' from dual union all select 4, 2, 'A', 'Match 1' from dual union all select 4, 3, 'B', '' from dual union all select 4, 4, 'X', 'Non-matching row between matches' from dual union all select 4, 5, 'A', 'Match 2' from dual union all select 4, 6, 'B', '' from dual union all select 4, 7, 'X', 'Non-matching row ends partition' from dual`

Table created. - Statement 2The headers table will provide headers called INPUT and OUTPUT to separate the data for each test case.
`create table headers(TEST_CASE, ORDER_BY, MN, CONDITION, DESCRIPTION) as select test_case, cast(null as number), cast(null as number), null, 'INPUT------------' from t union select test_case, null, 1, null, 'OUTPUT-----------' from t union all select TEST_CASE, ORDER_BY, null, CONDITION, DESCRIPTION from t`

Table created. - Statement 3A PATTERN is an uninterrupted series of rows that match the conditions. PATTERN( A B ) means "one A row followed immediately by one B row". Notice in test case 3 there is no match when an X row is between A and B. However, there can be X rows outside the series, as in test case 4.
`select * from headers union all SELECT * FROM t match_recognize( PARTITION BY test_case ORDER BY order_by MEASURES MATCH_NUMBER() mn ALL ROWS PER MATCH PATTERN( A B ) DEFINE A AS condition = 'A', B AS condition = 'B' ) order by 1, 3 nulls first, 2 nulls first`

TEST_CASE ORDER_BY MN CONDITION DESCRIPTION 1 - - - INPUT------------ 1 1 - A One match covers the entire partition 1 2 - B - 1 - 1 - OUTPUT----------- 1 1 1 A One match covers the entire partition 1 2 1 B - 2 - - - INPUT------------ 2 1 - A Match 1 starts partition 2 2 - B - 2 3 - A Match 2 immediately after 1, ends partition 2 4 - B - 2 - 1 - OUTPUT----------- 2 1 1 A Match 1 starts partition 2 2 1 B - 2 3 2 A Match 2 immediately after 1, ends partition 2 4 2 B - 3 - - - INPUT------------ 3 1 - A Match 1 starts partition 3 2 - B - 3 3 - A Second "match" bad: intermediate row 3 4 - X This is the intermediate row 3 5 - B - 3 - 1 - OUTPUT----------- 3 1 1 A Match 1 starts partition 3 2 1 B - 4 - - - INPUT------------ 4 1 - X Non-matching row starts partition 4 2 - A Match 1 4 3 - B - 4 4 - X Non-matching row between matches 4 5 - A Match 2 4 6 - B - 4 7 - X Non-matching row ends partition 4 - 1 - OUTPUT----------- 4 2 1 A Match 1 4 3 1 B - 4 5 2 A Match 2 4 6 2 B -

38 rows selected. - Statement 4"^" means the beginning of the partition, so the A row must be the first row. This means at most one match per partition. Notice test case 4 that finds no matches.
`select * from headers union all SELECT * FROM t match_recognize( PARTITION BY test_case ORDER BY order_by MEASURES MATCH_NUMBER() mn ALL ROWS PER MATCH PATTERN( ^A B ) DEFINE A AS condition = 'A', B AS condition = 'B' ) order by 1, 3 nulls first, 2 nulls first`

TEST_CASE ORDER_BY MN CONDITION DESCRIPTION 1 - - - INPUT------------ 1 1 - A One match covers the entire partition 1 2 - B - 1 - 1 - OUTPUT----------- 1 1 1 A One match covers the entire partition 1 2 1 B - 2 - - - INPUT------------ 2 1 - A Match 1 starts partition 2 2 - B - 2 3 - A Match 2 immediately after 1, ends partition 2 4 - B - 2 - 1 - OUTPUT----------- 2 1 1 A Match 1 starts partition 2 2 1 B - 3 - - - INPUT------------ 3 1 - A Match 1 starts partition 3 2 - B - 3 3 - A Second "match" bad: intermediate row 3 4 - X This is the intermediate row 3 5 - B - 3 - 1 - OUTPUT----------- 3 1 1 A Match 1 starts partition 3 2 1 B - 4 - - - INPUT------------ 4 1 - X Non-matching row starts partition 4 2 - A Match 1 4 3 - B - 4 4 - X Non-matching row between matches 4 5 - A Match 2 4 6 - B - 4 7 - X Non-matching row ends partition 4 - 1 - OUTPUT-----------

32 rows selected. - Statement 5Suppose we want only the first match, but it doesn't have to start the partition? Then we can use the MATCH_NUMBER() function in the DEFINE clause.
`select * from headers where test_case = 4 union all SELECT * FROM (select * from t where test_case = 4) match_recognize( PARTITION BY test_case ORDER BY order_by MEASURES MATCH_NUMBER() mn ALL ROWS PER MATCH PATTERN( A B ) DEFINE A AS condition = 'A' and match_number() = 1, B AS condition = 'B' ) order by 1, 3 nulls first, 2 nulls first`

TEST_CASE ORDER_BY MN CONDITION DESCRIPTION 4 - - - INPUT------------ 4 1 - X Non-matching row starts partition 4 2 - A Match 1 4 3 - B - 4 4 - X Non-matching row between matches 4 5 - A Match 2 4 6 - B - 4 7 - X Non-matching row ends partition 4 - 1 - OUTPUT----------- 4 2 1 A Match 1 4 3 1 B -

11 rows selected. - Statement 6"%" means the end of the partition, so the B row must be the last row. This means at most one match per partition. Notice test cases 3 and 4 that find no matches.
`select * from headers union all SELECT * FROM t match_recognize( PARTITION BY test_case ORDER BY order_by MEASURES MATCH_NUMBER() mn ALL ROWS PER MATCH PATTERN( A B$ ) DEFINE A AS condition = 'A', B AS condition = 'B' ) order by 1, 3 nulls first, 2 nulls first`

TEST_CASE ORDER_BY MN CONDITION DESCRIPTION 1 - - - INPUT------------ 1 1 - A One match covers the entire partition 1 2 - B - 1 - 1 - OUTPUT----------- 1 1 1 A One match covers the entire partition 1 2 1 B - 2 - - - INPUT------------ 2 1 - A Match 1 starts partition 2 2 - B - 2 3 - A Match 2 immediately after 1, ends partition 2 4 - B - 2 - 1 - OUTPUT----------- 2 3 1 A Match 2 immediately after 1, ends partition 2 4 1 B - 3 - - - INPUT------------ 3 1 - A Match 1 starts partition 3 2 - B - 3 3 - A Second "match" bad: intermediate row 3 4 - X This is the intermediate row 3 5 - B - 3 - 1 - OUTPUT----------- 4 - - - INPUT------------ 4 1 - X Non-matching row starts partition 4 2 - A Match 1 4 3 - B - 4 4 - X Non-matching row between matches 4 5 - A Match 2 4 6 - B - 4 7 - X Non-matching row ends partition 4 - 1 - OUTPUT-----------

30 rows selected. - Statement 7Back to our simple pattern. Notice there are two matches: MN = 1 then 2.
`SELECT * FROM (select * from t where test_case = 2) match_recognize( PARTITION BY test_case ORDER BY order_by MEASURES MATCH_NUMBER() mn ALL ROWS PER MATCH PATTERN( A B ) DEFINE A AS condition = 'A', B AS condition = 'B' ) order by 1, 3 nulls first, 2 nulls first`

TEST_CASE ORDER_BY MN CONDITION DESCRIPTION 2 1 1 A Match 1 starts partition 2 2 1 B - 2 3 2 A Match 2 immediately after 1, ends partition 2 4 2 B -

4 rows selected. - Statement 8Now the pattern says: give me a group (A followed by B), but repeat this group as many times as possible. With test case 2, the two contiguous matches become one - but test case 4 returns two matches because of the intervening row.
`SELECT * FROM (select * from t where test_case in(2, 4)) match_recognize( PARTITION BY test_case ORDER BY order_by MEASURES MATCH_NUMBER() mn ALL ROWS PER MATCH PATTERN( (A B)+ ) DEFINE A AS condition = 'A', B AS condition = 'B' ) order by 1, 3 nulls first, 2 nulls first`

TEST_CASE ORDER_BY MN CONDITION DESCRIPTION 2 1 1 A Match 1 starts partition 2 2 1 B - 2 3 1 A Match 2 immediately after 1, ends partition 2 4 1 B - 4 2 1 A Match 1 4 3 1 B - 4 5 2 A Match 2 4 6 2 B -

8 rows selected. - Statement 9One last thing: suppose we want to accept intervening rows, but not show them. We have to account for them in the pattern: "X*" means "give me zero or more X rows". Now, if we put "{-" and "-}" around the "X*", those rows will not be output even though they are part of the match.
`select * from headers where test_case = 3 union all SELECT * FROM (select * from t where test_case = 3) match_recognize( PARTITION BY test_case ORDER BY order_by MEASURES MATCH_NUMBER() mn ALL ROWS PER MATCH PATTERN( A {-X*-} B ) DEFINE X as condition not in ('A', 'B'), A AS condition = 'A', B AS condition = 'B' ) order by 1, 3 nulls first, 2 nulls first`

TEST_CASE ORDER_BY MN CONDITION DESCRIPTION 3 - - - INPUT------------ 3 1 - A Match 1 starts partition 3 2 - B - 3 3 - A Second "match" bad: intermediate row 3 4 - X This is the intermediate row 3 5 - B - 3 - 1 - OUTPUT----------- 3 1 1 A Match 1 starts partition 3 2 1 B - 3 3 2 A Second "match" bad: intermediate row 3 5 2 B -

11 rows selected.