Saturday, 5 November 2016

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 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.
  • 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
---------- ---------- ----------- ----------  
         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.