select max from previous group

I am trying to get the MAX value from the previous group. Here is a sample table.

group_id number,
cycle number,
time number);

insert into cycle_times (group_id, cycle, time) values (1, 1, 1);
insert into cycle_times (group_id, cycle, time) values (1, 1, 2);
insert into cycle_times (group_id, cycle, time) values (1, 1, 3);
insert into cycle_times (group_id, cycle, time) values (1, 1, 4);
insert into cycle_times (group_id, cycle, time) values (1, 2, 5);
insert into cycle_times (group_id, cycle, time) values (1, 2, 6);
insert into cycle_times (group_id, cycle, time) values (1, 2, 7);
insert into cycle_times (group_id, cycle, time) values (1, 2, 8);
insert into cycle_times (group_id, cycle, time) values (1, 3, 9);
insert into cycle_times (group_id, cycle, time) values (1, 3, 10);
insert into cycle_times (group_id, cycle, time) values (1, 3, 11);
insert into cycle_times (group_id, cycle, time) values (1, 3, 12);
insert into cycle_times (group_id, cycle, time) values (2, 1, 1);
insert into cycle_times (group_id, cycle, time) values (2, 1, 2);
insert into cycle_times (group_id, cycle, time) values (2, 1, 3);
insert into cycle_times (group_id, cycle, time) values (2, 1, 4);
insert into cycle_times (group_id, cycle, time) values (2, 2, 5);
insert into cycle_times (group_id, cycle, time) values (2, 2, 6);
insert into cycle_times (group_id, cycle, time) values (2, 2, 7);
insert into cycle_times (group_id, cycle, time) values (2, 2, 8);
insert into cycle_times (group_id, cycle, time) values (2, 3, 9);
insert into cycle_times (group_id, cycle, time) values (2, 3, 10);
insert into cycle_times (group_id, cycle, time) values (2, 3, 11);
insert into cycle_times (group_id, cycle, time) values (2, 3, 12);

This query works for the first group (just by virtue of it being first, I think). Each group/cycle has the max time from the previous group/cycle. But I want it to start over with each group_id so that group 2 cycle 1 prev_max = 0 and group 2 cycle 2 prev_max = 4 and so on.

select group_id, cycle, max_time, lag(max_time, 1, 0) over (order by group_id, cycle) prev_max 
from (
select distinct group_id, cycle, max(time) over (partition by group_id, cycle) max_time
from cycle_times
order by group_id, cycle
)order by group_id, cycle;

GROUP_ID    CYCLE   MAX_TIME    PREV_MAX
==========================================
1           1       4           0
1           2       8           4
1           3       12          8
2           1       4           12
2           2       8           4
2           3       12          8

Answer

You can use aggregation and lag() – you just need to properly adjust the partition and order by clause of the window function:

select
    group_id,
    cycle,
    max(time) max_time,
    lag(max(time), 1, 0) over(partition by group_id order by cycle) prev_max
from cycle_times
group by group_id, cycle
order by group_id, cycle

Demo on DB Fiddlde:

GROUP_ID | CYCLE | MAX_TIME | PREV_MAX
-------: | ----: | -------: | -------:
       1 |     1 |        4 |        0
       1 |     2 |        8 |        4
       1 |     3 |       12 |        8
       2 |     1 |        4 |        0
       2 |     2 |        8 |        4
       2 |     3 |       12 |        8

Advertisement

Answer