在Redshift中计算各产品库存余额的SQL查询修正

数据表结构与测试数据 #

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');

预期结果 #

问题描述 #

需要计算每个产品的库存余额，编写的查询语句报错window "product" does not exist，错误SQL如下：

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 
(product ORDER BY stock_date ASC ROWS UNBOUNDED PRECEDING) AS Balance
FROM inventory iv
GROUP BY 1,2,3,4
ORDER BY 2,1;

修正方案 #

错误原因 #

1. 窗口函数分区语法错误：按产品分区必须使用PARTITION BY product语法，直接写product不符合SQL规范，这是报错的核心原因。
2. 多余的GROUP BY：窗口函数会逐行计算累计值，不需要通过GROUP BY聚合，保留该子句会破坏原始数据的行结构。

修正后的SQL #

SELECT 
    stock_date AS 库存日期,
    product AS 产品,
    inbound_quantity AS 入库数量,
    outbound_quantity AS 出库数量,
    SUM(inbound_quantity - outbound_quantity) OVER (
        PARTITION BY product 
        ORDER BY stock_date ASC 
        ROWS UNBOUNDED PRECEDING
    ) AS 库存余额
FROM inventory
ORDER BY product, stock_date;

说明 #

• PARTITION BY product：将数据按产品分组，确保每个产品的库存余额独立计算，互不干扰。
• ORDER BY stock_date ASC：按日期顺序排序，保证余额计算遵循时间先后逻辑。
• ROWS UNBOUNDED PRECEDING：指定累计范围为当前行及之前的所有行，实现从初始日期开始的连续累计计算。

内容的提问来源于stack exchange，提问作者Michi