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.
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.
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 |
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 |
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 |
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 |
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 |
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.
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 |
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 |
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 |
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 |
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 |
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"
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.
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 |
Invalid Column Names Rejected
select *
from get_consecutive_rows (
running_log, columns ( dummy )
)
ORA-00904: "DUMMY": invalid identifierMore Details: https://docs.oracle.com/error-help/db/ora-00904
Column Names Must be Identifiers
select *
from get_consecutive_rows (
running_log, columns ( 'dummy' )
)
ORA-00904: : invalid identifierMore Details: https://docs.oracle.com/error-help/db/ora-00904
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 |
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 |
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 |