SQL moving average using output

Question

Assume there is a table comprises 3 rows:

date      num
05-01      1
05-02      2
05-03      3

Then,calculate its 3-day moving average using new values. For example:

1st result = (1+2+3)/3=2
2nd result = (2+3+2)/3=2.33
3rd result = (3+2+2.33)/3=2.43
4th result = (2+2.33+2.43)/3=2.25
5th result = (2.33+2.43+2.25)/3=2.34

....till the 30th result

Is is possible to implement this in SQL? Any help will be appreciated.

See if any of the answers here help you with this task: http://stackoverflow.com/questions/25922379/sql-query-for-7-day-rolling-average-in-sql-server — Bitcoin Murderous Maniac, May 12 '17 at 04:22
@Spittin' IT Thanks for your reply, but it seems to me that 'avg() over()' only suits fixed values... — superz, May 12 '17 at 05:54

score 0 · Answer 1 · answered May 13 '17 at 14:19

0

I would try creating a table with 30 rows, seeding the first 3 with your given values, and the use a recursive CTE that incorporates a window function to calculate the average and an INSERT statement to add the new row to the table.

answered May 13 '17 at 14:19

rd_nielsen

2,407
2
11
18

SQL moving average using output

1 Answers1