CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
phosplait
phosplait

Reputation: 295

Moving standard deviation with SQL Server

I have a program that caches moving averages for a very large table into another table on a daily basis, the output looks like the table below:

date       AvgNet
---------- ----------------------
2014-05-12 116.553598762874
2014-05-13 116.672864387303
2014-05-14 116.141278835128
2014-05-15 118.067104561961
2014-05-16 117.92175621176
2014-05-19 117.056031596087

How can I calculate a moving standard deviation (that is, standard deviation over the past x number of rows for each date) on the above table using SQL?

I have seen window function and CTE solutions to moving average, but from what I understand, these solutions cannot be made applicable to standard deviation.

Upvotes: 1

Views: 4718

Answers (2)

Ben Thul
Ben Thul

Reputation: 32717

I see that you're on SQL2008. If ever you get to SQL2012 or higher, the following works well enough.

SELECT  * ,
        STDEV(avgnet) 
           OVER ( 
              ORDER BY date 
              ROWS BETWEEN 10 PRECEDING AND CURRENT ROW 
           ) AS s
FROM    dbo.TestData

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270401

SQL Server has the stdev() function. However, it does not do cumulative standard deviations, so you need to use a correlated subquery or cross apply:

select t.*, t2.stdev10 
from table t outer apply
     (select stdev(t2.avgnet) as stdev10
      from (select top 10 avgnet
            from table t2
            where t2.date <= t.date
            order by t2.date desc
           ) t2
      ) t2;

Upvotes: 4

Related Questions