1

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

Improve this question

1 Answer 1

Reset to default
1

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Nice solution. (In Teradata) multiple window function will be calculated in a single step as long as PARTITION and ORDER are the same, so adding more will use just a bit more resources.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.