Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Recursively retrieve LAG() value of previous record

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

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

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

But, instead of using fValue, I must do that using previous record's 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.

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

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

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
)

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

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

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.

like image 831
Hikari Avatar asked Jul 10 '17 16:07

Hikari


People also ask

How do I find previous row values in SQL?

1) You can use MAX or MIN along with OVER clause and add extra condition to it. The extra condition is "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING" which will fetch previous row value. Check this: SELECT *,MIN(JoiningDate) OVER (ORDER BY JoiningDate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS EndDate.

What is LAG () in SQL?

LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

How does CTES recursive work?

Recursive CTE Syntax A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results. FROM cte_name; Again, at the beginning of your CTE is the WITH clause.

How can use lead and lag function in SQL Server?

The LEAD function is used to access data from SUBSEQUENT rows along with data from the current row. The LAG function is used to access data from PREVIOUS rows along with data from the current row. An ORDER BY clause is required when working with LEAD and LAG functions, but a PARTITION BY clause is optional.


1 Answers

UPDATE: Original Answer was not correct

Here is the correct one:

The code uses recursive CTEs

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

Output:

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

Update

If you encounter

The maximum recursion 100 has been exhausted before statement completion

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

like image 148
Alex Avatar answered Sep 17 '22 20:09

Alex