Cumulative sum() every 3 days SQL-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Disclaimer
The following solution was written based on a Preview version of SQL Server 2022, and thus may not reflect the final release.

For a bit of fun, if you had access to SQL Server 2022 (which went into preview yesterday) you could use DATE_BUCKET to "round" the date in the PARTITION BY to 3 days, using the minimum date as the starting date.

DECLARE @StartDate date,
        @EndDate date;

SELECT @StartDate = MIN(date),
       @EndDate = MAX(date)
FROM dbo.YourTable;

SELECT date,
       SUM(amount) OVER (PARTITION BY DATE_BUCKET(DAY,3,date,@StartDate) ORDER BY date) AS Amount
FROM dbo.YourTable
WHERE date >= @StartDate
  AND date <= @EndDate; --Incase this would be parametrised

Image of results as expected, as Fiddles of 2022 don't exist: