Calculate a Recursive Rolling Average in SQL Server

Question

We are attempting to calculate a rolling average and have tried to convert numerous SO answers to solve the problem. To this point we are still unsuccessful.

What we've tried:

Here are some of the SO answers we have considered.

• SQL Server: How to get a rolling sum over 3 days for different customers within same table
• SQL Query for 7 Day Rolling Average in SQL Server
• T-SQL calculate moving average

Our latest attempt has been to modify one of the solutions (#4) found here. https://www.red-gate.com/simple-talk/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/

Example:

Here is an example in SQL Fiddle: http://sqlfiddle.com/#!6/4570a/17

In the fiddle, we are still trying to get the SUM to work right but ultimately we are trying to get the average.

The end goal

Using the Fiddle example, we need to find the difference between Value1 and ComparisonValue1 and present it as Diff1. When a row has no Value1 available, we need to estimate it by taking the average of the last two Diff1 values and then add it to the ComparisonValue1 for that row.

With the correct query, the result would look like this:

GroupID  Number  ComparisonValue1  Diff1  Value1
5        10      54.78             2.41   57.19
5        11      55.91             2.62   58.53
5        12      55.93             2.78   58.71
5        13      56.54             2.7    59.24
5        14      56.14             2.74   58.88
5        15      55.57             2.72   58.29
5        16      55.26             2.73   57.99

Question: is it possible to calculate this average when it could potentially factor into the average of the following rows?

Update:

• Added a VIEW to the Fiddle schema to simplify the final query.
• Updated the query to include the new rolling average for Diff1 (column Diff1Last2Avg). This rolling average works great until we run into nulls in the Value1 column. This is where we need to insert the estimate.
• Updated the query to include the estimate that should be used when there is no Value1 (column Value1Estimate). This is working great and would be perfect if we could use the estimate in place of NULL in the Value1 column. Since the Diff1 column reflects the difference between Value1 (or its estimate) and ComparisonValue1, including the Estimate would fill in all the NULL values in Diff1. This in turn would continue to allow the Estimates of future rows to be calculated. It gets confusing at this point, but still hacking away at it. Any ideas?

Answer 1

Credit for the idea goes to this answer: https://stackoverflow.com/a/35152131/6305294 from @JesúsLópez

I have included comments in the code to explain it.

UPDATE

• I have corrected the query based on comments.
• I have swapped numbers in minuend and subtrahend to get difference as a positive number.
• Removed Diff2Ago column.

Results of the query now exactly match your sample output.

;WITH cte AS
(
    -- This is similar to your ItemWithComparison view
    SELECT i.Number, i.Value1, i2.Value1 AS ComparisonValue1,
        -- Calculated Differences; NULL will be returned when i.Value1 is NULL
        CONVERT( DECIMAL( 10, 3 ), i.Value1 - i2.Value1 ) AS Diff
    FROM Item AS i
            LEFT JOIN [Group] AS G ON g.ID = i.GroupID
            LEFT JOIN Item AS i2 ON i2.GroupID = g.ComparisonGroupID AND i2.Number = i.Number
    WHERE NOT i2.Id IS NULL
),
cte2 AS(
    /*
    Start with the first number

    Note if you do not have at least 2 consecutive numbers (in cte) with non-NULL Diff value and therefore Diff1Ago or Diff2Ago are NULL then everything else will not work;
    You may need to add additional logic to handle these cases */
    SELECT TOP 1 -- start with the 1st number (see ORDER BY)
            a.Number, a.Value1, a.ComparisonValue1, a.Diff, b.Diff AS Diff1Ago
    FROM cte AS a
            -- "1 number ago"
            LEFT JOIN cte AS b ON a.Number - 1 = b.Number
    WHERE NOT a.Value1 IS NULL
    ORDER BY a.Number
    UNION ALL
    SELECT b.Number, b.Value1, b.ComparisonValue1,
            ( CASE
                WHEN NOT b.Value1 IS NULL THEN b.Diff
                ELSE CONVERT( DECIMAL( 10, 3 ), ( a.Diff + a.Diff1Ago ) / 2.0 )
            END ) AS Diff,
        a.Diff AS Diff1Ago
    FROM cte2 AS a
        INNER JOIN cte AS b ON a.Number + 1 = b.Number
)
SELECT *, ( CASE WHEN Value1 IS NULL THEN ComparisonValue1 + Diff ELSE Value1 END ) AS NewValue1
FROM cte2 OPTION( MAXRECURSION 0 );

Limitations: this solution works well only when you need to consider small number of preceding values.