I want to sum over 3 rows (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) using SQL ROLLING WINDOW:
The issue is that if try to use the statement ROWS BETWEEN 2 AND CURRENT ROW, it will provide SUM even if I have only ONE preceding row, and I would want to have result as NULL / 0 if there’s not 2 preceding and only one.
Is there a manner to have it like that?
Thank you for your time!
Advertisement
Answer
Consider below example that illustrate approach
with your_table as ( select 1 ts, 10 value union all select 2, 20 union all select 3, 30 union all select 4, 40 ) select *, if(count(value) over win = 3, sum(value) over win, null) result from your_table window win as (order by ts rows between 2 preceding and current row)
with output
In case if you want at least one row behind – you would use
select *, if(count(value) over win > 1, sum(value) over win, null) result from your_table window win as (order by ts rows between 2 preceding and current row)
with output