PSQL Recursive Adding Query-CodePudding

I have a table called "deaths" with two columns. One is a date, and the second is the amount of people who died on that specific date. I need a query that gives me the total amount of people who died between that date and 90 days prior. For example, if row value of the date is 30/09/2021, I would need to add the deaths since 02/07/2021. ¿Can I get any guidance as to how can I do this? "deaths" Table example below.

    Date    | Deaths | 
------------ -------- 
 2021-08-19 |     21 |
 2021-08-18 |     96 |
 2021-08-17 |    100 |
 2021-08-16 |     64 |
 2021-08-15 |    107 |
 2021-08-14 |     93 |

So, if this was all my data, the first row (2021-08-19) of my result should be (21 96 100 64 107 93).

Hope I was clear enough.

CodePudding user response：

You don't need a recursive query for this, you can use window functions instead. As others have mentioned, "date" is not a good name for a column and it would have been better to give sample data with dates more than 90 days apart, but I believe this query should work for you:

SELECT "date",
       deaths,
       sum(deaths) OVER (ORDER BY "date" RANGE BETWEEN interval '90 days' preceding and current row)
FROM deaths;

The clause RANGE BETWEEN interval '90 days' preceding and current row, called the frame, limits the rows that will be part of the sum.

CodePudding user response：

Use

Select distinct "date" , (Select sum(deaths) from table where 
 "date" <=d."date" and "date" >=d."date" -90)
   as tot_deaths from tabl3 d;