20 Day moving average with joins alone

Question

There are questions like this all over the place so let me specify where I specifically need help.

I have seen moving averages in SQL with Oracle Analytic functions, MSSQL apply, or a variety of other methods. I have also seen this done with self joins (one join for each day of the average, such as here How do you create a Moving Average Method in SQL? ).

I am curious as to if there is a way (only using self joins) to do this in SQL (preferably oracle, but since my question is geared towards joins alone this should be possible for any RDBMS). The way would have to be scalable (for a 20 or 100 day moving average, in contrast to the link I researched above, which required a join for each day in the moving average).

My thoughts are

select customer, a.tradedate, a.shares, avg(b.shares) 
from trades a, trades b
where b.tradedate between a.tradedate-20 and a.tradedate
group by customer, a.tradedate

But when I tried it in the past it hadn't worked. To be more specific, I am trying a smaller but similar exmaple (5 day avg instead of 20 day) with this fiddle demo and cant find out where I am going wrong. http://sqlfiddle.com/#!6/ed008/41

select a.ticker, a.dt_date, a.volume, avg(b.volume) 
from yourtable a, yourtable b
where b.dt_date between a.dt_date-5 and a.dt_date
and a.ticker=b.ticker
group by a.ticker, a.dt_date, a.volume

score 0 · Answer 1 · answered Sep 09 '14 at 02:36

0

http://sqlfiddle.com/#!6/ed008/45 would appear to be what you need.

select a.ticker, 
       a.dt_date,
       a.volume,
       (select avg(cast(b.volume as float))
        from yourtable b
        where b.dt_date between a.dt_date-5 and a.dt_date
              and a.ticker=b.ticker)
from yourtable a
order by a.ticker, a.dt_date

not a join but a subquery

answered Sep 09 '14 at 02:36

Dale M

2,453
1
13
21

Thanks a lot! While I mentioned join, the spirit of my question was moreso about solving this using foundational elements of SQL, and dependent subqueries will be included in this. Is there a way to do this without a subquery and just with self joins? – jfalkson Sep 09 '14 at 02:42

Brian DeMilia · Accepted Answer · 2014-09-09T02:54:24.377

I don't see anything wrong with your second query, I think the only reason it's not what you're expecting is because the volume field is an integer data type so when you calculate the average the resulting output will also be an integer data type. For an average you have to cast it, because the result won't necessarily be an integer (whole number):

select a.ticker, a.dt_date, a.volume, avg(cast(b.volume as float))
  from yourtable a
  join yourtable b
    on a.ticker = b.ticker
 where b.dt_date between a.dt_date - 5 and a.dt_date
 group by a.ticker, a.dt_date, a.volume

Fiddle: http://sqlfiddle.com/#!6/ed008/48/0 (thanks to @DaleM for DDL)

I don't know why you would ever do this vs. an analytic function though, especially since you mention wanting to do this in Oracle (which has analytic functions). It would be different if your preferred database were MySQL or a database without analytic functions.

Just to add to the answer, this is how you would achieve the same result in Oracle using analytic functions. Notice how the PARTITION BY acts as the join you're using on ticker. That splits up the results so that the same date shared across multiple tickers don't interfere.

select ticker,
       dt_date,
       volume,
       avg(cast(volume as decimal)) over( partition by ticker
                                          order by dt_date
                                          rows between 5 preceding
                                                   and current row ) as mov_avg
  from yourtable
 order by ticker, dt_date, volume

Fiddle: http://sqlfiddle.com/#!4/0d06b/4/0

Analytic functions will likely run much faster.

20 Day moving average with joins alone

2 Answers2