计算红移中每个产品的平衡量

Question

DB Fiddle

CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
stock_date DATE,
product VARCHAR(255),
inbound_quantity INT,
outbound_quantity INT
);
INSERT INTO inventory
(stock_date, product, inbound_quantity, outbound_quantity
)
VALUES 
('2020-01-01', 'Product_A', '900', '0'),
('2020-01-02', 'Product_A', '0', '300'),
('2020-01-03', 'Product_A', '400', '250'),
('2020-01-04', 'Product_A', '0', '100'),
('2020-01-05', 'Product_A', '700', '500'),

('2020-01-03', 'Product_B', '850', '0'),
('2020-01-08', 'Product_B', '100', '120'),
('2020-02-20', 'Product_B', '0', '360'),
('2020-02-25', 'Product_B', '410', '230'),

预期结果：

余额0>750>>>290

	库存日期		产品	边界内数量	边界外数量
2020-01-01	产品_A	900	900
22020-01-02	产品_A	0	300	600
22020-01-03	产品_A	400	250
22020-01-04	产品_A	0	100	650
22020-01-05	产品_A	700	500	850
22020-01-03	产品_B	740	0	740
22020-01-08	产品_B	100	120	720
2020-020	产品_B	0	360
2020-025	产品_B	410	230	540
2020-03-09	产品_B	0	830

您已接近

您应该在产品前面添加分区

SELECT 
iv.stock_date AS stock_date,
iv.product AS product,
iv.inbound_quantity AS inbound_quantity,
iv.outbound_quantity AS outbound_quantity,
SUM(iv.inbound_quantity - iv.outbound_quantity) OVER 
(partition by product ORDER BY stock_date ASC ROWS UNBOUNDED PRECEDING) AS Balance
FROM inventory iv
GROUP BY 1,2,3,4
ORDER BY 2,1;

所以，它应该像这个

错误消息"产品"；不存在是因为您正试图引用列"；产品"；OVER子句中，但它不包含在GROUP BY子句中。

要解决这个问题，您需要将"；产品"；列，并在OVER子句中添加partitionby子句，以便SUM函数计算每个产品的余额。

尝试此查询：

SELECT 
iv.stock_date AS stock_date,
iv.product AS product,
iv.inbound_quantity AS inbound_quantity,
iv.outbound_quantity AS outbound_quantity,
SUM(iv.inbound_quantity - iv.outbound_quantity) OVER 
(PARTITION BY product ORDER BY stock_date ASC ROWS UNBOUNDED PRECEDING) AS Balance
FROM inventory iv
GROUP BY 1,2,3,4
ORDER BY 2,1;

这样，SUM函数将只对每个产品的inbound_quantity-outbound_quantuity求和，而不是对所有产品求和。

查询将返回预期结果。