DBA Hub

📋Steps in this guide1/3

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1)

Learn about the new pattern matching analytic function clause available in Oracle 12c onward.

oracle 12cconfigurationintermediate
by OracleDba
17 views
1

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 and 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. The clause defines the column output that will be produced for each match. Along with the , you need to decide if you want to present all the rows that represent the match, or just summary information. 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. 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 . The clause defines where the search is restarted from. Available options include the following. - : Search continues at the row following the start of the matched pattern. - : (Default) Search continues at the row following the end of the matched pattern. - : Search continues from the first row relating to the pattern defined by the specified pattern variable. - : Search continues from the last row relating to the pattern defined by the specified pattern variable. - : Equivalent of " ". There are a number of functions that provide additional information about the displayed output. - : Sequential numbering of matches 1-N, indicating which output rows relate to which match. - : 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 , , and functions. The pattern navigation, along with aggregate functions, can be qualified with the and 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
PARTITION BY product
ORDER BY tstamp

MEASURES  STRT.tstamp AS start_tstamp,
          LAST(UP.tstamp) AS peak_tstamp,
          LAST(DOWN.tstamp) AS end_tstamp

[ONE ROW | ALL ROWS] PER MATCH

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'

-- 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})

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).
2

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. The following query shows the pattern of the data, which we will refer to later. The following table defines an audit trail of all sales as they happen. The following query shows the order of the product sales for a specific time period, which we will refer to later.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
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;

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>

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;

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>
3

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 . 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 function to indicate which pattern variable is relevant for each row. 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. 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. 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 e documentation for more examples. For more information see: - SQL for Pattern Matching - Analytic Functions Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
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>

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>

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>

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!