Oracle 12c - Pattern Matching Part 1

Oracle 12c Pattern Matching

Before getting into Oracle 12c Pattern Matching – the set of questions which came across after looking at Oracle 12c Patern Matching – MATCH_RECOGNIZE are

With SQL we have many possible solution, what could be that one scenario which cannot be handled or may not be an optimal solution via SQL until Oracle 11g which lead to SQL Pattern Matching

It looks big, Very BIG

So many key words

Complex

Pattern Matching comes examples like V/W patterns in stock market, suspicious financial transaction and you will find much more online, But let us get into basic question why not in 11g, Demystifying the unknown usually helps so lets begin. Am going to walk you through multiple scenarios comparing regular expression and analytical SQL.

Assume we have data stored in a comma separated format in a column like below

With proper Data Modeling in place the data is not gonna be stored in CSV format, so let us re-iterate the requirement to identifying the pattern

Scenario/Pattern 1 – Data Format = Rows
With Analytical function – LEAD the same can be achieved
SQL

WITH PRDT_ORDER AS
(SELECT
BP.*
,LEAD(PRODUCT) OVER (PARTITION BY CUSTOMER ORDER BY BUY_DT) NEXT_PRDT
,LEAD(PRODUCT,2) OVER (PARTITION BY CUSTOMER ORDER BY BUY_DT) SECOND_NEXT_PRDT
,LEAD(PRODUCT,3) OVER (PARTITION BY CUSTOMER ORDER BY BUY_DT) THIRD_NEXT_PRDT
FROM BUYING_PATTERN BP
)
SELECT CUSTOMER,BUY_DT,PRODUCT FROM PRDT_ORDER
WHERE
PRODUCT='TV'
AND NEXT_PRDT = 'BIKE'
AND SECOND_NEXT_PRDT = 'MOBILE'
AND THIRD_NEXT_PRDT = 'CAR';
CUSTOMER BUY_DT PRODUCT
-------- ------------------------------ -----------
LINDA 15-JUN-16 07.51.48.812559 AM TV

Scenario 2 – Pattern 1 + Pattern 2 – Data Format = CSV
If we want to look out for another pattern along with existing one , .ie along with pattern 1 = TV,BIKE,MOBILE,CAR, check for pattern 2 = TV,BIKE,BIKE,MOBILE it is just a simple change to the existing SQL

Scenario 3 – Pattern 3 – Data Format = CSV
Pattern 3 is some like when you think sky is the limit i.e really not sure how many of them could repeat within a pattern. For an example – All 1,2,3 from the below picture are of same pattern XYZA

Scenario 3 – Pattern 3 – Data Format = ROWS
We have two sets of data, one has a fixed pattern while other has got a subset, ie, Looking out for buying pattern, matching the order – “TV(.*) BIKE(.*) MOBILE CAR”. Both 1 and 2 in the below picture belong to the same pattern “TV BIKE MOBILE CAR”

Untill 11g if this has to be written in a single SQL, it becomes pretty tough to have it in a single optimal SQL. Oracle 12c – Pattern Matching has got the answer, when you want to perform a pattern recognition in a sequence of rows – it is MATCH_RECOGNIZE