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:
- Extract the products from the table which have decreased their quantity from timestamp to timestamp
- 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?
CodePudding user response:
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.
CodePudding user response:
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