如何修改PostgreSQL库存余额查询以适配Amazon Redshift？

建表与数据插入语句 #

CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    stock_date DATE,
    inbound_quantity INT,
    outbound_quantity INT
);

INSERT INTO inventory
(stock_date, inbound_quantity, outbound_quantity)
VALUES 
('2020-01-01', '900', '0'),
('2020-01-02', '0', '300'),
('2020-01-03', '400', '250'),
('2020-01-04', '0', '100'),
('2020-01-05', '700', '500');

预期输出 #

PostgreSQL兼容的查询语句 #

以下查询可在PostgreSQL中正确计算库存余额：

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

Redshift运行报错 #

将上述语句在Amazon Redshift中执行时，会抛出以下错误：

Amazon Invalid operation: Aggregate window functions with an ORDER BY clause require a frame clause;
1 statement failed.

修改后的Redshift兼容语句 #

Redshift要求带ORDER BY的聚合窗口函数必须显式指定帧子句，而PostgreSQL会默认使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW的帧范围。我们只需补充该帧子句即可：

方案1：使用ROWS帧范围 #

SELECT
iv.stock_date AS stock_date,
iv.inbound_quantity AS inbound_quantity,
iv.outbound_quantity AS outbound_quantity,
SUM(iv.inbound_quantity - iv.outbound_quantity) OVER (
    ORDER BY stock_date ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Balance
FROM inventory iv
GROUP BY 1,2,3
ORDER BY 1;

方案2：使用RANGE帧范围 #

如果stock_date字段唯一（每条记录对应一个日期），也可以使用RANGE替代ROWS，效果一致：

SELECT
iv.stock_date AS stock_date,
iv.inbound_quantity AS inbound_quantity,
iv.outbound_quantity AS outbound_quantity,
SUM(iv.inbound_quantity - iv.outbound_quantity) OVER (
    ORDER BY stock_date ASC
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Balance
FROM inventory iv
GROUP BY 1,2,3
ORDER BY 1;

说明 #

Redshift对窗口函数的语法约束比PostgreSQL更严格，当窗口函数中包含ORDER BY时，必须明确指定计算的帧范围，不能依赖默认行为。这里指定从第一条记录到当前行的范围，即可实现累计计算库存余额的需求。

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