Oracle中插入列值时如何引用同列上一行值按规则计算Final_value

该需求属于逐行依赖的递推计算场景，Oracle中可以通过**递归WITH子句（递归CTE）**实现，若需要更新原表可配合MERGE语句完成。

1. 基础查询实现（直接得到计算结果） #

假设你的表名为your_table，递推排序依据为date字段，查询语句如下：

WITH RECURSIVE calc_cte AS (
    -- 锚点成员：取排序后的第一行，Final_value取对应column1值
    SELECT 
        "date",
        column1,
        column2,
        column1 AS Final_value,
        ROW_NUMBER() OVER (ORDER BY "date") AS rn
    FROM your_table
    ORDER BY "date"
    FETCH FIRST 1 ROW ONLY
    
    UNION ALL
    
    -- 递归成员：逐行计算后续的Final_value
    SELECT 
        t."date",
        t.column1,
        t.column2,
        CASE 
            WHEN t.column2 IS NOT NULL 
            THEN c.Final_value * (t.column1 / c.column1) + t.column2 * (c.column1 / c.Final_value)
            ELSE c.Final_value * (t.column1 / c.column1)
        END AS Final_value,
        c.rn + 1 AS rn
    FROM calc_cte c
    JOIN (
        SELECT 
            "date",
            column1,
            column2,
            ROW_NUMBER() OVER (ORDER BY "date") AS rn
        FROM your_table
    ) t ON t.rn = c.rn + 1
)
SELECT "date", column1, column2, ROUND(Final_value, 6) AS Final_value
FROM calc_cte
ORDER BY "date";

Oracle 11gR2及以上版本均支持递归WITH语法，上述查询返回结果与你给出的预期输出完全一致。如果业务场景中存在column1为0或者上一行Final_value为0的情况，需要额外添加除零判断逻辑。

2. 原表更新方案 #

如果需要把计算结果写入原表的Final_value列，可以使用MERGE语句实现：

MERGE INTO your_table t
USING (
    -- 递归查询得到计算结果作为更新数据源
    WITH RECURSIVE calc_cte AS (
        SELECT 
            "date",
            column1,
            column2,
            column1 AS Final_value,
            ROW_NUMBER() OVER (ORDER BY "date") AS rn
        FROM your_table
        ORDER BY "date"
        FETCH FIRST 1 ROW ONLY
        
        UNION ALL
        
        SELECT 
            t."date",
            t.column1,
            t.column2,
            CASE 
                WHEN t.column2 IS NOT NULL 
                THEN c.Final_value * (t.column1 / c.column1) + t.column2 * (c.column1 / c.Final_value)
                ELSE c.Final_value * (t.column1 / c.column1)
            END AS Final_value,
            c.rn + 1 AS rn
        FROM calc_cte c
        JOIN (
            SELECT 
                "date",
                column1,
                column2,
                ROW_NUMBER() OVER (ORDER BY "date") AS rn
            FROM your_table
        ) t ON t.rn = c.rn + 1
    )
    SELECT "date", Final_value FROM calc_cte
) c
ON (t."date" = c."date")
WHEN MATCHED THEN UPDATE SET t.Final_value = c.Final_value;

如果date字段存在重复值，可以替换排序逻辑，改用其他唯一排序键保证递推顺序正确。

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