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 cars(car_id integer not null primary key, km_driven integer);
insert into cars values (2,430),(3,112),(4,90),(5,201);
create table pricing(start int not null primary key, finish int, price numeric(2,1));
insert into pricing values(1,100,2),(101,200,1),(201,null,0.5);
4 rows affected
3 rows affected
select * from cars;
| car_id | km_driven |
|---|---|
| 2 | 430 |
| 3 | 112 |
| 4 | 90 |
| 5 | 201 |
select * from pricing;
| start | finish | price |
|---|---|---|
| 1 | 100 | 2.0 |
| 101 | 200 | 1.0 |
| 201 | null | 0.5 |
select
car_id, km_driven,
sum(case
when km_driven>=start then (least(finish,km_driven)-start+1)*price
else 0
end) as dist_price
from cars,pricing
group by car_id,km_driven
| car_id | km_driven | dist_price |
|---|---|---|
| 2 | 430 | 415.0 |
| 3 | 112 | 212.0 |
| 4 | 90 | 180.0 |
| 5 | 201 | 300.5 |
select
car_id, km_driven,
sum (case
when finish is null and km_driven >= start
then (km_driven-start+1) * price
when km_driven >= start
then (case
when (km_driven - start + 1) > finish
then (finish - start + 1)
else (km_driven - start + 1)
end) * price
else 0
end) as dist_price
from cars, pricing
where km_driven >= start
group by 1, 2;
| car_id | km_driven | dist_price |
|---|---|---|
| 2 | 430 | 415.0 |
| 3 | 112 | 212.0 |
| 4 | 90 | 180.0 |
| 5 | 201 | 301.5 |
select
car_id, km_driven,
sum((least(finish,km_driven)-start+1)*price) as dist_price
from cars,pricing
where km_driven >= start
group by car_id,km_driven
| car_id | km_driven | dist_price |
|---|---|---|
| 2 | 430 | 415.0 |
| 3 | 112 | 212.0 |
| 4 | 90 | 180.0 |
| 5 | 201 | 300.5 |