sum values based on 7-day cycle in SQL Oracle

Question

I have dates and some value, I would like to sum values within 7-day cycle starting from the first date.

date         value
01-01-2021    1
02-01-2021    1
05-01-2021    1
07-01-2021    1

10-01-2021    1
12-01-2021    1
13-01-2021    1
16-01-2021    1

18-01-2021    1
22-01-2021    1
23-01-2021    1

30-01-2021    1

this is my input data with 4 groups to see what groups will create the 7-day cycle. It should start with first date and sum all values within 7 days after first date included. then start a new group with next day plus anothe 7 days, 10-01 till 17-01 and then again new group from 18-01 till 25-01 and so on. so the output will be

group1  4
group2  4
group3  3
group4  1

with match_recognize would be easy current_day < first_day + 7 as a condition for the pattern but please don't use match_recognize clause as solution !!!

score 3 · Accepted Answer · answered Feb 07 '21 at 22:14

One approach is a recursive CTE:

with tt as (
      select dte, value, row_number() over (order by dte) as seqnum
      from t
     ),
     cte (dte, value, seqnum, firstdte) as (
      select tt.dte, tt.value, tt.seqnum, tt.dte
      from tt
      where seqnum = 1
      union all
      select tt.dte, tt.value, tt.seqnum,
             (case when tt.dte < cte.firstdte + interval '7' day then cte.firstdte else tt.dte end)
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select firstdte, sum(value)
from cte
group by firstdte
order by firstdte;

This identifies the groups by the first date. You can use row_number() over (order by firstdte) if you want a number.

Here is a db<>fiddle.

sum values based on 7-day cycle in SQL Oracle

1 Answers1