T-SQL calculate moving average

by

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

More Related Contents:

  1. Calculating distance between two points (Latitude, Longitude)
  2. Why no windowed functions in where clauses?
  3. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  4. T-SQL split string based on delimiter
  5. WHERE Clause vs ON when using JOIN
  6. Partition Function COUNT() OVER possible using DISTINCT
  7. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  8. Count Returning blank instead of 0
  9. Using IF ELSE statement based on Count to execute different Insert statements
  10. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  11. Convert Month Number to Month Name Function in SQL
  12. Multiple INSERT statements vs. single INSERT with multiple VALUES
  13. Limit on the WHERE col IN (…) condition
  14. Removing leading zeroes from a field in a SQL statement
  15. Getting result of dynamic SQL into a variable for sql-server
  16. Better techniques for trimming leading zeros in SQL Server?
  17. How can I determine the status of a job?
  18. SQL: How can I get the value of an attribute in XML datatype?
  19. Do clustered indexes have to be unique?
  20. Aggregate function in an SQL update query?
  21. SQL Server search using like while ignoring blank spaces
  22. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  23. How do I find the data directory for a SQL Server instance?
  24. Compare dates in T-SQL, ignoring the time part
  25. How do I compare two columns for equality in SQL Server?
  26. Mod negative numbers in SQL just like excel
  27. Find last sunday
  28. How to concatenate all columns in a select with SQL Server
  29. SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns
  30. Removing duplicate rows (based on values from multiple columns) from SQL table

Leave a Comment