IBM i环境下SQL递归计算：依赖前置值的双列实现需求

需求说明 #

需要计算两个互相依赖的列，规则如下：

• Additions = ROUND(Total_COB_GROWTH, 0) + Sum_of_Previous_Interest
• Previous_Interest = ROUNDDOWN(IF(当前行New_Total_Rate=上一行New_Total_Rate, 上一行Additions*(1+New_Total_Rate/100)^(Duration/12), 0), 0)

这两列依赖上一行的计算结果，使用LAG() OVER(PARTITION BY ...)无法实现连续值传递，后续行返回0。

源表结构及数据 #

期望计算结果 #

尝试的代码及错误 #

尝试用递归CTE实现，但报错SQL Error [42908]: [SQL0343] Column list not valid for table，代码如下：

WITH #cte AS (
SELECT 
    UNIQUEKEY, 
    POL_NO, 
    NEW_TOTAL_RATE, 
    TOTAL_COB_GROWTH, 
    DURATION,
    0 AS "Additions to date TF",
    0 AS "Sum of previous with interest",
    1 AS RowNum
FROM 
    QRYLIB.temp_Test
UNION ALL
SELECT 
    t.UNIQUEKEY, 
    t.POL_NO, 
    t.NEW_TOTAL_RATE, 
    t.TOTAL_COB_GROWTH, 
    t.DURATION,
    CASE 
        WHEN t.DURATION = 0 THEN 
            LAG(#cte."Sum of previous with interest", 1, 0) OVER (PARTITION BY t.POL_NO ORDER BY t.UNIQUEKEY) + ROUND(t.TOTAL_COB_GROWTH, 0)
        ELSE 
            LAG(#cte."Sum of previous with interest", 1, 0) OVER (PARTITION BY t.POL_NO ORDER BY t.UNIQUEKEY)
    END AS "Additions to date TF",
    CASE 
        WHEN t.POL_NO = LAG(t.POL_NO, 1, 0) OVER (ORDER BY t.UNIQUEKEY) THEN 
            ROUND(LAG(#cte."Sum of previous with interest", 1, 0) OVER (ORDER BY t.UNIQUEKEY) * POWER(1 + t.TOTAL_COB_GROWTH / 100, t.DURATION / 12), 0)
        ELSE 
            0
    END AS "Sum of previous with interest",
    #cte.RowNum + 1 AS RowNum
FROM 
    QRYLIB.temp_Test t
JOIN 
    #cte ON t.UNIQUEKEY = #cte.UNIQUEKEY + 1
)
SELECT 
    UNIQUEKEY, 
    POL_NO, 
    NEW_TOTAL_RATE, 
    TOTAL_COB_GROWTH, 
    DURATION,
    "Additions to date TF",
    "Sum of previous with interest"
FROM #cte
WHERE RowNum = 1
ORDER BY UNIQUEKEY;

正确的递归CTE解决方案（IBM i SQL） #

IBM i的递归CTE不能在递归部分使用窗口函数（如LAG()），递归是逐行处理的，直接引用上一行的计算结果即可。另外需要先给源表生成有序行号，确保递归按正确顺序执行。

完整代码如下：

WITH ranked_data AS (
    SELECT 
        UniqueKey,
        Pol_No,
        New_Total_Rate,
        Total_COB_Growth,
        Duration,
        ROW_NUMBER() OVER(PARTITION BY Pol_No ORDER BY UniqueKey) AS rn
    FROM QRYLIB.temp_Test
),
recursive_calc AS (
    -- 锚点成员：取每个Pol_No的第一行
    SELECT 
        UniqueKey,
        Pol_No,
        New_Total_Rate,
        Total_COB_Growth,
        Duration,
        ROUND(Total_COB_Growth, 0) + 0 AS Additions, -- 第一行Previous_Interest为0
        CAST(0 AS DECIMAL(18,0)) AS Previous_Interest,
        rn
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归成员：逐行计算，引用上一行的结果
    SELECT 
        rd.UniqueKey,
        rd.Pol_No,
        rd.New_Total_Rate,
        rd.Total_COB_Growth,
        rd.Duration,
        -- 计算当前行Additions
        ROUND(rd.Total_COB_Growth, 0) + rc.Previous_Interest AS Additions,
        -- 计算当前行Previous_Interest
        CASE
            WHEN rd.New_Total_Rate = rc.New_Total_Rate THEN
                ROUNDDOWN(rc.Additions * POWER(1 + rd.New_Total_Rate / 100, rd.Duration / 12), 0)
            ELSE
                0
        END AS Previous_Interest,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_calc rc 
        ON rd.Pol_No = rc.Pol_No 
        AND rd.rn = rc.rn + 1
)
-- 输出最终结果
SELECT 
    UniqueKey,
    Pol_No,
    New_Total_Rate,
    Total_COB_Growth,
    Duration,
    Additions,
    Previous_Interest
FROM recursive_calc
ORDER BY Pol_No, UniqueKey;

代码说明 #

1. ranked_data CTE：为每个保单分组生成有序行号，确保递归时按UniqueKey的顺序逐行处理。
2. recursive_calc CTE：
   • 锚点成员处理每个保单的第一行，初始化Additions和Previous_Interest（第一行Previous_Interest为0）。
   • 递归成员通过行号关联上一行的结果，直接使用上一行的Additions和Previous_Interest计算当前行的值，解决连续值传递问题。
3. 最终查询：按保单和UniqueKey排序输出，结果与期望一致。

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