TICKER daily moving average

Question

I am trying to calculate, say, a 3-day moving average (actually 30-day) volume for stocks. I am trying to get the average of the last 3 records of the date (and not today - 3 days). I tried to do something with rownumber in SQL Server 2012, but without success. Maybe someone will help. Below is a diagram of the template, and my attempt to garbage in SQL. I have various incarnations below SQL with a group, but still not working. Thank you very much!

select dt_eod, ticker, volume from ( select dt_eod, ticker, avg(volume) row_number() over(partition by dt_eod order by max_close desc) rn from mytable ) src where rn >= 1 and rn <= 3 order by dt_eod 

Example circuit:

 CREATE TABLE yourtable ([dt_date] int, [ticker] varchar(1), [volume] int); INSERT INTO yourtable ([dt_date], [ticker], [volume]) VALUES (20121201, 'A', 5), (20121201, 'B', 7), (20121201, 'C', 6), (20121202, 'A', 10), (20121202, 'B', 8), (20121202, 'C', 7), (20121203, 'A', 10), (20121203, 'B', 87), (20121203, 'C', 74), (20121204, 'A', 10), (20121204, 'B', 86), (20121204, 'C', 67), (20121205, 'A', 100), (20121205, 'B', 84), (20121205, 'C', 70), (20121206, 'A', 258), (20121206, 'B', 864), (20121206, 'C', 740); 

Answer 1

Three days average for each row:

 with top3Values as ( select t.ticker, t.dt_date, top3.volume from yourtable t outer apply ( select top 3 top3.volume from yourtable top3 where t.ticker = top3.ticker and t.dt_date >= top3.dt_date order by top3.dt_date desc ) top3 ) select ticker, dt_date, ThreeDayVolume = avg(volume) from top3Values group by ticker, dt_date order by ticker, dt_date 

Demo version of SQL Fiddle .

Last value:

 with tickers as ( select distinct ticker from yourtable ), top3Values as ( select t.ticker, top3.volume from tickers t outer apply ( select top 3 top3.volume from yourtable top3 where t.ticker = top3.ticker order by dt_date desc ) top3 ) select ticker, ThreeDayVolume = avg(volume) from top3Values group by ticker order by ticker 

Demo version of SQL Fiddle .

Actually, you will not need to create CTE tickers for the second query, as you would base it on the [ticker] table, and you probably would have some kind of date parameter in the query, but hopefully this helps you on the right track .

Answer 2

You mentioned SQL 2012, which means you can use a much simpler paradigm.

 select dt_date, ticker, avg(1.0*volume) over ( partition by ticker order by dt_date ROWS BETWEEN 2 preceding and current row ) from yourtable 

I find it much more transparent about what it is actually trying to accomplish.

Answer 3

You can see another technique presented here: The SQL-Server Moving Averages algorithm with flexible window periods joins .

The algorithm is quite fast (much faster than APPLY and does not deteriorate in performance, for example, APPLY when the window of the data-window expands), easily adapts to your requirement, works with pre-SQL2012 and overcomes the limitations of SQL-2012, which requires hard coding of the window width in the OVER / PARTITION-BY clause.

For a market type application with moving average prices, the general requirement allows the user to change the number of data points included in the average value (from a user interface selection, for example, allowing the user to select 7 days, 30 days, 60 days, etc.), and the offer OVER SQL-2012 cannot handle this section width variable without dynamic SQL.