T-SQL calculate moving average

Question

I am working with SQL Server 2008 R2, trying to calculate a moving average. For each record in my view, I would like to collect the values of the 250 previous records, and then calculate the average for this selection.

My view columns are as follows:

TransactionID | TimeStamp           | Value | MovAvg
----------------------------------------------------
            1 | 01.09.2014 10:00:12 |     5 |    
            2 | 01.09.2014 10:05:34 |     3 | 
...
          300 | 03.09.2014 09:00:23 |     4 | 

TransactionID is unique. For each TransactionID, I would like to calculate the average for column value, over previous 250 records. So for TransactionID 300, collect all values from previous 250 rows (view is sorted descending by TransactionID) and then in column MovAvg write the result of the average of these values. I am looking to collect data within a range of records.

Smut

Look into PARTITION BY and ROW_NUMBER – Bowes

Thanks. Any chance you have a suggestion on how to do it? – Smut

@Smut does same transaction id repeats with different value, is there timestamp column or identity column apart from transaction id? – Rutan

@RunW, can you provide sample data and if transaction id is unique , how will you have 250 previous records for same id? – Rutan

@Smut If you edit the question and include the sample data you will be able to do more formatting of it. Also, not everyone reads the comments, so it will help others when reading the question and deciding if they want to answer it. – Trap

Answer 1

The window functions in SQL 2008 are rather limited compared to later versions and if I remember correct you can only partition and you can't use any rows/range frame limit but I think this might be what you want:

;WITH cte (rn, transactionid, value) AS (
    SELECT 
       rn = ROW_NUMBER() OVER (ORDER BY transactionid),
       transactionid,
       value
    FROM your_table
)

SELECT 
    transactionid, 
    value, 
    movagv = (
        SELECT AVG(value) 
        FROM cte AS inner_ref
        -- average is calculated for 250 previous to current row inclusive
        -- I might have set the limit one row to large, maybe it should be 249
        WHERE inner_ref.rn BETWEEN outer_ref.rn-250 AND outer_ref.rn
        ) 
FROM cte AS outer_ref

Note that it applies a correlated sub-query to every row and performance might not be great.

With the later versions you could have used window frame functions and done something like this:

SELECT 
    transactionid, 
    value,
    -- avg over the 250 rows counting from the previous row
    AVG(value) OVER (ORDER BY transactionid  
                     ROWS BETWEEN 251 PRECEDING AND 1 PRECEDING),
    -- or 250 rows counting from current
    AVG(value) OVER (ORDER BY transactionid  
                     ROWS BETWEEN 250 PRECEDING AND CURRENT ROW)
FROM your_table

Rhodos

Thank you very much. It certainly is the way to do it, but as you say, the performance is quite bad. Appreciate your help. – Smut

Is there a more efficient way to do this in SQL 2008 that doesn't run a correlated sub-query on every row? I've been racking my brain to try and come up with a solution that drops my execution time but I've been coming up short. – Cinema

@Cinema There might be, but I'm not aware of any - I haven't thought much about it though as there are better options with later server versions. – Rhodos

Answer 2

Use a Common Table Expression (CTE) to include the rownum for each transaction, then join the CTE against itself on the row number so you can get the previous values to calculate the average with.

CREATE TABLE MyTable (TransactionId INT, Value INT)

;with Data as
(
  SELECT TransactionId, 
         Value, 
         ROW_NUMBER() OVER (ORDER BY TransactionId ASC) as rownum
  FROM MyTable
)
SELECT d.TransactionId , Avg(h.Value) as MovingAverage
FROM Data d
JOIN Data h on h.rownum between d.rownum-250 and d.rownum-1
GROUP BY d.TransactionId 

Trap