替代WHILE循环实现计算型度量的SQL方案咨询

问题背景 #

需要生成一个依赖前一次计算结果的字段FactorCalculated，当前使用WHILE循环逐周期迭代更新，但尝试LAG函数、分区函数、递归CTE均未达到预期效果，现有WHILE循环代码如下：

DECLARE @Period INT = 2;
DECLARE @MaxPeriod INT;

SELECT @MaxPeriod = Periods FROM dbo.Engine (NOLOCK);

WHILE (@Period <= @MaxPeriod)
BEGIN

    WITH CTE AS (
        SELECT 
            A.ProjectId, A.TypeId, A.Trial, A.Period, 
            ((B.ValueA * (COALESCE(B.FactorCalculated, 0) + 1)) +
                (A.ValueA * 0)) / A.ValueA AS FactorCalculated
        FROM dbo.PenetrationResults A (NOLOCK)
        INNER JOIN dbo.PenetrationResults B (NOLOCK)
            ON A.ProjectId = B.ProjectId
                AND A.TypeId = B.TypeId
                AND A.Trial = B.Trial
                AND (A.Period - 1) = B.Period 
        WHERE A.[Period] = @Period
        AND A.ProjectId = @ProjectId
    )
    UPDATE [target]
    SET FactorCalculated = CTE.FactorCalculated
    FROM dbo.PenetrationResults AS [target] --(TABLOCK)
    INNER JOIN CTE 
        ON [target].[ProjectId] = CTE.ProjectId AND [target].[TypeId] = CTE.TypeId 
        AND [target].[Trial] = CTE.Trial
        AND [target].[Period] = CTE.Period

    SET @Period = @Period + 1
END ;

替代方案：递归CTE实现递推计算 #

之前递归CTE失效的原因大概率是没有正确构建锚点（初始周期的计算值）和递归迭代逻辑。以下是适配需求的递归CTE写法，一次性计算所有周期的FactorCalculated，无需逐次更新：

核心逻辑 #

1. 锚点成员：获取周期为1的初始数据作为递推起点，若周期1的FactorCalculated已有初始值则直接复用，否则按业务逻辑初始化。
2. 递归成员：基于上一周期的计算结果，严格复刻原WHILE循环的公式推导当前周期的FactorCalculated，按ProjectId, TypeId, Trial分组递推。
3. 批量更新：将递归CTE计算出的全周期结果一次性同步到原表。

完整代码 #

WITH RecursiveCalculations AS (
    -- 锚点：初始周期（Period=1）的基础数据
    SELECT 
        ProjectId, TypeId, Trial, Period,
        ValueA,
        -- 此处替换为Period=1时FactorCalculated的初始值逻辑，已有值则直接用原字段
        COALESCE(FactorCalculated, 0) AS FactorCalculated
    FROM dbo.PenetrationResults
    WHERE Period = 1
      AND ProjectId = @ProjectId

    UNION ALL

    -- 递归：基于上一周期结果计算当前周期
    SELECT 
        curr.ProjectId, curr.TypeId, curr.Trial, curr.Period,
        curr.ValueA,
        -- 完全对齐原WHILE循环的计算逻辑
        ((prev.ValueA * (prev.FactorCalculated + 1)) + (curr.ValueA * 0)) / curr.ValueA AS FactorCalculated
    FROM dbo.PenetrationResults curr
    INNER JOIN RecursiveCalculations prev
        ON curr.ProjectId = prev.ProjectId
        AND curr.TypeId = prev.TypeId
        AND curr.Trial = prev.Trial
        AND curr.Period = prev.Period + 1
    WHERE curr.Period <= (SELECT Periods FROM dbo.Engine (NOLOCK))
      AND curr.ProjectId = @ProjectId
)
-- 一次性更新所有周期的计算结果
UPDATE target
SET FactorCalculated = rc.FactorCalculated
FROM dbo.PenetrationResults target
INNER JOIN RecursiveCalculations rc
    ON target.ProjectId = rc.ProjectId
    AND target.TypeId = rc.TypeId
    AND target.Trial = rc.Trial
    AND target.Period = rc.Period
WHERE target.ProjectId = @ProjectId;

方案优势 #

• 避免WHILE循环的逐次表扫描与更新，周期数越多性能提升越明显。
• 逻辑紧凑，无需维护循环变量，降低人为出错概率。
• 可直接在递归CTE中查询计算结果，便于调试验证。

注意事项 #

• 确保ProjectId, TypeId, Trial, Period组合为唯一键，防止递归时出现多匹配问题。
• 若周期不连续，需在递归成员中添加连续性判断，或提前预处理数据保证周期序列完整。
• 若递归深度超过默认限制（100），可在语句末尾添加OPTION (MAXRECURSION 0)，但需评估数据量避免性能问题。

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