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  |      product    |   difference    |
------------|-----------------|-----------------|---------
2020-01-14  |     Product_A   |        100      |  
2020-01-14  |     Product_B   |        300      |  
------------|-----------------|-----------------|---------
2020-01-15  |     Product_B   |        100      |
2020-01-15  |     Product_C   |        350      |
------------|-----------------|-----------------|---------
2020-01-16  |     Product_B   |        130      |  
2020-01-16  |     Product_C   |          0      |
2020-01-16  |     Product_D   |        670      |
------------|-----------------|-----------------|--------
2020-01-17  |     Product_C   |        150      |
2020-01-17  |     Product_D   |        310      |
2020-01-17  |     Product_E   |        700      |
2020-01-17  |     Product_F   |        450      |

I want to calculate the difference in the quantity for each product from timestamp to timestamp.

I tried something like this:

SELECT
t2.time_stamp AS time_stamp, 
t2.product AS product,
SUM(t2.difference) AS difference

FROM
 
  (SELECT
  t1.time_stamp AS time_stamp, 
  t1.product AS product,
  t1.quantity AS quantity,
  t1.quantity-LAG(t1.quantity) OVER (ORDER BY t1.time_stamp) AS difference
  FROM
 
      (SELECT
      l.time_stamp AS time_stamp,
      l.product AS product,
      SUM(l.quantity) AS quantity
      FROM logistics l
      GROUP BY 1,2
      ORDER BY 1,2) t1
  
  GROUP BY 1,2,3
  ORDER BY 1,2,3) t2
 
GROUP BY 1,2
ORDER BY 1,2;

However, I am not getting the expected results with this query.
Do you have any idea how I need to change it to make it work?

CodePudding user response：

It is a simple query with "lag" and "coalesce".

SELECT
  time_stamp AS time_stamp, 
  product AS product,
  Coalesce(quantity-LAG(quantity) OVER (Partition by product ORDER BY product, time_stamp), quantity) AS difference
  FROM logistics
Order by time_stamp, product

CodePudding user response：

That should be simple with a window function:

SELECT time_stamp,
       product,
       quantity -
       coalesce(
          lag(quantity) OVER (PARTITION BY product ORDER BY time_stamp),
          0
       ) AS difference
FROM logistics
ORDER BY time_stamp, product;

 time_stamp │  product  │ difference 
════════════╪═══════════╪════════════
 2020-01-14 │ Product_A │        100
 2020-01-14 │ Product_B │        300
 2020-01-15 │ Product_B │        100
 2020-01-15 │ Product_C │        350
 2020-01-16 │ Product_B │        130
 2020-01-16 │ Product_C │          0
 2020-01-16 │ Product_D │        670
 2020-01-17 │ Product_C │        150
 2020-01-17 │ Product_D │        310
 2020-01-17 │ Product_E │        700
 2020-01-17 │ Product_F │        450
(11 rows)