使用 while 循环创建计算度量值的 SQL 替代方法-解网

问：

我正在通过执行一些计算来创建一个字段。然后在下一次迭代中，我使用该结果来计算一个新值。目前使用 WHILE 循环执行此操作。还有其他方法吗？

我尝试过 LAG 和 Partition，甚至是递归 CTE，但无法达到相同的结果：

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 ;

t-sql while-loop


WITH ncte AS (
    -- number the rows sequentially without gaps
    SELECT 
        ProjectId,
        TypeId,
        Trial,
        [Period],
        FactorWifiCumulative--,
        --FactorConnectedCalc
    FROM dbo.PenetrationResults (NOLOCK) 
    WHERE ProjectId = @ProjectId

), rcte AS (
    -- find first row in each group
    SELECT *, CAST(0  AS DECIMAL(21,14)) AS Calc
    FROM ncte AS base
    WHERE Period = 1   
    UNION ALL
    -- find next row for each group from prev rows
    SELECT curr.*, 

            CAST(
            (
            (   
                prev.FactorWifiCumulative
            *
            (
                COALESCE(prev.Calc,0)
                + 1
            )
            )
            +
            (
                curr.FactorWifiCumulative*0
            )
            )
            /
            curr.FactorWifiCumulative AS DECIMAL(21,14))
            AS Calc    
    
    --CAST(prev.Calc * (1 + curr.FactorWifiCumulative / 100) AS DECIMAL(21, 14))
    
    FROM ncte AS curr
    INNER JOIN rcte AS prev 
    ON  curr.Period = prev.Period + 1
    AND curr.TypeId = prev.TypeId
    AND curr.Trial = prev.Trial
)
UPDATE [target] 
SET [FactorConnectedCalc] = [Calc]
FROM dbo.PenetrationResults AS [target] (NOLOCK) 
INNER JOIN rcte 
ON [target].[ProjectId] = rcte.ProjectId 
AND [target].[TypeId] = rcte.TypeId
AND [target].[Trial] = rcte.Trial
AND [target].[Period] = rcte.Period
OPTION(MAXRECURSION 0);

上一个：我正在尝试让我的 While 循环适用于某些场景？一些帮助表示赞赏

下一个：SQL while 循环用于每两周一次的数据

使用 while 循环创建计算度量值的 SQL 替代方法

SQL alternative to using while loop to create calculated measure

评论