SQL Server中基于历史行值迭代计算Delinq行Amount的实现

直接上可落地的SQL实现，针对只读数据库用临时表处理，完美适配你要求的迭代逻辑和多周期场景：

1. 初始化临时表并标记处理顺序 #

先把源表数据复制到临时表，同时给需要处理的Delinq且Amount=0的行按时间顺序标记序号，后续按这个序号逐行迭代：

-- 创建临时表，包含源表所有字段+Delinq行的处理顺序标记
SELECT 
    *,
    -- 仅对目标行标记顺序，非目标行留空不参与迭代
    CASE WHEN Period_Status = 'Delinq' AND Amount = 0 THEN
        DENSE_RANK() OVER (PARTITION BY Period_Status ORDER BY period_end_dt ASC)
    ELSE NULL END AS Delinq_Rank
INTO #Temp_Amount_Update
FROM Your_Source_Table; -- 替换成你的实际源表名

2. 按顺序迭代更新 #

因为更新依赖前一行的结果，必须用WHILE循环逐行处理，同时通过CASE适配不同周期的历史数据规则：

DECLARE @Current_Rank INT = 1;
DECLARE @Max_Rank INT;

-- 先拿到需要处理的最大序号
SELECT @Max_Rank = MAX(Delinq_Rank) FROM #Temp_Amount_Update WHERE Delinq_Rank IS NOT NULL;

WHILE @Current_Rank <= @Max_Rank
BEGIN
    DECLARE @Current_Dt DATE, @Time_Period VARCHAR(20), @History_Rows INT;
    DECLARE @Avg_Amount DECIMAL(18,2), @Same_Period_Amount DECIMAL(18,2), @New_Amount DECIMAL(18,2);

    -- 获取当前待处理行的时间和周期类型
    SELECT 
        @Current_Dt = period_end_dt,
        @Time_Period = Time_Period
    FROM #Temp_Amount_Update WHERE Delinq_Rank = @Current_Rank;

    -- 根据周期类型确定需要取的历史行数
    SET @History_Rows = CASE @Time_Period
        WHEN 'Quarterly' THEN 4  -- 季度取前4行（1年）
        WHEN 'Monthly' THEN 12   -- 月度取前12行（1年）
        WHEN 'Annual' THEN 1     -- 年度取前1行（1年）
        ELSE 0 -- 其他周期自行扩展
    END;

    -- 计算Amount A：前1年周期内的Amount平均值
    SELECT @Avg_Amount = AVG(Amount)
    FROM (
        SELECT TOP (@History_Rows) Amount
        FROM #Temp_Amount_Update
        WHERE period_end_dt < @Current_Dt AND Period_Status = 'Delinq'
        ORDER BY period_end_dt DESC
    ) AS History_Data;

    -- 计算Amount B：上一年同期的Amount
    SELECT @Same_Period_Amount = Amount
    FROM #Temp_Amount_Update
    WHERE 
        period_end_dt = CASE @Time_Period
            WHEN 'Quarterly' THEN DATEADD(QUARTER, -4, @Current_Dt)
            WHEN 'Monthly' THEN DATEADD(MONTH, -12, @Current_Dt)
            WHEN 'Annual' THEN DATEADD(YEAR, -1, @Current_Dt)
        END
        AND Period_Status = 'Delinq';

    -- 取两者较大值，处理NULL场景（比如无历史数据）
    SET @New_Amount = IIF(@Avg_Amount > @Same_Period_Amount, @Avg_Amount, @Same_Period_Amount);
    SET @New_Amount = ISNULL(@New_Amount, 0); -- 无数据时默认值可按需调整

    -- 更新当前行的Amount
    UPDATE #Temp_Amount_Update
    SET Amount = @New_Amount
    WHERE Delinq_Rank = @Current_Rank;

    -- 推进到下一行
    SET @Current_Rank += 1;
END;

3. 查看结果并清理 #

-- 查看更新后的数据
SELECT * FROM #Temp_Amount_Update ORDER BY period_end_dt;

-- 用完临时表记得删除
DROP TABLE IF EXISTS #Temp_Amount_Update;

关键说明 #

• 同期匹配逻辑：如果上一年同期的行不存在，@Same_Period_Amount会为NULL，此时自动取平均值；如果平均值也为NULL（比如是最早的几行），用ISNULL设置默认值，可根据业务需求修改。
• 周期扩展：如果需要支持半年度等其他周期，直接在CASE语句里加对应的分支即可，比如WHEN 'Semi-Annual' THEN 2，同时调整同期日期偏移为DATEADD(MONTH, -6, @Current_Dt)。
• 性能提示：如果待处理的Delinq行数特别多，WHILE循环效率可能不高，可以考虑改用递归CTE，但递归写法需要更严谨的排序和终止条件，小到中等规模数据用WHILE更直观好维护。

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