Is there a way to restrinct rolling window from summing if not 2 behind?

December 18, 2021

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!

>Solution :

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