SQL - Calculating variable moving average over variable lenghts

Question

FIRST: This question is NOT a duplicate. I have asked this on here already and it was closed as a duplicate. While it is similar to other threads on stackoverflow, it is actually far more complex. Please read the post before assuming it is a duplicate:

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

That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit the query based on input or have multiple queries if needed.

X = 1st moving average term  (N day moving average. Any number 1-N)
Y = 2nd moving average term. (N day moving average. Any number 1-N)
Z = Amount of days back from present to search for the occurance of:
option = Over/Under: (> or <. X passing over Y, or X passing Under Y)


X day moving average passing over OR under Y day moving average
within the past Z days.

My database is structured:

tbl_daily_data

id
stock_id
date
adj_close

And:

tbl_stocks

stock_id
symbol

I have a btree index on:

daily_data(stock_id, date, adj_close)

stock_id

I am stuck on this query and having a lot of trouble writing it. If the variables were fixed it would seem trivial but because X, Y, Z are all 100% independent of each other (could look, for example for 5 day moving average within the past 100 days, or 100 day moving average within the past 5) I am having a lot of trouble coding it.

Please help! :(

Edit: I've been told some more context might be helpful?

We are creating an open stock analytic system where users can perform trend analysis. I have a database containing 3500 stocks and their price histories going back to 1970.

This query will be running every day in order to find stocks that match certain criteria for example:

10 day moving average crossing over 20 day moving average within 5 days

20 day crossing UNDER 10 day moving average within 5 days

55 day crossing UNDER 22 day moving average within 100 days

But each user may be interested in a different analysis so I cannot just store the moving average with each row, it must be calculated.

Any chance you could post a small data set and the output you want from the query under a couple of set of params? — Ian Kenney, Apr 28 '13 at 20:37
Sure, Coming right up... Sample data: http://zbt-psu.org/daily_data.zip — user1797484, Apr 28 '13 at 20:51
Edited the above question with some parameters. Output = For each/every stock that meets the input parameters: The stock's symbol, The two calculated moving averages and the stock's symbol. Outputting the daily price history for each stocks over the selected period would be nice too but thats a bell and whistle that is not required. — user1797484, Apr 28 '13 at 20:54

score 2 · Answer 1 · edited May 23 '17 at 12:04

I am not sure if I fully understand the question ... but something like this might help you get where you need to go: sqlfiddle

SET @X:=5;
SET @Y:=3;
set @Z:=25;
set @option:='under';

select * from ( 

SELECT stock_id,
datediff(current_date(), date) days_ago, 
     adj_close,
     (
     SELECT
          AVG(adj_close) AS moving_average
     FROM
          tbl_daily_data T2
     WHERE
          (
               SELECT
                    COUNT(*)
               FROM
                    tbl_daily_data T3
               WHERE
                    date BETWEEN T2.date AND T1.date
          ) BETWEEN 1 AND @X
     ) move_av_1,
    (
     SELECT
          AVG(adj_close) AS moving_average
     FROM
          tbl_daily_data T2
     WHERE
          (
               SELECT
                    COUNT(*)
               FROM
                    tbl_daily_data T3
               WHERE
                    date BETWEEN T2.date AND T1.date
          ) BETWEEN 1 AND @Y
     ) move_av_2

FROM
     tbl_daily_data T1

where   
datediff(current_date(), date) <= @z
) x
where 
  case when @option ='over'  and move_av_1 > move_av_2 then 1 else 0 end + 
  case when @option ='under' and move_av_2 > move_av_1 then 1 else 0 end  > 0
order by stock_id, days_ago

Based on answer by @Tom H here: How do I calculate a moving average using MySQL?

I don't understand the question either, but +1 for the attempt; it looks great to me. My only suggestion would be to use the OP's parameter names (X,Y,Z, and option) to be more clear. — BellevueBob, Apr 28 '13 at 22:03
Ian: wow, nice work. This looks correct to me. However, I have attempted to run in 20 hours ago. It is still running....hah. — user1797484, Apr 29 '13 at 19:59
Ouch - lots of data I guess! I presume you have date indexed ? — Ian Kenney, Apr 29 '13 at 20:55
Okay so its now May 17th. The query has not yet completed. Yes it is indexed....lol Gonna have to try and improve this... — user1797484, May 17 '13 at 11:56

SQL - Calculating variable moving average over variable lenghts

1 Answers1