How to calculate moving average in SQL?

Question

I've a table with 2 columns in SQL

+------+--------+
| WEEK | OUTPUT |
+------+--------+
|    1 |     10 |
|    2 |     20 |
|    3 |     30 |
|    4 |     40 |
|    5 |     50 |
|    6 |     50 |
+------+--------+

How do I calculate to sum up output for 2 weeks before (ex : on week 3, it will sum up the output for week 3, 2 and 1), I've seen many tutorials to do moving average but they are using date, in my case i want to use (int), is that possible ?.

Thanks !.

Answer 1

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

Answer 2

You can use a self-join. The idea is to put you table beside itself with a condition that brings matching rows in a single row:

SELECT * FROM [output] o1
INNER JOIN [output] o2 ON o1.Week between o2.Week and o2.Week + 2

this select will produce this output:

o1.Week    o1.Output    o2.Week    o2.Output
--------------------------------------------
1          10           1          10
2          20           1          10
2          20           2          20
3          30           1          10
3          30           2          20
3          30           3          30
4          40           2          20
4          40           3          30
4          40           4          40

and so on. Note that for weeks 1 and 2 there aren't previous weeks available.

Now you should just group the data by o1.Week and get the SUM:

SELECT o1.Week, SUM(o2.Output) 
FROM [output] o1
INNER JOIN [output] o2 ON o1.Week between o2.Week and o2.Week + 2
GROUP BY o1.Week

Answer 3

You are not written your sqlserver, if it is sqlserver2012 or above , then the simple example is

declare @table table(wk int,outpt int )

insert into @table values (1,10)
,(2,20)
,(3,30)
,(4,40)
,(5,50)
,(6,60)

select *,SUM(outpt) over(partition by id order by id rows between unbounded  preceding and current row  ) dd
from (
select * , 1 id
from @table
where wk < 5
) a

Answer 4

Try this:

SELECT SUM(t1.output) / 3
FROM yourtable t1
WHERE t1.week <= 
(select t2.week from yourtable t2 where t2.week - t1.week > 0 and t2.week - t1.week <= 2)

Answer 5

If week is continuous, you can simply use Window function

SELECT [Week], [Output], 
    SUM([Output]) OVER (ORDER BY [Week] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM dbo.SomeTable

Range is more accurate for your calculation, but it not implemented in SQL Server yet. Other database engines may support

SELECT [Week], [Output], 
    SUM([Output]) OVER (ORDER BY [Week] RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM dbo.SomeTable