I have a table with following data: computed column : current
| id | Date (dd/mm/yyyy) | Factor | Actual | Current |
|----|-------------------|--------|--------|----------|
| 1 | 04/01/2017 | 0.5 | 100 | 100 |
| 2 | 04/02/2017 | 0.5 | 120 | 100 |
| 3 | 04/03/2017 | 0.5 | 120 | 110 |
| 4 | 04/04/2017 | 0.5 | 115 | 115 |
| 5 | 04/05/2017 | 0.5 | 125 | 115 |
| 6 | 04/06/2017 | 0.5 | 100 | 120 |
| 7 | 04/07/2017 | 0.5 | 100 | 110 |
Current row = current of previous row + factor * (actual of previous row - current of previous row)
For id = 1, current = same as actual = 100
For id = 2, current = 100 + 0.5 * (100 - 100) = 100
For id = 3, current = 100 + 0.5 * (120 - 100) = 110
For id = 4, current = 110 + 0.5 * (120 - 110) = 115
and so on...
How to achieve in postgresql using query?
You need a recursive query.
with recursive my_table_with_rn as
(
select *, row_number() over (order by id) as rn
from my_table
),
rec_query(rn, id, date, factor, actual, current) as
(
select rn, id, date, factor, actual, actual
from my_table_with_rn
where rn = 1
union all
select
t.rn, t.id, t.date, t.factor, t.actual,
p.current + t.factor * (p.actual - p.current)
from rec_query p
join my_table_with_rn t on t.rn = p.rn + 1
)
select id, date, factor, actual, current
from rec_query
order by id;
Note, that row_number() was added to work in cases when ids are not consecutive (it's not necessary for actual data, you can use id instead of rn).