Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

daily_data

id
stock_id
date
adj_close

And:

stocks

stock_id
symbol

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.

share|improve this question
    
What is the query supposed to do? (please explain and format it better) Also add the CREATE TABLE statement and the EXPLAIN plan. –  ypercube Apr 30 '13 at 10:23
    
It seems that your moving averages are not per stock but for all the stocks and all the date range. Is that what you want to calculate? –  ypercube Apr 30 '13 at 11:32
    
Yes. Its screen stocks. So it will calculate moving average for all the stocks looking for when X-day moving average passes over/under Y day moving average within the past Z days. –  coderego Apr 30 '13 at 14:32
    
What I mean is that there is no correlation based on stock_id between the subqueries (tables T2 and T3) and the external query (table T1). If that's what you want, the query can be simplified. –  ypercube Apr 30 '13 at 14:43
    
Ypercube: thanks for offering to help. I might be too close to this thing and cant see an easy solution :). I will edit the main post with the details you are asking for –  coderego Apr 30 '13 at 16:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.