sqlteradata

Aggregate 3-month rolling dates with overlap


Suppose I have below dataset:

date    Value   
01-Jul-24   37    
01-Aug-24   76   
01-Sep-24   25    
01-Oct-24   85    
01-Nov-24   27    
01-Dec-24   28

And I want to aggregate by 3 months rolling:

Date_Period Sum   
Oct ~ Dec 2024  140    
Sep ~ Nov 2024  137    
Aug ~ Oct 2024  186

As you can see there is an overlap of 2 months in between the date groups and can't find a similar post on this problem.

My initial thoughts were over(partition.. preceding..) but that would mean multiple columns and my desired output is only one date_period column and one aggregate column


Solution

  • Running a window function, even for 1 column (in fact 2, because the start date has to be computed from the window too), is probably more efficient than subselecting the 3 preceding dates for each row.

    It's a bit hard to find a Teradata instance to try code, but adapting this should work relatively well:

    select
        min(d) over (order by d rows between 2 preceding and current row) as Date_Start,
        d as Date_End,
        sum(v) over (order by d rows between 2 preceding and current row) as Sum
    from t
    order by d desc;
    

    (example with PostgreSQL)