- Script Name Match_Recognize - who is afraid of red, yellow and blue
- Description This example demonstrates the use of MATCH_RECOGNIZE for pattern matching, in this case finding a very simple pattern of three subsequent rows with the colors red, yellow and blue. A more elaborate explanation is provided in this blog article: https://technology.amis.nl/2013/06/27/oracle-database-12c-pattern-matching-through-match_recognize-in-sql/
- Category Oracle 12c
- Contributor Lucas Jellema
- Created Tuesday October 20, 2015

- Statement 1
`create table events ( seq number(5) , payload varchar2(200) )`

Table created. - Statement 2
`insert into events values (1, 'red')`

1 row(s) inserted. - Statement 3
`insert into events values (2, 'blue')`

1 row(s) inserted. - Statement 4
`insert into events values (3, 'yellow')`

1 row(s) inserted. - Statement 5
`insert into events values (4, 'red')`

1 row(s) inserted. - Statement 6
`insert into events values (5, 'white')`

1 row(s) inserted. - Statement 7
`insert into events values (6, 'blue')`

1 row(s) inserted. - Statement 8
`insert into events values (7, 'red')`

1 row(s) inserted. - Statement 9
`insert into events values (8, 'yellow')`

1 row(s) inserted. - Statement 10
`insert into events values (9, 'blue')`

1 row(s) inserted. - Statement 11
`insert into events values (29, 'red')`

1 row(s) inserted. - Statement 12
`insert into events values (11, 'white')`

1 row(s) inserted. - Statement 13
`insert into events values (12, 'red')`

1 row(s) inserted. - Statement 14
`insert into events values (13, 'blue')`

1 row(s) inserted. - Statement 15
`insert into events values (14, 'blue')`

1 row(s) inserted. - Statement 16
`insert into events values (15, 'green')`

1 row(s) inserted. - Statement 17
`insert into events values (16, 'blue')`

1 row(s) inserted. - Statement 18
`insert into events values (17, 'yellow')`

1 row(s) inserted. - Statement 19
`insert into events values (18, 'red')`

1 row(s) inserted. - Statement 20
`insert into events values (19, 'yellow')`

1 row(s) inserted. - Statement 21
`insert into events values (20, 'red')`

1 row(s) inserted. - Statement 22
`insert into events values (10, 'blue')`

1 row(s) inserted. - Statement 23Our challenge in terms of SQL could be: find any occurrences of the combination red, yellow and blue in this data set. Now here is a challenge Analytical Functions are very well prepared to deal with. Using two LEAD expressions to look from each row to the next and the second next, each sequence of red, yellow and blue is easily found.
**Finding this simple, static pattern using traditional Analytical Functions**`with look_ahead_events as ( SELECT e.* , lead(payload) over (order by seq) next_color , lead(payload,2) over (order by seq) second_next_color FROM events e ) select seq from look_ahead_events where payload ='red' and next_color ='yellow' and second_next_color='blue'`

SEQ 7 - Statement 24Using the MATCH_RECOGNIZE operator we can solve the challenge in a much more elaborate way than with the analytical functions. So why show it all? Because it helps me explain what MATCH_RECOGNIZE is and where it goes beyond what analytical functions can do. image.png Oracle Database 12c: Pattern Matching through MATCH_RECOGNIZE in SQL 0 By Lucas Jellema on June 27, 2013 Database, Oracle, Oracle 12, PL/SQL Share this on .. Tweet about this on Twitter0Share on LinkedIn1Share on Facebook0Share on Google+0Email this to someoneShare on Tumblr0Buffer this page Oracle 8i (8.1.6 if I remember correctly) started with the introduction of a that wonderful new phenomenon in SQL: Analytical Functions. Functions that basically allow the result for one row in the result set to be calculated using the values in other rows. This allowed to look forward (lag) and back (lead) in result sets as well as calculate aggregates across partitions in every direction. Analytical Functions have helped to produce quite advanced analyses of data using very elegant, compact and surprisingly well performing queries. With Oracle Database 12c, our SQL just got a little better equipped to perform analysis. The MATCH_RECOGNIZE operator has been introduced – one you may know if you have ever dabbled in CQL, the Continuous Query Language that is at the heart of several complex event processors. This operator goes beyond Analytical Functions in its capabilities to analyze a data set. The comparisons MATCH_RECOGNIZE allows us to make between rows in order to decide whether or not to produce a result are more advanced (and sometimes more fuzzy) than the straightforward comparison operators available with analytical functions. That sounds a little cryptic. Let’s look at some examples. image This painting is called: Who’s afraid of red, yellow and blue. Suppose we have a data set that contains color observations: image Our challenge in terms of SQL could be: find any occurrences of the combination red, yellow and blue in this data set. Now here is a challenge Analytical Functions are very well prepared to deal with. Using two LEAD expressions to look from each row to the next and the second next, each sequence of red, yellow and blue is easily found: image Using the new MATCH_RECOGNIZE we can solve the same challenge in a much more elaborate way. So why show it all? Because it helps me explain what MATCH_RECOGNIZE is and where it goes beyond what analytical functions can do. image The Match_Recognize section ins the query acts on whatever rows are produced by the from and where clauses of the query – in a way that is somewhat similar to the MODEL clause. In this case, those rows are taken and ordered by the seq column. image Two new ‘column expressions’ are introduced (and therefore will be produced in the outcome of the SELECT) through the measures redseq and match_num. The Pattern we instruct the MATCH_RECOGNIZE to look for is defined through a regular expression: RED YELLOW BLUE. This expression is found whenever the conditions defined by RED, YELLOW and BLUE occur in this sequence – with every condition occurring exactly once (we could have defined RED+ YELLOW{0,1} BLUE* to make it look more like a regular expression, but we would have found very different paintings as well). The conditions labeled RED, YELLOW and BLUE have been defined as: an occurrence (a row in the result set) where payload is ‘red’, ‘yellow’ and ‘blue’ respectively. The measure redseq takes it value from the payload on the row matching the RED condition. MATCH_NUMBER() is the sequence assigned by the MATCH_RECOGNIZE operator to the pattern matches.
`SELECT * FROM events MATCH_RECOGNIZE ( ORDER BY seq MEASURES RED.seq AS redseq , MATCH_NUMBER() AS match_num ALL ROWS PER MATCH PATTERN (RED YELLOW BLUE) DEFINE RED AS RED.payload ='red', YELLOW AS YELLOW.payload ='yellow', BLUE AS BLUE.payload ='blue' ) MR ORDER BY MR.redseq , MR.seq`

SEQ REDSEQ MATCH_NUM PAYLOAD 7 7 1 red 8 7 1 yellow 9 7 1 blue

3 rows selected.