sqloracle-databaseoracle12canalytic-functions

sum values based on 7-day cycle in SQL Oracle


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 !!!


Solution

  • 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.