Postgres 13

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 inbound (
    id SERIAL PRIMARY KEY,
    campaign VARCHAR,
    expected_inbound_date DATE,
    expected_inbound_quantity DECIMAL,
    received_inbound_quantity DECIMAL
);

INSERT INTO inbound
(campaign, expected_inbound_date, expected_inbound_quantity, received_inbound_quantity)
VALUES 
('C001', '2022-05-03', '500', '0'),
('C001', '2022-05-03', '800', '0'),
('C001', '2022-05-03', '400', '0'),
('C001', '2022-05-03', '200', '0'),
('C001', NULL, '0', '700'),

('C002', '2022-08-20', '3000', '0'),
('C002', '2022-08-20', '5000', '0'),
('C002', '2022-08-20', '2800', '0'),
('C002', NULL, '0', '4000');



CREATE TABLE



INSERT 0 9


SELECT
t1.campaign AS campaign,
t1.expected_inbound_date AS expected_inbound_date,
t1.expected_inbound_quantity AS expected_inbound_quantity,
t1.received_inbound_quantity AS received_inbound_quantity,
t1.quantity_accumulated AS quantity_accumulated,
t1.quantity_total AS quantity_total,


  (CASE WHEN (t1.quantity_total - t1.quantity_accumulated) >=0
  THEN t1.expected_inbound_quantity ELSE
  
    (CASE WHEN (t1.expected_inbound_quantity + t1.quantity_total - t1.quantity_accumulated) >=0
    THEN (t1.expected_inbound_quantity + t1.quantity_total - t1.quantity_accumulated)
    ELSE 0 END)
    
  END) AS split

FROM

  (SELECT
  i.campaign AS campaign,
  i.expected_inbound_date AS expected_inbound_date,
  i.expected_inbound_quantity AS expected_inbound_quantity,
  i.received_inbound_quantity AS received_inbound_quantity,
  SUM(i.expected_inbound_quantity) OVER (PARTITION BY i.campaign ORDER BY i.expected_inbound_date, i.expected_inbound_quantity ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS quantity_accumulated,
  MAX(i.received_inbound_quantity) OVER (PARTITION BY i.campaign) AS quantity_total
  FROM inbound i) t1
  
GROUP BY 1,2,3,4,5,6
ORDER BY 1,2,3,4,5,6; 



campaign
expected_inbound_date
expected_inbound_quantity
received_inbound_quantity
quantity_accumulated
quantity_total
split


C001
2022-05-03
200
0
200
700
200

C001
2022-05-03
400
0
600
700
400

C001
2022-05-03
500
0
1100
700
100

C001
2022-05-03
800
0
1900
700
0

C001
null
0
700
1900
700
0

C002
2022-08-20
2800
0
2800
4000
2800

C002
2022-08-20
3000
0
5800
4000
1200

C002
2022-08-20
5000
0
10800
4000
0

C002
null
0
4000
10800
4000
0



SELECT 9


SELECT
i.campaign AS campaign,
i.expected_inbound_date AS exptected_inbound_date,
i.expected_inbound_quantity AS expected_inbound_quantity,
i.received_inbound_quantity AS received_inbound_quantity,
SUM(i.expected_inbound_quantity) OVER (PARTITION BY i.campaign ORDER BY i.expected_inbound_date, i.expected_inbound_quantity) AS quantity_accumulated,
MAX(i.received_inbound_quantity) OVER (PARTITION BY i.campaign) AS quantity_total
FROM inbound i


campaign
exptected_inbound_date
expected_inbound_quantity
received_inbound_quantity
quantity_accumulated
quantity_total


C001
2022-05-03
200
0
200
700

C001
2022-05-03
400
0
600
700

C001
2022-05-03
500
0
1100
700

C001
2022-05-03
800
0
1900
700

C001
null
0
700
1900
700

C002
2022-08-20
2800
0
2800
4000

C002
2022-08-20
3000
0
5800
4000

C002
2022-08-20
5000
0
10800
4000

C002
null
0
4000
10800
4000



SELECT 9

campaign	expected_inbound_date	expected_inbound_quantity	received_inbound_quantity	quantity_accumulated	quantity_total	split
C001	2022-05-03	200	0	200	700	200
C001	2022-05-03	400	0	600	700	400
C001	2022-05-03	500	0	1100	700	100
C001	2022-05-03	800	0	1900	700	0
C001	null	0	700	1900	700	0
C002	2022-08-20	2800	0	2800	4000	2800
C002	2022-08-20	3000	0	5800	4000	1200
C002	2022-08-20	5000	0	10800	4000	0
C002	null	0	4000	10800	4000	0