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 transactions (
timestamp int not null,
customer varchar(30) not null,
item varchar(30) not null,
volume int not null
);
create index idx_transactions_timestamp
on transactions (timestamp, item);
insert into transactions
(timestamp, customer, item, volume) values
(2000, 'Joe', 'A', 100)
, (2001, 'Joe', 'A', 200)
, (2001, 'Doe', 'A', 100)
3 rows affected
create table valuations (
timestamp int not null,
item varchar(30) not null,
price decimal(10,1) not null
);
create index idx_valuations_timestamp
on valuations (timestamp, item);
insert into valuations
(timestamp, item, price) values
(2000, 'A', 1.1)
, (2001, 'A', 1.2)
, (2002, 'A', 1.3)
3 rows affected
create temporary table tmp_customer_valuations (
timestamp int not null,
item varchar(30) not null,
customer varchar(30) not null,
price decimal(10,1) not null
);
insert into tmp_customer_valuations
(timestamp, item, price, customer)
select v.timestamp, v.item, v.price, c.customer
from valuations v
join (
select item, customer, min(timestamp) as min_timestamp
from transactions
group by item, customer
) c
on c.item = v.item
and c.min_timestamp <= v.timestamp
5 rows affected
create index idx_tmp_customer_valuations
on tmp_customer_valuations (timestamp, item)
select
tmp.timestamp
, tmp.customer
, tmp.item
--, tr.volume as order_volume,
, sum(coalesce(tr.volume, 0))
over (partition by tmp.item, tmp.customer
order by tmp.timestamp) as stock
, tmp.price * sum(coalesce(tr.volume, 0))
over (partition by tmp.item, tmp.customer
order by tmp.timestamp) as value
from tmp_customer_valuations tmp
left join transactions tr
on tr.timestamp = tmp.timestamp
and tr.item = tmp.item
and tr.customer = tmp.customer
order by
tmp.customer desc,
tmp.item,
tmp.timestamp;
| timestamp | customer | item | stock | value |
|---|---|---|---|---|
| 2000 | Joe | A | 100 | 110.0 |
| 2001 | Joe | A | 300 | 360.0 |
| 2002 | Joe | A | 300 | 390.0 |
| 2001 | Doe | A | 100 | 120.0 |
| 2002 | Doe | A | 100 | 130.0 |