alter session set nls_date_format = ' DD Mon YYYY '
Statement processed.
create table running_log (
run_date date not null,
time_in_s int not null,
distance_in_km int not null
)
Table created.
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.
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|
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 |
---|