create table running_log (
run_date date not null,
time_in_seconds int not null,
distance_in_miles int not null
)
Table created.
begin
insert into running_log values (date'2018-01-01', 420, 1);
insert into running_log values (date'2018-01-02', 2400, 5);
insert into running_log values (date'2018-01-03', 2430, 5);
insert into running_log values (date'2018-01-06', 2350, 5);
insert into running_log values (date'2018-01-07', 410, 1);
insert into running_log values (date'2018-01-10', 400, 1);
insert into running_log values (date'2018-01-13', 2300, 5);
insert into running_log values (date'2018-01-14', 425, 1);
insert into running_log values (date'2018-01-15', 422, 1);
commit;
end;
1 row(s) inserted.
select * from running_log
| RUN_DATE | TIME_IN_SECONDS | DISTANCE_IN_MILES | 01-JAN-18 | 420 | 1 | 02-JAN-18 | 2400 | 5 | 03-JAN-18 | 2430 | 5 | 06-JAN-18 | 2350 | 5 | 07-JAN-18 | 410 | 1 | 10-JAN-18 | 400 | 1 | 13-JAN-18 | 2300 | 5 | 14-JAN-18 | 425 | 1 | 15-JAN-18 | 422 | 1 |
|---|
Model Clause Solution
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
)
| RN | RUN_DATE | GRP | GRP_START | 1 | 01-JAN-18 | 1 | 01-JAN-18 | 2 | 02-JAN-18 | 1 | 01-JAN-18 | 3 | 03-JAN-18 | 1 | 01-JAN-18 | 4 | 06-JAN-18 | 1 | 01-JAN-18 | 5 | 07-JAN-18 | 1 | 01-JAN-18 | 6 | 10-JAN-18 | 2 | 10-JAN-18 | 7 | 13-JAN-18 | 2 | 10-JAN-18 | 8 | 14-JAN-18 | 2 | 10-JAN-18 | 9 | 15-JAN-18 | 2 | 10-JAN-18 |
|---|
Recursive Subquery Factoring solution
with rws as (
select r.*,
row_number() over (order by run_date) rn
from running_log r
), within_7 (
run_date, time_in_seconds, distance_in_miles, rn, grp, grp_start
) as (
select run_date, time_in_seconds, distance_in_miles, rn,
1 grp, run_date grp_start
from rws
where rn = 1
union all
select r.run_date, r.time_in_seconds, r.distance_in_miles, r.rn,
case
when r.run_date < w.grp_start + 7 then grp
else grp + 1
end grp,
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 w.run_date,
dense_rank() over (order by grp_start) grp,
grp_start
from within_7 w
| RUN_DATE | GRP | GRP_START | 01-JAN-18 | 1 | 01-JAN-18 | 02-JAN-18 | 1 | 01-JAN-18 | 03-JAN-18 | 1 | 01-JAN-18 | 06-JAN-18 | 1 | 01-JAN-18 | 07-JAN-18 | 1 | 01-JAN-18 | 10-JAN-18 | 2 | 10-JAN-18 | 13-JAN-18 | 2 | 10-JAN-18 | 14-JAN-18 | 2 | 10-JAN-18 | 15-JAN-18 | 2 | 10-JAN-18 |
|---|
Pattern Matching Solution
select *
from running_log
match_recognize (
order by run_date
measures
match_number() as grp,
first(run_date) as grp_start
all rows per match
pattern ( strt within_7* )
define
within_7 as run_date < ( first ( run_date ) + 7 )
)
| RUN_DATE | GRP | GRP_START | TIME_IN_SECONDS | DISTANCE_IN_MILES | 01-JAN-18 | 1 | 01-JAN-18 | 420 | 1 | 02-JAN-18 | 1 | 01-JAN-18 | 2400 | 5 | 03-JAN-18 | 1 | 01-JAN-18 | 2430 | 5 | 06-JAN-18 | 1 | 01-JAN-18 | 2350 | 5 | 07-JAN-18 | 1 | 01-JAN-18 | 410 | 1 | 10-JAN-18 | 2 | 10-JAN-18 | 400 | 1 | 13-JAN-18 | 2 | 10-JAN-18 | 2300 | 5 | 14-JAN-18 | 2 | 10-JAN-18 | 425 | 1 | 15-JAN-18 | 2 | 10-JAN-18 | 422 | 1 |
|---|