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?
Advertisement
Answer
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;