Calculate total difference of values between two timestamps-CodePudding

DB-Fiddle

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?

CodePudding user response：

You can remove the PARTITION BY in the LAG function.

If the "product" column was part of the result. Then PARTITION BY product would make sense.
But since that's not the case, the ORDER BY t1.time_stamp is sufficient.

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;

time_stamp | quantity | difference
:--------- | -------: | ---------:
2020-01-14 |      400 |        400
2020-01-15 |      750 |        350
2020-01-16 |     1550 |        800
2020-01-17 |     2630 |       1080

db<>fiddle here

CodePudding user response：

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;

DBFiddle demo

CodePudding user response：

you can use this :

select time_stamp
      ,s-COALESCE(LAG(t1.s) OVER  (ORDER BY t1.time_stamp),0)  as difference    
from 
 (select time_stamp,sum(quantity) as s 
       from logistics 
       group by time_stamp)t1

Output:

time_stamp  difference    
2020-01-14  400
2020-01-15  350
2020-01-16  800
2020-01-17  1080

CodePudding user response：

you can also use a with clause, first calculate the sum, then its easier to calculate the lag:

with _sum as (
SELECT distinct
logistics.time_stamp AS time_stamp,
sum(quantity) over (PARTITION by time_stamp) as summe
from logistics
order by time_stamp
)
select 
time_stamp,
summe - coalesce(lag(summe) over (order by time_stamp),0)
from _sum