I need 5 MySQL stored functions that will work as follows:
Relevant table structure(add here):
Symbol - varchar(5)
Price - floar
MOVING_AVG(valuecolumn,period) - will return the average of the previous n values for the selected column
EMA(column,period) will compute the exponential moving average (see wikipedia for formula)
CHANGE_P(column) will calculate the % change in the selected column from the previous row
RSI(column,period) will calculate the relative strength index for the specified column (see wikipedia for rsi formula)
ROLLING_SD(column,period) will calculate the standard deviation of values in the specific column for the previous n values (n specified in the period field)
The functions need to be used in queries like the one below:
ORDER BY date,symbol
All functions will need to take into account the symbol, i.e. When computing the percent change, the previous value is the value of price the day before for the same symbol as for which the function is called. Maybe a reference to the symbol column will need to be added to the function parameters.
Also please note that there are gaps in the dates forexample weekends, so date-1 day can not be used, the formulas need to take into account the true previous date existent in the database.
All the functions need to be written using best practice and be fully optimised.