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 logistics (
id SERIAL PRIMARY KEY,
time_stamp DATE,
product VARCHAR(255),
quantity INT
);
INSERT INTO logistics
(time_stamp, product, quantity)
VALUES
('2020-01-14', 'Product_A', '100'),
('2020-01-14', 'Product_B', '300'),
('2020-01-15', 'Product_B', '400'),
('2020-01-15', 'Product_C', '350'),
('2020-01-16', 'Product_B', '530'),
('2020-01-16', 'Product_C', '350'),
('2020-01-16', 'Product_D', '670'),
('2020-01-17', 'Product_C', '500'),
('2020-01-17', 'Product_D', '980'),
('2020-01-17', 'Product_E', '700'),
('2020-01-17', 'Product_F', '450');
11 rows affected
SELECT
t1.time_stamp AS time_stamp,
t1.quantity AS quantity,
COALESCE(t1.quantity-LAG(t1.quantity) OVER (PARTITION BY t1.time_stamp ORDER BY t1.time_stamp), t1.quantity) AS difference
FROM
(SELECT
l.time_stamp AS time_stamp,
SUM(l.quantity) AS quantity
FROM logistics l
GROUP BY 1
ORDER BY 1) t1
GROUP BY 1,2
ORDER BY 1,2;
| time_stamp | quantity | difference |
|---|---|---|
| 2020-01-14 | 400 | 400 |
| 2020-01-15 | 750 | 750 |
| 2020-01-16 | 1550 | 1550 |
| 2020-01-17 | 2630 | 2630 |