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