How to Find Patterns in Your Data with SQL Using Match_Recognize

View All Scripts Login and Run Script

Script Name How to Find Patterns in Your Data with SQL Using Match_Recognize
Description Scripts for the presentation "How to Find Patterns in Your Data with SQL". Find the slides at https://www.slideshare.net/ChrisSaxon1/how-to-find-patterns-in-your-data-with-sql
Area SQL General / Analytics
Contributor Chris Saxon (Oracle)
Created Wednesday October 09, 2019

Statement 1

alter session set nls_date_format = ' DD Mon YYYY '

Statement processed.

Statement 2

create table running_log ( 
  run_date       date not null,  
  time_in_s      int  not null, 
  distance_in_km int  not null 
)

Table created.

Statement 3

A sample data set of running training logs.

begin  
  insert into running_log values (date'2018-01-01', 310, 1);  
  insert into running_log values (date'2018-01-02', 1600, 5);  
  insert into running_log values (date'2018-01-03', 3580, 11);  
  insert into running_log values (date'2018-01-06', 1550, 5);  
  insert into running_log values (date'2018-01-07', 300, 1);  
  insert into running_log values (date'2018-01-10', 280, 1);  
  insert into running_log values (date'2018-01-13', 1530, 5);  
  insert into running_log values (date'2018-01-14', 295, 1);  
  insert into running_log values (date'2018-01-15', 292, 1);  
  commit;  
end;

Statement processed.

Statement 4
```
select * from running_log
```
RUN_DATE TIME_IN_S DISTANCE_IN_KM
01 Jan 2018 310 1
02 Jan 2018 1600 5
03 Jan 2018 3580 11
06 Jan 2018 1550 5
07 Jan 2018 300 1
10 Jan 2018 280 1
13 Jan 2018 1530 5
14 Jan 2018 295 1
15 Jan 2018 292 1

9 rows selected.

RUN_DATE	TIME_IN_S	DISTANCE_IN_KM
01 Jan 2018	310	1
02 Jan 2018	1600	5
03 Jan 2018	3580	11
06 Jan 2018	1550	5
07 Jan 2018	300	1
10 Jan 2018	280	1
13 Jan 2018	1530	5
14 Jan 2018	295	1
15 Jan 2018	292	1

Statement 5

This uses the Tabibitosan method to find each group of consecutive dates I went running. It reports the first date in each group and the number of days.

Finding Consecutive Dates with Runs

with grps as (  
  select run_date, row_number () over ( order by run_date ) rn ,  
         run_date - row_number () over ( order by run_date ) grp  
  from   running_log  
)  
  select min ( run_date ) first_run, count(*) runs  
  from   grps  
  group  by grp  
  order  by min ( run_date )

FIRST_RUN	RUNS
01 Jan 2018	3
06 Jan 2018	2
10 Jan 2018	1
13 Jan 2018	3

4 rows selected.

Statement 6

This is the SQL pattern matching equivalent of the previous query.

Finding Consecutive Dates with Runs

select *   
from   running_log   
match_recognize (  
  order by run_date  
  measures   
    first ( run_date ) as start_date,  
    count (*) as days  
  pattern ( init consecutive* )  
  define   
    consecutive as run_date = ( prev ( run_date ) + 1 )  
)

START_DATE	DAYS
01 Jan 2018	3
06 Jan 2018	2
10 Jan 2018	1
13 Jan 2018	3

4 rows selected.

Statement 7

The "all rows per match" returns each row that matches the pattern. The classifier() function returns the name of the pattern variable matched for each row.

Finding Consecutive Dates with Runs Unaggregated

select *   
from   running_log   
match_recognize (  
  order by run_date  
  measures   
    classifier() as var,  
    first ( run_date) as st, 
    count(*) as num  
  all rows per match  
  pattern ( init consecutive* )  
  define   
    consecutive as run_date = ( prev ( run_date ) + 1 )  
)

RUN_DATE	VAR	ST	NUM	TIME_IN_S	DISTANCE_IN_KM
01 Jan 2018	INIT	01 Jan 2018	1	310	1
02 Jan 2018	CONSECUTIVE	01 Jan 2018	2	1600	5
03 Jan 2018	CONSECUTIVE	01 Jan 2018	3	3580	11
06 Jan 2018	INIT	06 Jan 2018	1	1550	5
07 Jan 2018	CONSECUTIVE	06 Jan 2018	2	300	1
10 Jan 2018	INIT	10 Jan 2018	1	280	1
13 Jan 2018	INIT	13 Jan 2018	1	1530	5
14 Jan 2018	CONSECUTIVE	13 Jan 2018	2	295	1
15 Jan 2018	CONSECUTIVE	13 Jan 2018	3	292	1

9 rows selected.

Statement 8
This uses group by to find all the Monday - Sunday periods where there are at least three runs logged.
Find all Calendar Weeks with Three or More Runs
```
select trunc ( run_date , 'iw' ), count(*)   
from   running_log  
group  by trunc ( run_date , 'iw' )  
having count(*) >= 3 
```
TRUNC(RUN_DATE,'IW') COUNT(*)
08 Jan 2018 3
01 Jan 2018 5

2 rows selected.

TRUNC(RUN_DATE,'IW')	COUNT(*)
08 Jan 2018	3
01 Jan 2018	5

Statement 9

This is the pattern matching equivalent of the previous query.

Find all Calendar Weeks with Three or More Runs

select *   
from   running_log   
match_recognize (  
  order by run_date  
  measures   
    first ( trunc ( run_date, 'iw' ) ) as start_date,  
    count(*) as days  
  pattern ( within_7 {3,} )  
  define   
    within_7 as trunc ( run_date, 'iw' ) =  
      first ( trunc ( run_date, 'iw' ) )  
)

START_DATE	DAYS
01 Jan 2018	5
08 Jan 2018	3

2 rows selected.

Statement 10
The previous two queries find runs with at least three rows in a Monday - Sunday period. This returns searches for rows in any seven day period. The key difference between this at the previous queries is they start every group on a Monday. This can start each group on a different day of the week.
Find all the Seven Day Periods with Three or More Runs
```
select *   
from   running_log   
match_recognize (  
  order by run_date  
  measures   
    first ( run_date ) as start_date,  
    count(*) as days  
  pattern ( within_7 {3, } )  
  define   
    within_7 as   
      run_date < first ( run_date ) + 7  
)
```
START_DATE DAYS
01 Jan 2018 5
10 Jan 2018 4

2 rows selected.

Statement 11

This is the recursive WITH equivalent of the previous query. If you're on 11.2 you can use this method.

Find all the Seven Day Periods with Three or More Runs

with rws as (  
  select r.*,   
         row_number() over (order by run_date) rn  
  from   running_log r  
), within_7 (  
  run_date, time_in_s, distance_in_km, rn, grp_start  
) as (  
  select run_date, time_in_s, distance_in_km, rn,  
         run_date grp_start  
  from   rws  
  where  rn = 1  
  union all  
  select r.run_date, r.time_in_s, r.distance_in_km, r.rn,  
         case   
           when r.run_date < w.grp_start + 7 then grp_start  
           else r.run_date  
         end grp_start  
  from   within_7 w  
  join   rws r  
  on     w.rn + 1 = r.rn  
)  
  select grp_start, count(*)  
  from   within_7  
  group  by grp_start

GRP_START	COUNT(*)
01 Jan 2018	5
10 Jan 2018	4

2 rows selected.

Statement 12

If you're on Oracle Database 10g or higher you can also use the model clause to get the same result as the prior two searches.

Find all the Seven Day Periods with Three or More Runs

select min ( run_date ) first_run, count (*)  
from   ( 
  select *  
  from running_log   
  model    
    dimension by ( row_number() over (order by run_date) rn )    
    measures ( run_date, 1 grp, run_date grp_start )   
    rules (   
      grp_start[1] = run_date[cv()],   
      grp_start[any] =    
        case    
          when run_date[cv()] < grp_start[cv()-1] + 7 then grp_start[cv() - 1]     
          else run_date[cv()]    
        end ,   
      grp[any] =    
        case    
          when run_date[cv()] < grp_start[cv()-1] + 7 then grp[cv() - 1]   
          else nvl(grp[cv() - 1] + 1, 1)   
        end    
    ) 
) 
group by grp  
having count(*) >= 3

FIRST_RUN	COUNT(*)
01 Jan 2018	5
10 Jan 2018	4

2 rows selected.

Statement 13
The classifier function returns the name of the last variable matched. But by default match_recognize uses "one row per match". So it groups all chains of faster runs into one, just reporting the last.
Tagging Runs as Faster or Slower than the Previous
```
select *  
from running_log match_recognize (  
  order by run_date  
  measures  
    classifier() as faster  
  pattern ( slower faster *)   
  define  
    faster as time_in_s < prev( time_in_s )  
)
```
FASTER
SLOWER
SLOWER
FASTER
FASTER

4 rows selected.

FASTER
SLOWER
SLOWER
FASTER
FASTER

Statement 14

To see the variable matched for each row, use the clause "all rows per match"

Tagging Runs as Faster or Slower than the Previous

select *  
from running_log match_recognize (  
  order by run_date  
  measures  
    classifier() as faster  
  all rows per match  
  pattern ( slower faster *)   
  define  
    faster as time_in_s < prev( time_in_s )  
)

RUN_DATE	FASTER	TIME_IN_S	DISTANCE_IN_KM
01 Jan 2018	SLOWER	310	1
02 Jan 2018	SLOWER	1600	5
03 Jan 2018	SLOWER	3580	11
06 Jan 2018	FASTER	1550	5
07 Jan 2018	FASTER	300	1
10 Jan 2018	FASTER	280	1
13 Jan 2018	SLOWER	1530	5
14 Jan 2018	FASTER	295	1
15 Jan 2018	FASTER	292	1

9 rows selected.

Statement 15

Comparing runs of different lengths will almost always classify the shorter run as faster. To avoid this, the partition by clause carves the data set up into groups. Thus the database only compares times for runs of the same distance.

Tagging Runs as Faster or Slower than the Previous

select *  
from running_log match_recognize (  
  partition by distance_in_km  
  order by run_date  
  measures  
    classifier() as faster  
  all rows per match  
  pattern ( slower faster *)   
  define  
    faster as time_in_s < prev( time_in_s )  
)

DISTANCE_IN_KM	RUN_DATE	FASTER	TIME_IN_S
1	01 Jan 2018	SLOWER	310
1	07 Jan 2018	FASTER	300
1	10 Jan 2018	FASTER	280
1	14 Jan 2018	SLOWER	295
1	15 Jan 2018	FASTER	292
5	02 Jan 2018	SLOWER	1600
5	06 Jan 2018	FASTER	1550
5	13 Jan 2018	FASTER	1530
11	03 Jan 2018	SLOWER	3580

9 rows selected.

Statement 16
This sums up all the rows with a total distance up to 10k. The sum in ten_k returns the cumulative total. So the database keeps adding rows to the group until the total distance is greater than 10.
Find the Average Pace for Runs With a Total Distance Up To 10k
```
select * from running_log  
match_recognize (  
 order by run_date  
 measures   
   first ( run_date ) as init ,  
   round (   
     avg ( time_in_s /  distance_in_km )   
   , 2 ) as mean_pace,  
   sum ( distance_in_km ) as dist  
 pattern ( ten_k+ )  
 define   
   ten_k as   
     sum ( distance_in_km ) <= 10  
)
```
INIT MEAN_PACE DIST
01 Jan 2018 315 6
06 Jan 2018 296.67 7
13 Jan 2018 297.67 7

3 rows selected.
Statement 17
The previous query has a limit of 10. So any runs longer than 10k are automatically excluded. This changes the query as many rows as is needed to take the total past 10k. Once the total distance is 10k or more, the database starts a new group on the next row.
Find the Average Pace for Runs With a Total Distance Up To 10k
```
select * from running_log  
match_recognize (  
 order by run_date  
 measures   
   first ( run_date ) as init ,  
   round (   
     avg ( time_in_s /  distance_in_km )   
   , 2 ) as mean_pace,  
   sum ( distance_in_km ) as dist  
 pattern ( under_10k* over_10k )  
 define   
   under_10k as   
     sum ( distance_in_km ) < 10,  
   over_10k as   
     sum ( distance_in_km ) >= 10  
)
```
INIT MEAN_PACE DIST
01 Jan 2018 318.48 17
06 Jan 2018 299 12

2 rows selected.
Statement 18
This is similar to the previous query. But the clause "after match skip to next row" means that once the total distance exceed 10k, it starts the second group from the second row. And the third group from the third row. And so on. This means each row can appear in many groups. In the previous query, each row is in at most one group.
Find the Average Pace for Runs With a Total Distance Up To 10k
```
select * from running_log  
match_recognize (  
 order by run_date  
 measures   
   first ( run_date ) as init ,  
   round (   
     avg ( time_in_s /  distance_in_km )   
   , 2 ) as mean_pace,  
   sum ( distance_in_km ) as dist  
 after match skip to next row  
 pattern ( under_10k* over_10k )  
 define   
   under_10k as   
     sum ( distance_in_km ) < 10,  
   over_10k as   
     sum ( distance_in_km ) >= 10  
)
```
INIT MEAN_PACE DIST
01 Jan 2018 318.48 17
02 Jan 2018 322.73 16
03 Jan 2018 325.45 11
06 Jan 2018 299 12

4 rows selected.

INIT	MEAN_PACE	DIST
01 Jan 2018	315	6
06 Jan 2018	296.67	7
13 Jan 2018	297.67	7

INIT	MEAN_PACE	DIST
01 Jan 2018	318.48	17
06 Jan 2018	299	12

INIT	MEAN_PACE	DIST
01 Jan 2018	318.48	17
02 Jan 2018	322.73	16
03 Jan 2018	325.45	11
06 Jan 2018	299	12

Statement 19

This searches for a sequence of events across rows. Here that's a 5k run immediately followed by two or more 1k runs in a seven day period.

One 5k Run Followed by 2+1k Runs in a 7 Day Period

select * from running_log  
match_recognize (  
 order by run_date  
 measures   
   first ( run_date ) as start_date,  
   count (*) as total_runs  
 pattern ( five_mile one_mile {2, } )  
 define  
   five_mile as distance_in_km = 5,  
   one_mile as distance_in_km = 1 and  
      run_date < first ( run_date ) + 7  
)

START_DATE	TOTAL_RUNS
06 Jan 2018	3
13 Jan 2018	3

2 rows selected.

Additional Information

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