如何在PostgreSQL中根据计算列的前一个值计算其值

-- 创建一个示例表
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    date DATE,
    amount NUMERIC
);

-- 插入一些示例数据
INSERT INTO sales (date, amount) VALUES
    ('2022-01-01', 100),
    ('2022-01-02', NULL),
    ('2022-01-03', 150),
    ('2022-01-04', NULL),
    ('2022-01-05', 200);

-- 使用LAG函数计算计算列的前一个值
SELECT
    date,
    amount,
    LAG(amount) OVER (ORDER BY date) AS previous_amount,
    CASE
        WHEN amount IS NULL THEN previous_amount
        ELSE amount
    END AS calculated_amount
FROM
    sales;