I want to implement below logic in Presto SQL:
DECLARE @StartDate DATE = '2016-10-01' ; DECLARE @EndDate DATE = '2021-05-02' ;
WHILE (@StartDate <= @EndDate)
BEGIN
print @StartDate;
DECLARE @12MonthStartDate date
select @12MonthStartDate = (SELECT DateAdd(YEAR, -1, @StartDate));
DECLARE @12MonthEndDate date
select @12MonthEndDate = @StartDate ;
select * from MyTable where Date >= @12MonthStartDate and Date <= @12MonthStartDate
Based on above results, we need to do the rolling 12 months and insert the max date values into the table.
Note: we are using Presto SQL in Amperity tool.
set @StartDate = DATEADD(day, 1, @StartDate);
END;
Can any one please help with how to implement above logic in Presto SQL?
Thanks
You can compute a rolling sum with a window function:
SELECT dt,
sum(amount) OVER (
ORDER BY dt
RANGE BETWEEN INTERVAL '12' MONTH PRECEDING AND CURRENT ROW)
FROM my_table
For every row in my_table, it looks at all the rows ordered by dt
(ascending). Then it restricts the windows to rows whose dt
is between 12 months before the dt
of the current row and the dt
of the row. Finally, it computes the sum of amount
for all the rows in that window.
See this blog post for an explanation of how RANGE window frames work.