Updating a table using values in previous rows and then using the outputted value in the next calculation

Month	Value	a	b
June	400	50	20
July	NULL	25	10
August	NULL	50	20

I want to forecast future months using existing values.

I need to update the NULL values for July using the calculation 400 + 50 – 20 and obtain the value 430. I then need to use July’s value of 430 in a calculation (430 + 25 – 10) which should return a value of 445 for August.

How can I used postgreSQL to fill these the value column for each month given columns a and b and an initial value?

Answer

If you want a query, you can use:

select t.*,
       ( sum(value) over () +
         sum(a + b) over (order by month) - (a + b)
       ) as imputed_value
from t;

Note: This assumes that month is really stored in some manner that preserves the date — such as the first day of the month. If it is a string, you can use:

select t.*,
       ( sum(value) over () +
         sum(a + b) over (order by to_date(month, 'Mon')) - (a + b)
       ) as imputed_value
from t;