DB-Fiddle

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

Expected Output:

Query:

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;

With the above query I am able to calculate the balance of inbound_quantity and outbound_quantity in PostgresSQL.
However, when I run the same query in Amazon-Redshift I get this error:

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

How do I need to change the query to also make it work in Redshift?

CodePudding user response：

As the error speaks, you need to add the frame specification clause, namely the ROWS UNBOUNDED PRECEDING into your window function.

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