sql-server - 递归检索前一条记录的 LAG() 值

Question

我进行了以下涉及 LAG() 的计算:

(lag(fValue,1,fValue) OVER (PARTITION BY Cluster ORDER BY iSequence) + fValue)/2 as fValueAjusted

它采用前一个(基于 iSequence)记录的 fValue，与当前记录的 fValue 相加，然后除以 2。

但是，我必须使用上一条记录的 fValueAjusted 来代替 fValue。

这意味着第一条记录的 fValueAjusted 将是它自己的 fValue。第二条记录的 fValueAjusted 将基于第一条记录的 fValue。并且，从第三条记录开始，将根据前一条记录的fValueAjusted进行计算。

我需要在前一个记录的 fValueAjusted 上递归计算 fValueAjusted。我不知道该怎么做。

更新 1:这是源数据的示例。真实表有数百条记录和80个簇。

CREATE TABLE dbo.example (
    iUnity      int NOT NULL,
    Cluster     char(2) NOT NULL,
    fValue      float NOT NULL
)

15  A1      150
17  A1      170
21  B2      210
23  B2      230
71  C3      710

这是计算序列的 CTE:

WITH cteSequencing AS (
    SELECT
        iUnity,Cluster
        ,fValue as fValueOriginal
        ,row_number() OVER (PARTITION BY Cluster ORDER BY fValueOriginal) as iSequence
    FROM dbo.example
)

如果 fValueAjusted 根据 fValueOriginal 计算，则查询将如下所示:

SELECT
    iUnity,Cluster,fValueOriginal
    ,(
        lag(fValue,1,fValue) OVER (PARTITION BY Cluster ORDER BY iSequence)
        + fValueOriginal
    ) / 2 as fValueAjusted
FROM cteSequencing

但是一条记录的fValueAjusted必须根据前一条记录的fValueAjusted计算。它会是这样的:

SELECT
    iUnity,Cluster,fValueOriginal
    ,(
        lag(fValueAjusted,1,fValueOriginal) OVER (PARTITION BY Cluster ORDER BY iSequence)
        + fValueOriginal
    ) / 2 as fValueAjusted
FROM cteSequencing

当然，fValueAjusted 在执行时不可用。 LAG() 必须递归地计算一条记录的列，然后提供该列供下一条记录使用。

Answer 1

更新:原始答案不正确

正确的是:

代码使用递归 CTE

CREATE TABLE #example (
    iUnity      int NOT NULL,
    Cluster     char(2) NOT NULL,
    fValue      float NOT NULL
)
INSERT INTO #example
VALUES
( 15,  'A1',      150 ),
( 16,  'A1',      170 ),
( 17,  'A1',      190 ),
( 18,  'A1',      210 ),
( 21,  'B2',      210 ),
( 23,  'B2',      230 ),
( 71,  'C3',      710 )

WITH cteSequencing AS (
    -- Get Values Order
    SELECT iUnity, Cluster, fValue, fValue AS fValueAjusted,
        ROW_NUMBER() OVER (PARTITION BY Cluster ORDER BY fValue) AS iSequence
    FROM #example
),
Recursion AS(
    -- Anchor - the first value in clusters
    SELECT iUnity, Cluster, fValue, fValueAjusted, iSequence
    FROM cteSequencing
    WHERE iSequence = 1
    UNION ALL
    -- Calculate next value based on the previous
    SELECT b.iUnity As iUnity, b.Cluster, b.fValue,
        ( a.fValueAjusted + b.fValue ) / 2 AS fValueAjusted,
        b.iSequence
    FROM Recursion AS a
        INNER JOIN cteSequencing AS b ON a.iSequence + 1 = b.iSequence AND a.Cluster = b.Cluster
)
SELECT * FROM Recursion ORDER BY Cluster, fValue

-- Manually check results
SELECT ( 150 + 170 ) / 2
SELECT ( 190 + 160 ) / 2 
SELECT ( 190 + 170 ) / 2

输出:

iUnity      Cluster fValue                 fValueAjusted          iSequence
----------- ------- ---------------------- ---------------------- --------------------
15          A1      150                    150                    1
16          A1      170                    160                    2
17          A1      190                    175                    3
18          A1      210                    192.5                  4
21          B2      210                    210                    1
23          B2      230                    220                    2
71          C3      710                    710                    1

更新

如果你遇到

The maximum recursion 100 has been exhausted before statement completion

错误，则使用OPTION (MAXRECURSION xxx)设置更大的递归限制，最多 32,767。