Select values that have a minus difference between two timestamps and show their complete history in the table

user_4934150 0
Viewed 45

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', '250'),
('2020-01-16', 'Product_D', '670'),

('2020-01-17', 'Product_C', '380'),
('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-15  |     Product_C   |        350      |
2020-01-16  |     Product_C   |       -100      |
2020-01-17  |     Product_C   |        130      |

I want to do the following two things:

  1. Extract the products from the table which have decreased their quantity from timestamp to timestamp
  2. Display the history of those products over all timestamps.

With the below query I am able to do Step 1 but I am wondering how I need to modify it to also include the history of the selected products.

SELECT
t1.time_stamp AS time_stamp,
t1.product AS product,
SUM(t1.difference) AS difference
FROM

  (SELECT
  l.time_stamp AS time_stamp, 
  l.product AS product,
  Coalesce(l.quantity-LAG(l.quantity) OVER (Partition by l.product ORDER BY l.product, l.time_stamp), l.quantity) AS difference
  FROM logistics l
  ORDER BY 1,2) t1
  
WHERE t1.difference < 0
GROUP BY 1,2
ORDER BY 1,2;

Do you have any idea?

window-functions exists postgresql sql
3 Answers

You can use a MAX OVER to calculate a flag per product.
Then filter on the flag.

SELECT q2.time_stamp, q2.product, q2.difference
FROM (
  SELECT q1.*
  , MAX(CASE WHEN q1.quantity < q1.prev_quantity THEN 1 ELSE 0 END) 
        OVER (PARTITION BY q1.product) AS has_difference
  , (q1.quantity - coalesce(q1.prev_quantity, 0)) AS difference
  FROM (
    SELECT l.product, l.time_stamp, l.quantity
        , LAG(l.quantity) OVER (PARTITION BY l.product ORDER BY l.time_stamp) AS prev_quantity
        FROM logistics l
  ) AS q1
) q2
WHERE q2.has_difference = 1
ORDER BY q2.product, q2.time_stamp;

time_stamp | product   | difference
:--------- | :-------- | ---------:
2020-01-15 | Product_C |        350
2020-01-16 | Product_C |       -100
2020-01-17 | Product_C |        130

db<>fiddle here

user_40034190

Use EXISTS:

WITH cte AS (
  SELECT time_stamp, product, 
         quantity - LAG(quantity, 1, 0) OVER (PARTITION BY product ORDER BY time_stamp) difference    
  FROM logistics
)
SELECT c1.*
FROM cte c1
WHERE EXISTS (
  SELECT 1 
  FROM cte c2
  WHERE c2.product = c1.product AND c2.difference < 0
)
ORDER BY c1.product, c1.time_stamp;

See the demo.

user_104988280

DB-Fiddle

WITH cte AS
    (SELECT
    l.time_stamp AS time_stamp, 
    l.product AS product,
    Coalesce(l.quantity-LAG(l.quantity) OVER (Partition by l.product ORDER BY l.product, l.time_stamp), l.quantity) AS difference
    FROM logistics l
    ORDER BY 1,2)


SELECT
t1.time_stamp AS time_stamp,
t1.product AS product,
SUM(t1.difference) AS difference
FROM cte t1

WHERE EXISTS

   (SELECT
    t2.product AS product
    FROM cte t2
    WHERE t2.difference < 0
    AND t2.product = t1.product
    GROUP BY 1
    ORDER BY 1)

GROUP BY 1,2
ORDER BY 1,2;
user_49341500
Related