SQL Calculating Moving Average Crossover of variable lengths

Question

I am trying to calculate moving averages crossover with variable dates.

My database is structured:

id
stock_id
date
closing_price

And:

stock_id
symbol

For example, I'd like to find out if the average price going back X days ever gets greater than the average price going back Y days within the past Z days. Each of those time periods is variable. This needs to be run for every stock in the database (about 3000 stocks with prices going back 100 years).

I'm a bit stuck on this, what I currently have is a mess of SQL subqueries that don't work because they cant account for the fact that X, Y, and Z can all be any value (0-N). That is, in the past 5 days I could be looking for a stock where the 40 day average is > than 5, or the 5 > 40. Or I could be looking over the past 40 days to find stocks where the 10 day moving average is > 30 day moving average.

This question is different from the other questions as there is variable short and long dates as well as a variable term.

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

Please find see these earlier posts on Stackoverflow:

These posts have solutions to your question.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 27 '13 at 14:55

GregD

2,691
2
26
38

This question is different from the other questions as there is variable short and long dates as well as a variable term. – user1797484 Apr 28 '13 at 13:46

Gordon Linoff · Answer 2 · 2013-04-27T19:02:34.007

0

I think the most direct way to do a moving average in MySQL is using a correlated subquery. Here is an example:

select p.*,
       (select avg(closing_price)
        from prices p2
        where p2.stock_id = p.stock_id and
              p2.date between p.date - interval x day and pdate
       ) as MvgAvg_X,
       (select avg(closing_price)
        from prices p2
        where p2.stock_id = p.stock_id and
              p2.date between p.date - interval y day and pdate
       ) as MvgAvg_Y
from prices p

You need to fill in the values for x and y.

For performance reasons, you will want an index on prices(stock_id, date, closing_price).

If you have an option for another database, Oracle, Postgres, and SQL Server 2012 all offer much better performing solutions for this problem.

In Postgres, you can write this as:

select p.*,
       avg(p.price) over (partition by stock_id rows x preceding) as AvgX,
       avg(p.price) over (partition by stock_id rows y preceding) as AvgY
from p

edited Apr 27 '13 at 19:02

answered Apr 27 '13 at 15:24

Gordon Linoff

1,198,228
53
572
709

I have the option of Postgres...is it really that much faster? Also that seems to work for part of the problem, but not the full problem. It does not account for Z---that is, this occuring in the past X days. Also, remember X and Y could swap. This is my sticking point. – user1797484 Apr 27 '13 at 18:53
Example: I would want this query to be able to find both the 10 day moving average crossing over 50 day moving average within the past 5 days AND the 50 day crossing UNDER 10 day moving average within the past 5 days. Each of those numbers, X,Y, and Z are variable. – user1797484 Apr 27 '13 at 18:56
You would calculate the moving averages in a subquery and then put the logic in the surrounding query. – Gordon Linoff Apr 27 '13 at 19:03
This query is taking hours to run...wow. With a tuned db – user1797484 Apr 27 '13 at 22:54
@user1797484 . . . Do you have the appropriate indexes? – Gordon Linoff Apr 27 '13 at 22:56
Yes, btree on each prices(stock_id, date, closing_price) – user1797484 Apr 28 '13 at 04:05
There should be three seperate indexes right? Not an index containing all 3 cols? or does it not matter? – user1797484 Apr 28 '13 at 13:37
@user1797484 . . . It matters a great deal. There should be one index with the three columns. – Gordon Linoff Apr 28 '13 at 18:57
Okay, I read up on indexes a bit and created the index with the cols in order: stock_id, date, adj_close. Running this query and I still have not gotten anything returned after HOURS! The box has an 8 core CPU and 16 gigs of ram...the db is stored on a SSD! select p.*, (select avg(adj_close) from daily_data p2 where p2.stock_id = p.stock_id and p2.date between p.date - interval 5 day and p.date ) as MvgAvg_X, (select avg(adj_close) from daily_data p2 where p2.stock_id = p.stock_id and p2.date between p.date - interval 10 day and p.date ) as MvgAvg_Y from daily_data p – user1797484 Apr 28 '13 at 20:03
I feel like I must be missing something..please save me Mr. Gordon Linoff! – user1797484 Apr 28 '13 at 20:03
Actually I just noticed the query you wrote does not even solve the problem will it? Don't we also need to group by the stock_id so it gets us the average price for the stocks separately? – user1797484 Apr 28 '13 at 20:10

SQL Calculating Moving Average Crossover of variable lengths

2 Answers2