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 tbl (
date date
, PR int
, amount numeric
, total numeric
, balance numeric
, expected_value text
, PRIMARY KEY(pr, date)
);
INSERT INTO tbl
VALUES
('2020-1-1', 1, 1.0, 100.0, 1.0, '100'),
('2020-1-2', 1, 2.0, 220.0, 3.0, '320'),
('2020-1-2', 2, 3.0, 270.0, 4.5, '430'), -- different pr!
('2020-1-3', 1, -1.5, -172.5, 1.5, '160'),
('2020-1-4', 1, 3.0, 270.0, 4.5, '430'),
('2020-1-4', 2, 3.0, 270.0, 4.5, '430'), -- different pr!
('2020-1-5', 1, 1.0, 85.0, 5.5, '515'),
('2020-1-6', 1, 2.0, 202.0, 7.5, '717'),
('2020-1-7', 1, -4.0, -463.0, 3.5, '334.6'),
('2020-1-8', 1, -0.5, - 55.0, 3.0, null),
('2020-1-9', 1, 2.0, 214.0, 5.0, null);
11 rows affected
CREATE OR REPLACE FUNCTION f_special_running_sum (_state numeric, _total numeric, _amount numeric, _prev_balance numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE AS
'SELECT CASE WHEN _amount > 0 THEN _state + _total
ELSE _state * (1 + _amount / _prev_balance) END';
CREATE OR REPLACE AGGREGATE special_running_sum (_total numeric, _amount numeric, _prev_balance numeric) (
sfunc = f_special_running_sum
, stype = numeric
, initcond = '0'
);
-- query
SELECT *
, special_running_sum(total, amount, prev_balance) OVER (PARTITION BY pr ORDER BY date)
FROM (
SELECT * -- pr, date, amount, total
, lag(balance, 1, '1') OVER (PARTITION BY pr ORDER BY date) AS prev_balance
FROM tbl
) t
| date | pr | amount | total | balance | expected_value | prev_balance | special_running_sum |
|---|---|---|---|---|---|---|---|
| 2020-01-01 | 1 | 1.0 | 100.0 | 1.0 | 100 | 1 | 100.0 |
| 2020-01-02 | 1 | 2.0 | 220.0 | 3.0 | 320 | 1.0 | 320.0 |
| 2020-01-03 | 1 | -1.5 | -172.5 | 1.5 | 160 | 3.0 | 160.000000000000000000000 |
| 2020-01-04 | 1 | 3.0 | 270.0 | 4.5 | 430 | 1.5 | 430.000000000000000000000 |
| 2020-01-05 | 1 | 1.0 | 85.0 | 5.5 | 515 | 4.5 | 515.000000000000000000000 |
| 2020-01-06 | 1 | 2.0 | 202.0 | 7.5 | 717 | 5.5 | 717.000000000000000000000 |
| 2020-01-07 | 1 | -4.0 | -463.0 | 3.5 | 334.6 | 7.5 | 334.60000000000000000239000000000000000000000 |
| 2020-01-08 | 1 | -0.5 | -55.0 | 3.0 | null | 3.5 | 286.8000000000000000030045714285714285714354000000000000000000000 |
| 2020-01-09 | 1 | 2.0 | 214.0 | 5.0 | null | 3.0 | 500.8000000000000000030045714285714285714354000000000000000000000 |
| 2020-01-02 | 2 | 3.0 | 270.0 | 4.5 | 430 | 1 | 270.0 |
| 2020-01-04 | 2 | 3.0 | 270.0 | 4.5 | 430 | 4.5 | 540.0 |