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.

Answer 1

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?