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:
id stock_id date adj_close
I have a btree index on:
daily_data(stock_id, date, adj_close) stock_id
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.
The following query has yet to return anything to me after MANY hours of being run:
SET @X:=5; SET @Y:=3; set @Z:=10; set @option:='under'; select stock_id from ( SELECT stock_id, datediff(current_date(), date) days_ago, adj_close, ( SELECT AVG(adj_close) AS moving_average FROM daily_data T2 WHERE ( SELECT COUNT(*) FROM 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 daily_data T2 WHERE ( SELECT COUNT(*) FROM daily_data T3 WHERE date BETWEEN T2.date AND T1.date ) BETWEEN 1 AND @Y ) move_av_2 FROM 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
I am currnetly running Mysql - Innodb but am open to other engines (Postgres/Oracle maybe?) if there would be a significant speed increase.
The main problem is I don't have much hardware available for this.
Currently I am trying to run it on a 1gig ram, virtual private server running Ubuntu.
I have my own desktop I could try and run this on, 8 core CPU/16gig ram/ssd but its Windows only.
Any guidance on how to improve the query / what to run it on would be very helpful.