Oracle Live SQL - Script: How to Make Reusable SQL Pattern Matching Clauses with SQL Macros

Statement 1

Create the Table

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

Table created.

Statement 2

Load the Data

begin    
  insert into running_log values (date'2020-04-01', 310, 1);    
  insert into running_log values (date'2020-04-02', 1600, 5);    
  insert into running_log values (date'2020-04-03', 3580, 11);    
  insert into running_log values (date'2020-04-06', 1550, 5);    
  insert into running_log values (date'2020-04-07', 300, 1);    
  insert into running_log values (date'2020-04-10', 280, 1);    
  insert into running_log values (date'2020-04-13', 1530, 5);    
  insert into running_log values (date'2020-04-14', 295, 1);    
  insert into running_log values (date'2020-04-15', 292, 1);    
    
  insert into running_log values (date'2020-04-17', 1510, 5);    
  insert into running_log values (date'2020-04-18', 289, 1);    
  insert into running_log values (date'2020-04-19', 302, 1);    
  insert into running_log values (date'2020-04-20', 1545, 5);    
  insert into running_log values (date'2020-04-21', 595, 2);    
  insert into running_log values (date'2020-04-22', 281, 1);    
    
  insert into running_log values (date'2020-04-24', 600, 2);    
  insert into running_log values (date'2020-04-25', 589, 2);    
  insert into running_log values (date'2020-04-27', 301, 1);    
  insert into running_log values (date'2020-04-28', 2430, 8);    
  insert into running_log values (date'2020-04-29', 599, 2);    
  insert into running_log values (date'2020-04-30', 330, 1);    
  commit;    
end;

Statement processed.

Statement 3

select * from running_log


RUN_DATE TIME_IN_S DISTANCE_IN_KM
01-APR-20 310 1
02-APR-20 1600 5
03-APR-20 3580 11
06-APR-20 1550 5
07-APR-20 300 1
10-APR-20 280 1
13-APR-20 1530 5
14-APR-20 295 1
15-APR-20 292 1
17-APR-20 1510 5
18-APR-20 289 1
19-APR-20 302 1
20-APR-20 1545 5
21-APR-20 595 2
22-APR-20 281 1
24-APR-20 600 2
25-APR-20 589 2
27-APR-20 301 1
28-APR-20 2430 8
29-APR-20 599 2
30-APR-20 330 1

21 rows selected.

RUN_DATE	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	310	1
02-APR-20	1600	5
03-APR-20	3580	11
06-APR-20	1550	5
07-APR-20	300	1
10-APR-20	280	1
13-APR-20	1530	5
14-APR-20	295	1
15-APR-20	292	1
17-APR-20	1510	5
18-APR-20	289	1
19-APR-20	302	1
20-APR-20	1545	5
21-APR-20	595	2
22-APR-20	281	1
24-APR-20	600	2
25-APR-20	589	2
27-APR-20	301	1
28-APR-20	2430	8
29-APR-20	599	2
30-APR-20	330	1

Statement 4

The pattern searches for any row (init) followed by any number of rows (consecutive*) where the current date equals the previous row's date plus one day.

Find Consecutive Rows

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-APR-20 3
06-APR-20 2
10-APR-20 1
13-APR-20 3
17-APR-20 6
24-APR-20 2
27-APR-20 4

7 rows selected.

START_DATE	DAYS
01-APR-20	3
06-APR-20	2
10-APR-20	1
13-APR-20	3
17-APR-20	6
24-APR-20	2
27-APR-20	4

Statement 5

Changing the regular expression to consecutive{2} searches for sequences of exactly three consecutive rows.

Find Three Consecutive Rows

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


START_DATE DAYS
01-APR-20 3
13-APR-20 3
17-APR-20 3
20-APR-20 3
27-APR-20 3

5 rows selected.

START_DATE	DAYS
01-APR-20	3
13-APR-20	3
17-APR-20	3
20-APR-20	3
27-APR-20	3

Statement 6

By default match_recognize returns one row for all the rows matching the pattern. So if you have a chain of ten consecutive rows, it only returns one row in the output. Adding the all rows per match clause returns every row that matches the pattern.

Show All the Consecutive Rows

select *     
from   running_log match_recognize (   
  order by run_date    
  measures     
    first ( run_date ) as start_date,    
    count (*) as days   
  all rows per match  
  pattern ( init consecutive{2} )    
  define consecutive as run_date = ( prev ( run_date ) + 1 )    
)


RUN_DATE START_DATE DAYS TIME_IN_S DISTANCE_IN_KM
01-APR-20 01-APR-20 1 310 1
02-APR-20 01-APR-20 2 1600 5
03-APR-20 01-APR-20 3 3580 11
13-APR-20 13-APR-20 1 1530 5
14-APR-20 13-APR-20 2 295 1
15-APR-20 13-APR-20 3 292 1
17-APR-20 17-APR-20 1 1510 5
18-APR-20 17-APR-20 2 289 1
19-APR-20 17-APR-20 3 302 1
20-APR-20 20-APR-20 1 1545 5
21-APR-20 20-APR-20 2 595 2
22-APR-20 20-APR-20 3 281 1
27-APR-20 27-APR-20 1 301 1
28-APR-20 27-APR-20 2 2430 8
29-APR-20 27-APR-20 3 599 2

15 rows selected.

RUN_DATE	START_DATE	DAYS	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	01-APR-20	1	310	1
02-APR-20	01-APR-20	2	1600	5
03-APR-20	01-APR-20	3	3580	11
13-APR-20	13-APR-20	1	1530	5
14-APR-20	13-APR-20	2	295	1
15-APR-20	13-APR-20	3	292	1
17-APR-20	17-APR-20	1	1510	5
18-APR-20	17-APR-20	2	289	1
19-APR-20	17-APR-20	3	302	1
20-APR-20	20-APR-20	1	1545	5
21-APR-20	20-APR-20	2	595	2
22-APR-20	20-APR-20	3	281	1
27-APR-20	27-APR-20	1	301	1
28-APR-20	27-APR-20	2	2430	8
29-APR-20	27-APR-20	3	599	2

Statement 7

Match_recognize excludes any rows that don't match the pattern. To show every row, whether or not it matches the pattern, use the clause: all rows per match with unmatched rows. This is useful when writing and debugging these queries to help you understand why rows are missing from the output.

Include Unmatched Rows

select *     
from   running_log match_recognize (    
  order by run_date    
  measures     
    first ( run_date ) as start_date,    
    count (*) as days_so_far,  
    final count (*) as total_days,  
    classifier() as variable  
  all rows per match with unmatched rows  
  pattern ( init consecutive{2} )    
  define consecutive as run_date = ( prev ( run_date ) + 1 )    
)


RUN_DATE START_DATE DAYS_SO_FAR TOTAL_DAYS VARIABLE TIME_IN_S DISTANCE_IN_KM
01-APR-20 01-APR-20 1 3 INIT 310 1
02-APR-20 01-APR-20 2 3 CONSECUTIVE 1600 5
03-APR-20 01-APR-20 3 3 CONSECUTIVE 3580 11
06-APR-20  -  -  -  - 1550 5
07-APR-20  -  -  -  - 300 1
10-APR-20  -  -  -  - 280 1
13-APR-20 13-APR-20 1 3 INIT 1530 5
14-APR-20 13-APR-20 2 3 CONSECUTIVE 295 1
15-APR-20 13-APR-20 3 3 CONSECUTIVE 292 1
17-APR-20 17-APR-20 1 3 INIT 1510 5
18-APR-20 17-APR-20 2 3 CONSECUTIVE 289 1
19-APR-20 17-APR-20 3 3 CONSECUTIVE 302 1
20-APR-20 20-APR-20 1 3 INIT 1545 5
21-APR-20 20-APR-20 2 3 CONSECUTIVE 595 2
22-APR-20 20-APR-20 3 3 CONSECUTIVE 281 1
24-APR-20  -  -  -  - 600 2
25-APR-20  -  -  -  - 589 2
27-APR-20 27-APR-20 1 3 INIT 301 1
28-APR-20 27-APR-20 2 3 CONSECUTIVE 2430 8
29-APR-20 27-APR-20 3 3 CONSECUTIVE 599 2
30-APR-20  -  -  -  - 330 1

21 rows selected.

RUN_DATE	START_DATE	DAYS_SO_FAR	TOTAL_DAYS	VARIABLE	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	01-APR-20	1	3	INIT	310	1
02-APR-20	01-APR-20	2	3	CONSECUTIVE	1600	5
03-APR-20	01-APR-20	3	3	CONSECUTIVE	3580	11
06-APR-20	-	-	-	-	1550	5
07-APR-20	-	-	-	-	300	1
10-APR-20	-	-	-	-	280	1
13-APR-20	13-APR-20	1	3	INIT	1530	5
14-APR-20	13-APR-20	2	3	CONSECUTIVE	295	1
15-APR-20	13-APR-20	3	3	CONSECUTIVE	292	1
17-APR-20	17-APR-20	1	3	INIT	1510	5
18-APR-20	17-APR-20	2	3	CONSECUTIVE	289	1
19-APR-20	17-APR-20	3	3	CONSECUTIVE	302	1
20-APR-20	20-APR-20	1	3	INIT	1545	5
21-APR-20	20-APR-20	2	3	CONSECUTIVE	595	2
22-APR-20	20-APR-20	3	3	CONSECUTIVE	281	1
24-APR-20	-	-	-	-	600	2
25-APR-20	-	-	-	-	589	2
27-APR-20	27-APR-20	1	3	INIT	301	1
28-APR-20	27-APR-20	2	3	CONSECUTIVE	2430	8
29-APR-20	27-APR-20	3	3	CONSECUTIVE	599	2
30-APR-20	-	-	-	-	330	1

Statement 8

SQL macros return a SQL fragment. The default is a table macro. You use these in the from clause of SQL statements and pass a table as one of the parameters. This creates a generic get N rows from a table function.

A SQL Macro to Return N Rows from a Table

create or replace function top_n (  
  tab dbms_tf.table_t, num_rows number  
) return varchar2 sql_macro is  
begin  
  return 'select * from top_n.tab  
    fetch first top_n.num_rows   
      rows only';  
end top_n;

Function created.

Statement 9

When you execute a SQL macro, the database uses the exact text of your input parameters. It uses these to do a find/replace of the parameters within the string returned by the function. So this returns 5 rows from running_log: select * from running_log fetch first 5 rows only

Calling a SQL Macro

select * from top_n (  
  running_log, 5  
)


RUN_DATE TIME_IN_S DISTANCE_IN_KM
01-APR-20 310 1
02-APR-20 1600 5
03-APR-20 3580 11
06-APR-20 1550 5
07-APR-20 300 1

5 rows selected.

RUN_DATE	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	310	1
02-APR-20	1600	5
03-APR-20	3580	11
06-APR-20	1550	5
07-APR-20	300	1

Statement 10

You can also pass subqueries as arguments. Again, the database takes the exact text you've supplied. It doesn't execute the query, find it's result is two and make that part of the final SQL. The subquery itself becomes part of the final query: select * from running_log fetch first ( select 2 from dual ) rows only

Passing Subqueries as Arguments

select * from top_n (  
  running_log, ( select 2 from dual )   
)


RUN_DATE TIME_IN_S DISTANCE_IN_KM
01-APR-20 310 1
02-APR-20 1600 5

2 rows selected.

RUN_DATE	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	310	1
02-APR-20	1600	5

Statement 11

As with subqueries, the function becomes part of the final SQL statement. The final query here is: select * from running_log fetch first dbms_random.value ( 1, 10 ) rows only So multiple calls to this will return a different number of rows each time.

Passing a Function as an Argument

select * from top_n (  
  running_log, dbms_random.value ( 1, 10 )  
)


RUN_DATE TIME_IN_S DISTANCE_IN_KM
01-APR-20 310 1
02-APR-20 1600 5
03-APR-20 3580 11
06-APR-20 1550 5
13-APR-20 1530 5

5 rows selected.

RUN_DATE	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	310	1
02-APR-20	1600	5
03-APR-20	3580	11
06-APR-20	1550	5
13-APR-20	1530	5

Statement 12

select * from top_n ( 
  running_log, dbms_random.value ( 1, 10 ) 
)


RUN_DATE TIME_IN_S DISTANCE_IN_KM
01-APR-20 310 1
02-APR-20 1600 5
03-APR-20 3580 11
06-APR-20 1550 5
07-APR-20 300 1
10-APR-20 280 1

6 rows selected.

RUN_DATE	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	310	1
02-APR-20	1600	5
03-APR-20	3580	11
06-APR-20	1550	5
07-APR-20	300	1
10-APR-20	280	1

Statement 13

select * from top_n ( 
  running_log, dbms_random.value ( 1, 10 ) 
)


RUN_DATE TIME_IN_S DISTANCE_IN_KM
01-APR-20 310 1
02-APR-20 1600 5
03-APR-20 3580 11
06-APR-20 1550 5
10-APR-20 280 1
13-APR-20 1530 5
15-APR-20 292 1

7 rows selected.

RUN_DATE	TIME_IN_S	DISTANCE_IN_KM
01-APR-20	310	1
02-APR-20	1600	5
03-APR-20	3580	11
06-APR-20	1550	5
10-APR-20	280	1
13-APR-20	1530	5
15-APR-20	292	1

Statement 14

To view the complete SQL statement generated by the macro, pass the macro query to dbms_utility.expand_sql_text. This shows you the query the database executes.

Get the Final SQL Statement

declare  
  l_clob clob;  
begin  
  dbms_utility.expand_sql_text (  
    input_sql_text  => q'!select * from top_n (  
  running_log, dbms_random.value ( 1, 10 )  
)!',  
    output_sql_text => l_clob  );  
  dbms_output.put_line(l_clob);  
end;

Statement processed.
SELECT "A1"."RUN_DATE" "RUN_DATE","A1"."TIME_IN_S" "TIME_IN_S","A1"."DISTANCE_IN_KM" "DISTANCE_IN_KM" FROM  (SELECT "A3"."RUN_DATE" "RUN_DATE","A3"."TIME_IN_S" "TIME_IN_S","A3"."DISTANCE_IN_KM" "DISTANCE_IN_KM" FROM  (SELECT "A4"."RUN_DATE" "RUN_DATE","A4"."TIME_IN_S" "TIME_IN_S","A4"."DISTANCE_IN_KM" "DISTANCE_IN_KM" FROM  (SELECT "A5"."RUN_DATE" "RUN_DATE","A5"."TIME_IN_S" "TIME_IN_S","A5"."DISTANCE_IN_KM" "DISTANCE_IN_KM",ROW_NUMBER() OVER ( ORDER BY NULL) "rowlimit_$$_rownumber" FROM  (SELECT "A2"."RUN_DATE" "RUN_DATE","A2"."TIME_IN_S" "TIME_IN_S","A2"."DISTANCE_IN_KM" "DISTANCE_IN_KM" FROM "SQL_DYEMJVCGYPXNTAPGCPPZXRSQZ"."RUNNING_LOG" "A2") "A5") "A4" WHERE "A4"."rowlimit_$$_rownumber"<="SYS"."DBMS_RANDOM"."VALUE"(1,10)) "A3") "A1"

Statement 15

This places the pattern matching clause in a SQL macro. The col parameter accepts a comma-separated list of column names using the columns operator. You access these as an array within the function. So to use the column name, you need to concatenate it into the query string.

Using Pattern Matching in SQL Macros

create or replace function get_consecutive_rows (  
  tab dbms_tf.table_t, col dbms_tf.columns_t  
)  
  return varchar2 sql_macro   
as  
begin  
  return 'get_consecutive_rows.tab     
match_recognize (    
  order by ' || get_consecutive_rows.col ( 1 ) || '    
  measures     
    first ( ' || get_consecutive_rows.col ( 1 ) || ' ) as start_value,    
    count (*) as num_rows    
  pattern ( init consecutive* )    
  define     
    consecutive as ' || get_consecutive_rows.col ( 1 ) || ' = (   
      prev ( ' || get_consecutive_rows.col ( 1 ) || ' ) + 1   
    )    
)';  
end get_consecutive_rows;

Function created.

Statement 16

Pass the table (running_log) to the SQL macro and the column you using to search for consecutive values (run_date). This is the same as the original query to find consecutive running dates.

Use Pattern Matching in a SQL Macro

select *   
from   get_consecutive_rows (   
  running_log, columns ( run_date )   
)


START_VALUE NUM_ROWS
01-APR-20 3
06-APR-20 2
10-APR-20 1
13-APR-20 3
17-APR-20 6
24-APR-20 2
27-APR-20 4

7 rows selected.

START_VALUE	NUM_ROWS
01-APR-20	3
06-APR-20	2
10-APR-20	1
13-APR-20	3
17-APR-20	6
24-APR-20	2
27-APR-20	4

Statement 17

If pass a column not present in the input table, the database will reject the request.

Invalid Column Names Rejected

select *   
from   get_consecutive_rows (   
  running_log, columns ( dummy )   
)

ORA-00904: "DUMMY": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 18

You can't pass string literals as column names in the columns operator. They must be the names of columns in the table.

Column Names Must be Identifiers

select *   
from   get_consecutive_rows (   
  running_log, columns ( 'dummy' )   
)

ORA-00904: : invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 19

You can use this SQL macro to search for consecutive rows using any numeric or datetime column in the table. This searches for rows with consecutive times (in seconds).

Pass Any Table Column

select *   
from   get_consecutive_rows (   
  running_log, columns ( time_in_s )   
)


START_VALUE NUM_ROWS
280 2
289 1
292 1
295 1
300 3
310 1
330 1
589 1
595 1
599 2
1510 1
1530 1
1545 1
1550 1
1600 1
2430 1
3580 1

17 rows selected.

START_VALUE	NUM_ROWS
280	2
289	1
292	1
295	1
300	3
310	1
330	1
589	1
595	1
599	2
1510	1
1530	1
1545	1
1550	1
1600	1
2430	1
3580	1

Statement 20

The whole table is the input to a SQL macro. If you want to filter the data first, you need to do this in a subquery. This uses a CTE to limit the input to the two kilometre runs, then searching for consecutive days for this distance.

Filtering Before the Macro

with rws as (  
  select * from running_log  
  where  distance_in_km = 2  
)  
select *   
from   get_consecutive_rows (   
  rws, columns ( run_date )   
)


START_VALUE NUM_ROWS
21-APR-20 1
24-APR-20 2
29-APR-20 1

3 rows selected.

START_VALUE	NUM_ROWS
21-APR-20	1
24-APR-20	2
29-APR-20	1

Statement 21

With the SQL macro in place, you can apply it to any table. This could be a physical table, view or a named subquery. The subquery rws generates two ranges of integers, [1, 10] and [21, 24]. Passing this table to the SQL macro splits the rows into these groups.

Apply Pattern Matching to Any Tables

with rws as (   
  select level c1 from dual connect by level <= 10   
  union all    
  select level + 20 c1 from dual connect by level <= 4   
)   
  select *    
  from   get_consecutive_rows ( rws, columns ( c1 ) )


START_VALUE NUM_ROWS
1 10
21 4

2 rows selected.

How to Make Reusable SQL Pattern Matching Clauses with SQL Macros

Additional Information