递归检索上一条记录的 LAG() 值

Recursively retrieve LAG() value of previous record(递归检索上一条记录的 LAG() 值)

问题描述

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

I've made the following calculation involving LAG():

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

它取前一​​个(基于 iSequence)记录的 fValue，与当前记录相加，然后除以 2.

It takes the previous (based on iSequence) record's fValue, sums with current one, and divides it by 2.

但是，我必须使用上一条记录的 fValueAjusted，而不是使用 fValue.

But, instead of using fValue, I must do that using previous record's fValueAjusted.

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

It means that first record's fValueAjusted will be its own fValue. Second record's fValueAjusted will be based on first record's fValue. And, starting from third record, it's calculation will be based on previous record's fValueAjusted.

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

I need fValueAjusted to be calculated recursively over the previous record's fValueAjusted. I can't figure out how to do that.

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

Update 1: This is an example of source data. Real table has hundreds of records and 80 clusters.

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:

This is the CTE that calculates the sequence:

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 计算，则查询将如下所示:

If fValueAjusted would be calculated based on fValueOriginal, the query would look like:

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

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

But fValueAjusted of one record must be calculated based on fValueAjusted of previous record. It would be something like:

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

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

Of course fValueAjusted isn't available when it executes. LAG() must go recursively, calculating the column for a record then providing this column for next record to use.

推荐答案

更新:原始答案不正确

正确的是:

代码使用递归 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

更新

如果遇到

最大递归100在语句前已用完完成

The maximum recursion 100 has been exhausted before statement completion

错误，然后使用 OPTION (MAXRECURSION xxx) 设置更大的递归限制，最高可达 32,767.

error, then use OPTION (MAXRECURSION xxx) to set a larger recursion limit up-to 32,767.

这篇关于递归检索上一条记录的 LAG() 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！