8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1)
Introduced in Oracle 8i, Analytic Functions, also known as windowing functions, allow developers to perform tasks in SQL that were previously confined to procedural languages. Oracle 12c has added the MATCH_RECOGNIZE clause into the analytic function syntax to make pattern matching from SQL simpler. This article gives a flavour of what can be done using the MATCH_RECOGNIZE clause, but you will need to refer to the documentation to understand the true level of complexity possible.
Related articles.
Syntax Made Simple
The pattern matching syntax includes a lot of options, which make it quite daunting at first. This section describes a very simplistic view of the syntax, allowing you to take your first steps. For a detailed description of the syntax, see the documentation
Data must be processed correctly and in a deterministic fashion. The PARTITION BY and ORDER BY clauses of all analytic functions are used to break the data up into groups and make sure it is ordered correctly within each group, so order-sensitive analytic functions work as expected. This is explained here. If no partitions are defined, it is assumed the whole result set is one big partition.
PARTITION BY product ORDER BY tstamp
The MEASURES clause defines the column output that will be produced for each match.
MEASURES STRT.tstamp AS start_tstamp,
LAST(UP.tstamp) AS peak_tstamp,
LAST(DOWN.tstamp) AS end_tstamp
Along with the MEASURES, you need to decide if you want to present all the rows that represent the match, or just summary information.
[ONE ROW | ALL ROWS] PER MATCH
The pattern that represents a match is defined using pattern variables, so it makes sense to look at those first. Pattern variables can use any non-reserved word associated with an expression. Two examples are given below.
DEFINE UP AS UP.units_sold > PREV(UP.units_sold), FLAT AS FLAT.units_sold = PREV(FLAT.units_sold), DOWN AS DOWN.units_sold < PREV(DOWN.units_sold) DEFINE TWINKIES AS TWINKIES.product='TWINKIES', DINGDONGS AS DINGDONG.product='DINGDONGS', HOHOS AS HOHOS.product='HOHOS'
The pattern is then defined using regular expressions incorporating the pattern variables. Some examples are given below, but a full list of the possibilities is available from the documentation.
-- 1-Many increases, followed by 1-Many decreases in a value. A "V" shaped spike.
PATTERN (STRT UP+ DOWN+)
-- 1-Many increases, followed by a single decrease, then 1-Many increases. A single dip, during the rise.
PATTERN (STRT UP+ DOWN{1} UP+)
-- 1-5 Twinkies, followed by 1 DingDong, followed by 2 HoHos.
PATTERN(STRT TWINKIES{1,5} DINGDONGS{1} HOHOS{2})
The AFTER MATCH SKIP clause defines where the search is restarted from. Available options include the following.
AFTER MATCH SKIP TO NEXT ROW: Search continues at the row following the start of the matched pattern.AFTER MATCH SKIP PAST LAST ROW: (Default) Search continues at the row following the end of the matched pattern.AFTER MATCH SKIP TO FIRST pattern_variable: Search continues from the first row relating to the pattern defined by the specified pattern variable.AFTER MATCH SKIP TO LAST pattern_variable: Search continues from the last row relating to the pattern defined by the specified pattern variable.AFTER MATCH SKIP TO pattern_variable: Equivalent of "AFTER MATCH SKIP TO LAST pattern_variable".
There are a number of functions that provide additional information about the displayed output.
MATCH_NUMBER(): Sequential numbering of matches 1-N, indicating which output rows relate to which match.CLASSIFIER(): The pattern variable that applies to the output row. This only makes sense when all rows are displayed.
Navigation around the rows in a patterns is possible using the PREV, NEXT, FIRST and LAST functions.
PREV(UP.units_sold) -- Value of units_sold from previous row. PREV(UP.units_sold, 2) -- Value of units_sold from the row before the previous row (offset of 2 rows). NEXT(UP.units_sold) -- Value of units_sold from the next row. NEXT(UP.units_sold, 2) -- Value of units_sold from the row after the following row (offset of 2 rows). FIRST(UP.units_sold) -- First row in the pattern. FIRST(UP.units_sold, 1) -- Row following the first row (offset of 1 row). LAST(UP.units_sold) -- Last row in the pattern. LAST(UP.units_sold, 1) -- Row preceding the last row (offset of 1 row).
The pattern navigation, along with aggregate functions, can be qualified with the FINAL and RUNNING semantics keywords. These are effectively a windowing clause within the pattern, defining if the action relates to the whole pattern, or from the start of the pattern to the current row.
Setup
The examples in this article require the following two tables. The first table defines the total sales of each product per day. In this case, there is only data for a single product.
DROP TABLE sales_history PURGE; CREATE TABLE sales_history ( id NUMBER, product VARCHAR2(20), tstamp TIMESTAMP, units_sold NUMBER, CONSTRAINT sales_history_pk PRIMARY KEY (id) ); ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY'; INSERT INTO sales_history VALUES ( 1, 'TWINKIES', '01-OCT-2014', 17); INSERT INTO sales_history VALUES ( 2, 'TWINKIES', '02-OCT-2014', 19); INSERT INTO sales_history VALUES ( 3, 'TWINKIES', '03-OCT-2014', 23); INSERT INTO sales_history VALUES ( 4, 'TWINKIES', '04-OCT-2014', 23); INSERT INTO sales_history VALUES ( 5, 'TWINKIES', '05-OCT-2014', 16); INSERT INTO sales_history VALUES ( 6, 'TWINKIES', '06-OCT-2014', 10); INSERT INTO sales_history VALUES ( 7, 'TWINKIES', '07-OCT-2014', 14); INSERT INTO sales_history VALUES ( 8, 'TWINKIES', '08-OCT-2014', 16); INSERT INTO sales_history VALUES ( 9, 'TWINKIES', '09-OCT-2014', 15); INSERT INTO sales_history VALUES (10, 'TWINKIES', '10-OCT-2014', 17); INSERT INTO sales_history VALUES (11, 'TWINKIES', '11-OCT-2014', 23); INSERT INTO sales_history VALUES (12, 'TWINKIES', '12-OCT-2014', 30); INSERT INTO sales_history VALUES (13, 'TWINKIES', '13-OCT-2014', 31); INSERT INTO sales_history VALUES (14, 'TWINKIES', '14-OCT-2014', 29); INSERT INTO sales_history VALUES (15, 'TWINKIES', '15-OCT-2014', 25); INSERT INTO sales_history VALUES (16, 'TWINKIES', '16-OCT-2014', 21); INSERT INTO sales_history VALUES (17, 'TWINKIES', '17-OCT-2014', 35); INSERT INTO sales_history VALUES (18, 'TWINKIES', '18-OCT-2014', 46); INSERT INTO sales_history VALUES (19, 'TWINKIES', '19-OCT-2014', 45); INSERT INTO sales_history VALUES (20, 'TWINKIES', '20-OCT-2014', 30); COMMIT;
The following query shows the pattern of the data, which we will refer to later.
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';
SET PAGESIZE 50
COLUMN product FORMAT A10
COLUMN tstamp FORMAT A11
COLUMN graph FORMAT A50
SELECT id,
product,
tstamp,
units_sold,
RPAD('#', units_sold, '#') AS graph
FROM sales_history
ORDER BY id;
ID PRODUCT TSTAMP UNITS_SOLD GRAPH
---------- ---------- ----------- ---------- --------------------------------------------------
1 TWINKIES 01-OCT-2014 17 #################
2 TWINKIES 02-OCT-2014 19 ###################
3 TWINKIES 03-OCT-2014 23 #######################
4 TWINKIES 04-OCT-2014 23 #######################
5 TWINKIES 05-OCT-2014 16 ################
6 TWINKIES 06-OCT-2014 10 ##########
7 TWINKIES 07-OCT-2014 14 ##############
8 TWINKIES 08-OCT-2014 16 ################
9 TWINKIES 09-OCT-2014 15 ###############
10 TWINKIES 10-OCT-2014 17 #################
11 TWINKIES 11-OCT-2014 23 #######################
12 TWINKIES 12-OCT-2014 30 ##############################
13 TWINKIES 13-OCT-2014 31 ###############################
14 TWINKIES 14-OCT-2014 29 #############################
15 TWINKIES 15-OCT-2014 25 #########################
16 TWINKIES 16-OCT-2014 21 #####################
17 TWINKIES 17-OCT-2014 35 ###################################
18 TWINKIES 18-OCT-2014 46 ##############################################
19 TWINKIES 19-OCT-2014 45 #############################################
20 TWINKIES 20-OCT-2014 30 ##############################
20 rows selected.
SQL>
The following table defines an audit trail of all sales as they happen.
DROP TABLE sales_audit PURGE; CREATE TABLE sales_audit ( id NUMBER, product VARCHAR2(20), tstamp TIMESTAMP, CONSTRAINT sales_audit_pk PRIMARY KEY (id) ); ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS'; INSERT INTO sales_audit VALUES ( 1, 'TWINKIES', '01-OCT-2014 12:00:01'); INSERT INTO sales_audit VALUES ( 2, 'TWINKIES', '01-OCT-2014 12:00:02'); INSERT INTO sales_audit VALUES ( 3, 'DINGDONGS', '01-OCT-2014 12:00:03'); INSERT INTO sales_audit VALUES ( 4, 'HOHOS', '01-OCT-2014 12:00:04'); INSERT INTO sales_audit VALUES ( 5, 'HOHOS', '01-OCT-2014 12:00:05'); INSERT INTO sales_audit VALUES ( 6, 'TWINKIES', '01-OCT-2014 12:00:06'); INSERT INTO sales_audit VALUES ( 7, 'TWINKIES', '01-OCT-2014 12:00:07'); INSERT INTO sales_audit VALUES ( 8, 'DINGDONGS', '01-OCT-2014 12:00:08'); INSERT INTO sales_audit VALUES ( 9, 'DINGDONGS', '01-OCT-2014 12:00:09'); INSERT INTO sales_audit VALUES (10, 'HOHOS', '01-OCT-2014 12:00:10'); INSERT INTO sales_audit VALUES (11, 'HOHOS', '01-OCT-2014 12:00:11'); INSERT INTO sales_audit VALUES (12, 'TWINKIES', '01-OCT-2014 12:00:12'); INSERT INTO sales_audit VALUES (13, 'TWINKIES', '01-OCT-2014 12:00:13'); INSERT INTO sales_audit VALUES (14, 'DINGDONGS', '01-OCT-2014 12:00:14'); INSERT INTO sales_audit VALUES (15, 'DINGDONGS', '01-OCT-2014 12:00:15'); INSERT INTO sales_audit VALUES (16, 'HOHOS', '01-OCT-2014 12:00:16'); INSERT INTO sales_audit VALUES (17, 'TWINKIES', '01-OCT-2014 12:00:17'); INSERT INTO sales_audit VALUES (18, 'TWINKIES', '01-OCT-2014 12:00:18'); INSERT INTO sales_audit VALUES (19, 'TWINKIES', '01-OCT-2014 12:00:19'); INSERT INTO sales_audit VALUES (20, 'TWINKIES', '01-OCT-2014 12:00:20'); COMMIT;
The following query shows the order of the product sales for a specific time period, which we will refer to later.
COLUMN tstamp FORMAT A20
SELECT *
FROM sales_audit
ORDER BY tstamp;
ID PRODUCT TSTAMP
---------- ---------- --------------------
1 TWINKIES 01-OCT-2014 12:00:01
2 TWINKIES 01-OCT-2014 12:00:02
3 DINGDONG 01-OCT-2014 12:00:03
4 HOHOS 01-OCT-2014 12:00:04
5 HOHOS 01-OCT-2014 12:00:05
6 TWINKIES 01-OCT-2014 12:00:06
7 TWINKIES 01-OCT-2014 12:00:07
8 DINGDONGS 01-OCT-2014 12:00:08
9 DINGDONGS 01-OCT-2014 12:00:09
10 HOHOS 01-OCT-2014 12:00:10
11 HOHOS 01-OCT-2014 12:00:11
12 TWINKIES 01-OCT-2014 12:00:12
13 TWINKIES 01-OCT-2014 12:00:13
14 DINDONGS 01-OCT-2014 12:00:14
15 HOHOS 01-OCT-2014 12:00:15
16 TWINKIES 01-OCT-2014 12:00:16
17 TWINKIES 01-OCT-2014 12:00:17
18 TWINKIES 01-OCT-2014 12:00:18
19 TWINKIES 01-OCT-2014 12:00:19
20 TWINKIES 01-OCT-2014 12:00:20
20 rows selected.
SQL>
Examples
Check for peaks/spikes in sales, where sales go up then down. Notice the pattern variables "UP", "FLAT" and "DOWN" are defined to show an increase, no change and decrease in the value respectively. The pattern we are searching for is 1-Many UPs, optionally leveling off, followed by 1-Many Downs. The measures displayed are the start of the pattern (STRT.tstamp), the top of the peak (LAST(UP.tstamp)) and the bottom of the drop (LAST(DOWN.tstamp)), with a single row for each match. We are also displaying the MATCH_NUMBER().
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';
COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp FORMAT A11
COLUMN end_tstamp FORMAT A11
SELECT *
FROM sales_history MATCH_RECOGNIZE (
PARTITION BY product
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
LAST(UP.tstamp) AS peak_tstamp,
LAST(DOWN.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT UP+ FLAT* DOWN+)
DEFINE
UP AS UP.units_sold > PREV(UP.units_sold),
FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
) MR
ORDER BY MR.product, MR.start_tstamp;
PRODUCT START_TSTAM PEAK_TSTAMP END_TSTAMP MNO
---------- ----------- ----------- ----------- ----------
TWINKIES 01-OCT-2014 03-OCT-2014 06-OCT-2014 1
TWINKIES 06-OCT-2014 08-OCT-2014 09-OCT-2014 2
TWINKIES 09-OCT-2014 13-OCT-2014 16-OCT-2014 3
TWINKIES 16-OCT-2014 18-OCT-2014 20-OCT-2014 4
4 rows selected.
SQL>
The output tells us there were 4 distinct peaks/spikes in the sales, giving us the location of the start, peak and end of the pattern.
The following query is similar, but shows all the rows for the match and includes the CLASSIFIER() function to indicate which pattern variable is relevant for each row.
SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';
COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp FORMAT A11
COLUMN end_tstamp FORMAT A11
COLUMN cls FORMAT A5
SELECT *
FROM sales_history MATCH_RECOGNIZE (
PARTITION BY product
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
FINAL LAST(UP.tstamp) AS peak_tstamp,
FINAL LAST(DOWN.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT UP+ FLAT* DOWN+)
DEFINE
UP AS UP.units_sold > PREV(UP.units_sold),
DOWN AS DOWN.units_sold < PREV(DOWN.units_sold),
FLAT AS FLAT.units_sold = PREV(FLAT.units_sold)
) MR
ORDER BY MR.product, MR.mno, MR.tstamp;
PRODUCT TSTAMP START_TSTAM PEAK_TSTAMP END_TSTAMP MNO CLS ID UNITS_SOLD
---------- ----------- ----------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES 01-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 STRT 1 17
TWINKIES 02-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 UP 2 19
TWINKIES 03-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 UP 3 23
TWINKIES 04-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 FLAT 4 23
TWINKIES 05-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 DOWN 5 16
TWINKIES 06-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 DOWN 6 10
TWINKIES 06-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 STRT 6 10
TWINKIES 07-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 UP 7 14
TWINKIES 08-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 UP 8 16
TWINKIES 09-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 DOWN 9 15
TWINKIES 09-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 STRT 9 15
TWINKIES 10-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 10 17
TWINKIES 11-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 11 23
TWINKIES 12-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 12 30
TWINKIES 13-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 13 31
TWINKIES 14-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 DOWN 14 29
TWINKIES 15-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 DOWN 15 25
TWINKIES 16-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 DOWN 16 21
TWINKIES 16-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 STRT 16 21
TWINKIES 17-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 UP 17 35
TWINKIES 18-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 UP 18 46
TWINKIES 19-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 DOWN 19 45
TWINKIES 20-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 DOWN 20 30
23 rows selected.
SQL>
Notice how some rows are duplicated, as they represent the end of one pattern and the start of the next.
The next example identified the only occurrence of a general rise in values, containing a single dipping value.
SELECT *
FROM sales_history MATCH_RECOGNIZE (
PARTITION BY product
ORDER BY tstamp
MEASURES STRT.tstamp AS start_tstamp,
FINAL LAST(UP.tstamp) AS peak_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST DOWN
PATTERN (STRT UP+ DOWN{1} UP+)
DEFINE
UP AS UP.units_sold > PREV(UP.units_sold),
DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)
) MR
ORDER BY MR.product, MR.tstamp;
PRODUCT TSTAMP START_TSTAM PEAK_TSTAMP MNO CLS ID UNITS_SOLD
---------- ----------- ----------- ----------- ---------- ----- ---------- ----------
TWINKIES 06-OCT-2014 06-OCT-2014 13-OCT-2014 1 STRT 6 10
TWINKIES 07-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 7 14
TWINKIES 08-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 8 16
TWINKIES 09-OCT-2014 06-OCT-2014 13-OCT-2014 1 DOWN 9 15
TWINKIES 10-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 10 17
TWINKIES 11-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 11 23
TWINKIES 12-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 12 30
TWINKIES 13-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 13 31
8 rows selected.
SQL>
Turning to the audit table, we can look for specific patterns of sales. In this case, we don't want to partition the result set as the pattern spans multiple products. We are looking for sales of 2-Many TWINKIES, followed by a 2 DINGDONGS and a 1 HOHOS sale, followed by 3-Many TWINKIES sales.
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';
COLUMN tstamp FORMAT A20
COLUMN start_tstamp FORMAT A20
COLUMN end_tstamp FORMAT A20
COLUMN cls FORMAT A10
SELECT *
FROM sales_audit MATCH_RECOGNIZE (
--PARTITION BY product
ORDER BY tstamp
MEASURES FIRST(TWINKIES.tstamp) AS start_tstamp,
FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST TWINKIES
PATTERN(TWINKIES{2,} DINGDONGS{2} HOHOS{1} TWINKIES{3,})
DEFINE
TWINKIES AS TWINKIES.product='TWINKIES',
DINGDONGS AS DINGDONGS.product='DINGDONGS',
HOHOS AS HOHOS.product='HOHOS'
) MR
ORDER BY MR.mno, MR.tstamp;
TSTAMP START_TSTAMP END_TSTAMP MNO CLS ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
01-OCT-2014 12:00:12 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 TWINKIES 12 TWINKIES
01-OCT-2014 12:00:13 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 TWINKIES 13 TWINKIES
01-OCT-2014 12:00:14 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 DINGDONGS 14 DINGDONGS
01-OCT-2014 12:00:15 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 DINGDONGS 15 DINGDONGS
01-OCT-2014 12:00:16 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 HOHOS 16 HOHOS
01-OCT-2014 12:00:17 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 TWINKIES 17 TWINKIES
01-OCT-2014 12:00:18 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 TWINKIES 18 TWINKIES
01-OCT-2014 12:00:19 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 TWINKIES 19 TWINKIES
01-OCT-2014 12:00:20 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 1 TWINKIES 20 TWINKIES
9 rows selected.
SQL>
We can see there is only a single match for that pattern in the data.
Next we check for a run of TWINKIES sales separated by exactly three sales matching any combination of DINGDONGS and/or HOHOS.
SELECT *
FROM sales_audit MATCH_RECOGNIZE (
--PARTITION BY product
ORDER BY tstamp
MEASURES FIRST(TWINKIES.tstamp) AS start_tstamp,
FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
MATCH_NUMBER() AS mno,
CLASSIFIER() AS cls
ALL ROWS PER MATCH
AFTER MATCH SKIP TO LAST TWINKIES
PATTERN(TWINKIES+ (DINGDONGS | HOHOS){3} TWINKIES+)
DEFINE
TWINKIES AS TWINKIES.product='TWINKIES',
DINGDONGS AS DINGDONGS.product='DINGDONGS',
HOHOS AS HOHOS.product='HOHOS'
) MR
ORDER BY MR.mno, MR.tstamp;
TSTAMP START_TSTAMP END_TSTAMP MNO CLS ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
01-OCT-2014 12:00:01 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07 1 TWINKIES 1 TWINKIES
01-OCT-2014 12:00:02 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07 1 TWINKIES 2 TWINKIES
01-OCT-2014 12:00:03 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07 1 DINGDONGS 3 DINGDONGS
01-OCT-2014 12:00:04 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07 1 HOHOS 4 HOHOS
01-OCT-2014 12:00:05 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07 1 HOHOS 5 HOHOS
01-OCT-2014 12:00:06 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07 1 TWINKIES 6 TWINKIES
01-OCT-2014 12:00:07 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07 1 TWINKIES 7 TWINKIES
01-OCT-2014 12:00:12 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 TWINKIES 12 TWINKIES
01-OCT-2014 12:00:13 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 TWINKIES 13 TWINKIES
01-OCT-2014 12:00:14 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 DINGDONGS 14 DINGDONGS
01-OCT-2014 12:00:15 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 DINGDONGS 15 DINGDONGS
01-OCT-2014 12:00:16 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 HOHOS 16 HOHOS
01-OCT-2014 12:00:17 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 TWINKIES 17 TWINKIES
01-OCT-2014 12:00:18 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 TWINKIES 18 TWINKIES
01-OCT-2014 12:00:19 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 TWINKIES 19 TWINKIES
01-OCT-2014 12:00:20 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20 2 TWINKIES 20 TWINKIES
16 rows selected.
SQL>
We can see there are two matches to this pattern.
Once you get a feeling for the pattern matching syntax, you should be able to start building your own examples for patterns. You can also check out the edocumentation for more examples.
For more information see:
Hope this helps. Regards Tim...