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.
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.
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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With