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

问题说明 #

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

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

可复现的测试表结构及数据：

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、3、-7、-22、-28

错误代码分析 #

你尝试的CASE+LAG函数写法无法实现递归计算，因为LAG只能获取已有行的历史值，无法动态引用刚计算出的前一行结果，所以无法完成递推逻辑。

正确解决方案 #

方法1：递归CTE（直观递归实现） #

利用递归CTE先锚定第一行，再逐行递推计算：

WITH recursive cte AS (
    -- 锚点成员：获取第一行数据
    SELECT 
        id, 
        date, 
        amount, 
        amount1 AS calculated_amount1,
        ROW_NUMBER() OVER (ORDER BY date) AS rn
    FROM your_table_name
    WHERE amount1 IS NOT NULL

    UNION ALL

    -- 递归成员：关联上一行，计算当前行的amount1
    SELECT 
        t.id, 
        t.date, 
        t.amount, 
        c.calculated_amount1 - t.amount AS calculated_amount1,
        c.rn + 1 AS rn
    FROM your_table_name t
    JOIN cte c ON ROW_NUMBER() OVER (ORDER BY t.date) = c.rn + 1
)
SELECT id, date, amount, calculated_amount1 AS amount1
FROM cte
ORDER BY date;

方法2：窗口函数累加（高效非递归写法） #

观察计算规则可发现，每一行的amount1 = 初始值 - 该行之前（不含自身）所有amount的累加和，因此可以用窗口函数实现：

SELECT 
    id,
    date,
    amount,
    -- 初始amount1值减去当前行之前所有amount的总和
    (SELECT amount1 FROM your_table_name WHERE amount1 IS NOT NULL) 
    - SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    AS amount1
FROM your_table_name;

注：如果初始值所在行不是第一行，需要调整窗口范围或先确定初始值的位置。

结果验证 #

两种方法都会返回符合预期的结果：

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