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');
Expected Result:
time_stamp | difference | info
------------|----------------|--------------------------------------
2020-01-14 | 400 | =(100+300)
2020-01-15 | 350 | =(400+350)-(300+100)
2020-01-16 | 800 | =(530+350+670)-(400+350)
2020-01-17 | 1080 | =(500+980+700+450)-(530+350+670)
I want to calculate the total difference of the quantity between two timestamps.
Therefore, I tried to use the query from this question:
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;
However, I am not able to get the expected result.
What do I need to change to make it work?
>Solution :
The partition by clause in the lag call of your query is redundant. The inner query is already grouped by the timestamp, so the outer query needn’t (and shouldn’t!) further partition the results.
With a partition by clause, each lag call is applied to just one timestamp (since they are already unique in the outer query), and you get the wrong result.
Remove it, and you should be fine:
SELECT
t1.time_stamp AS time_stamp,
t1.quantity AS quantity,
COALESCE(t1.quantity-LAG(t1.quantity) OVER (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;