Match_Recognize - who is afraid of red, yellow and blue

View All Scripts Login to Run Script

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/
Area 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 23
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.
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 24
Using 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.
```

SEQ
7

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

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database