sqlwindow-functionsdatabase-agnostic

Cumulative sum() every 3 days SQL

I have a table like this

date       amount
2020-02-01    5 
2020-02-02    2 
2020-02-03    10 
2020-02-04    2  
2020-02-06    3 
2020-02-07    1

And I need sum() every 3 days as below:

date       amount   sum
2020-02-01    5      5
2020-02-02    2      7
2020-02-03    10     17
2020-02-04    2      2
2020-02-06    3      5
2020-02-07    1      1
...

So when a difference between days is 3, the summation should start over. Some days may not be in the table.

I tried to do this with window function like sum(amount) over (order by date) but I have no idea how to set a fixed number of days and get the date difference in cumulative sum like this. Is it possible in any SQL?

Solution

  • In MS Sql Server

    select t.[date], t.Amount, sum(t.Amount) over(partition by datediff(d, '2020-02-01', t.[date])/3 order by t.[date]) cum
from tbl t

    '2020-02-01' is a starting date you want.