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 customer (
CustID VARCHAR(512),
OrderDate date,
OrderType VARCHAR(50),
Orig_Value INT
);
INSERT INTO customer (CustID, OrderDate, OrderType, Orig_Value) VALUES
('A', '1/1/2025', 'Bulk', '10')
,('A', '1/2/2025', 'Individual', '20')
,('B', '1/3/2025', 'Bulk', '30')
,('B', '1/3/2025', 'Individual', '10')
,('C', '1/4/2025', 'Bulk', '0')
,('C', '1/4/2025', 'Individual', '5')
,('C', '1/4/2025', 'Other', '8')
,('D', '2/4/2025', 'Other', '8')
,('D', '2/4/2025', 'Bulk', '0')
;
select * from customer;
CREATE TABLE
INSERT 0 9
| custid | orderdate | ordertype | orig_value |
|---|---|---|---|
| A | 2025-01-01 | Bulk | 10 |
| A | 2025-02-01 | Individual | 20 |
| B | 2025-03-01 | Bulk | 30 |
| B | 2025-03-01 | Individual | 10 |
| C | 2025-04-01 | Bulk | 0 |
| C | 2025-04-01 | Individual | 5 |
| C | 2025-04-01 | Other | 8 |
| D | 2025-04-02 | Other | 8 |
| D | 2025-04-02 | Bulk | 0 |
SELECT 9
select custid,orderdate,ordertype,orig_value
,coalesce(Bulk_orig_Value,Individual_Orig_value,Other_Orig_Value) Adj_Value
, bulk_orig_value, individual_orig_value, other_orig_value
from(
select *
,max(case when OrderType='Bulk' then nullif(Orig_Value,0) end)
over(partition by CustID, OrderDate) Bulk_Orig_Value
,max(case when OrderType='Individual' then nullif(Orig_Value,0) end)
over(partition by CustID, OrderDate) Individual_Orig_Value
,max(case when OrderType not in('Bulk','Individual') then nullif(Orig_Value,0) end)
over(partition by CustID, OrderDate) Other_Orig_Value
from customer
)a
| custid | orderdate | ordertype | orig_value | adj_value | bulk_orig_value | individual_orig_value | other_orig_value |
|---|---|---|---|---|---|---|---|
| A | 2025-01-01 | Bulk | 10 | 10 | 10 | null | null |
| A | 2025-02-01 | Individual | 20 | 20 | null | 20 | null |
| B | 2025-03-01 | Bulk | 30 | 30 | 30 | 10 | null |
| B | 2025-03-01 | Individual | 10 | 30 | 30 | 10 | null |
| C | 2025-04-01 | Bulk | 0 | 5 | null | 5 | 8 |
| C | 2025-04-01 | Individual | 5 | 5 | null | 5 | 8 |
| C | 2025-04-01 | Other | 8 | 5 | null | 5 | 8 |
| D | 2025-04-02 | Other | 8 | 8 | null | null | 8 |
| D | 2025-04-02 | Bulk | 0 | 8 | null | null | 8 |
SELECT 9