T-SQL Calculate Moving Average

T-SQL calculate moving average

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

SQL Server : moving average calculation

Thank you! I solved it as following:

SELECT  [Brand],
        [month],
        cast(sum([Volume]) AS INT) as [Volume (t)],
        AVG(cast(sum([Volume])AS INT)) OVER (PARTITION BY [Brand]) AS [Average (t)],

        CASE WHEN (Row_Number() OVER (ORDER BY [month]))>3
        THEN AVG(cast(sum([Volume])AS INT)) 
                    OVER (PARTITION BY [Brand] ORDER BY [month] ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) 
                    ELSE NULL END AS [Moving Average (3)]
............

How to calculate moving average in SQL?

I think you want something like this :

SELECT *,
       (SELECT Sum(output)
        FROM   table1 b
        WHERE  b.week IN( a.week, a.week - 1, a.week - 2 )) AS SUM
FROM   table1 a 

OR

In clause can be converted to between a.week-2 and a.week.

sql fiddle

Calculate moving average using SQL window functions with leading null's where not enough data is avaliable

You could use another window function (COUNT()) to make sure that at least two records are available in the window before doing the computation, like:

SELECT
    nr, 
    value, 
    CASE WHEN COUNT(*) OVER(ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) > 1
        THEN AVG(value) OVER (ORDER BY nr ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING)::FLOAT 
        ELSE NULL
    END AS "Moving-Average-2"
FROM average;

Demo on DB Fiddle:

| nr  | value | Moving-Average-2 |
| --- | ----- | ---------------- |
| 1   | 2     |                  |
| 2   | 4     | 3                |
| 3   | 6     | 5                |
| 3   | 8     | 7                |
| 4   | 10    | 9                |

---

Related Topics