By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table MyTable
( block_date date
, address char(1)
, amount decimal(5,2)
, price decimal(5,2)
);
insert into MyTable values
(date '2023-01-01', 'A', 5, 10),
(date '2023-01-02', 'A', -5, 20),
(date '2023-01-03', 'A', 8, 30),
(date '2023-01-04', 'A', 2, 40),
(date '2023-01-05', 'A', -2, 50),
(date '2023-01-06', 'A', 7, 60),
(date '2023-01-07', 'A', -4, 70),
(date '2023-01-08', 'A', 6, 80),
(date '2023-01-09', 'A', 9, 90);
CREATE TABLE
INSERT 0 9
with recursive cte_recurs (block_date, address, balance, amount, price, avg_acq_price) as
(
select block_date, address, amount, amount, price, price
from MyTable
where block_date = date '2023-01-01'
union all
select t.block_date
, c.address
, (c.balance + t.amount)::decimal(5,2)
, t.amount
, t.price
, case
when t.amount < 0
then c.avg_acq_price
else coalesce((c.balance * c.avg_acq_price + t.amount * t.price)
/ nullif(c.balance + t.amount, 0), 0)::decimal(5,2)
end
from MyTable as t
join cte_recurs as c on c.address = t.address
and c.block_date = t.block_date - 1
)
select block_date, address, balance, amount, price, avg_acq_price
from cte_recurs;
| block_date | address | balance | amount | price | avg_acq_price |
|---|---|---|---|---|---|
| 2023-01-01 | A | 5.00 | 5.00 | 10.00 | 10.00 |
| 2023-01-02 | A | 0.00 | -5.00 | 20.00 | 10.00 |
| 2023-01-03 | A | 8.00 | 8.00 | 30.00 | 30.00 |
| 2023-01-04 | A | 10.00 | 2.00 | 40.00 | 32.00 |
| 2023-01-05 | A | 8.00 | -2.00 | 50.00 | 32.00 |
| 2023-01-06 | A | 15.00 | 7.00 | 60.00 | 45.07 |
| 2023-01-07 | A | 11.00 | -4.00 | 70.00 | 45.07 |
| 2023-01-08 | A | 17.00 | 6.00 | 80.00 | 57.40 |
| 2023-01-09 | A | 26.00 | 9.00 | 90.00 | 68.68 |
SELECT 9