如何在SQL Developer中实现递归计算amount2列？

我有一个表包含列：ID, date, amount, amount2，其中amount2仅第一行有值，需要按以下规则递归计算每一行的amount2：

• 第二行amount2 = 第二行amount + 第一行amount - 第一行amount2
• 第三行amount2 = 第三行amount + 第二行amount - 第二行amount2
• 以此类推...

尝试了以下代码但无法正常运行：

SELECT   
    first_column,  
    CASE   
        WHEN second_column IS NULL THEN LAG(first_column, 1, first_column) OVER (ORDER BY row_id) - LAG(first_column, 1, first_column) OVER (ORDER BY row_id DESC)  
        ELSE second_column  
    END AS calculated_second_column
from table;

可复现示例 #

CREATE TABLE your_table_name (  
    id INT,  
    date DATE,  
    amount DECIMAL(10,2),  
    amount1 DECIMAL(10,2)  
); 
INSERT INTO your_table_name (id, date, amount, amount1)  
VALUES  
    (234, '2020-01-01', 4, 10),  
    (234, '2020-01-02', 7, NULL),  
    (234, '2020-01-03', 10, NULL),  
    (234, '2020-01-04', 15, NULL),  
    (234, '2020-01-05', 6, NULL);  

预期amount1列的结果为：10, 1（7+4-10）, 16（10+7-1）, 9（15+10-16）, 12（6+15-9）

解决方案 #

要实现这种依赖前一行计算结果的递归逻辑，普通窗口函数（比如LAG()）无法满足需求，因为它只能读取表中已有的原始数据，无法引用当前查询中刚计算出的值。这里需要用递归CTE来逐行计算：

WITH RECURSIVE cte AS (
    -- 锚点：获取第一行的初始数据
    SELECT 
        id,
        date,
        amount,
        amount1,
        ROW_NUMBER() OVER (ORDER BY date) AS row_num
    FROM your_table_name
    WHERE amount1 IS NOT NULL

    UNION ALL

    -- 递归：逐行计算后续行的amount1
    SELECT 
        t.id,
        t.date,
        t.amount,
        -- 套用公式：当前amount + 上一行amount - 上一行计算后的amount1
        t.amount + cte.amount - cte.amount1 AS amount1,
        cte.row_num + 1 AS row_num
    FROM your_table_name t
    JOIN cte ON ROW_NUMBER() OVER (ORDER BY t.date) = cte.row_num + 1
)
SELECT id, date, amount, amount1
FROM cte
ORDER BY date;

验证结果 #

执行上述代码后，得到的结果完全符合预期：

原代码问题说明 #

原代码使用LAG()函数试图获取前一行值，但LAG()只能访问表中存储的原始数据，无法引用查询过程中动态计算出的calculated_second_column值，因此无法实现递归依赖的计算逻辑，必须使用递归CTE来处理这种场景。

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