Fermé

5 MySQL functions (moving average, sd, etc)

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:

SELECT date,symbol,price,ROLLING_SD(price,5)

FROM prices

WHERE year(date)>2009

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.

Compétences : MySQL

Voir plus : mysql moving average, mysql exponential moving average, mysql moving average function, mysql moving average gaps, mysql ema formula, mysql function moving average, 5 below, mysql moving average formulas, exponential moving average mysql, moving from, day period, one percent, mysql index, ema, mysql structure, mysql specific, calculate strength, period rsi, table queries, mysql date field, year date column, price formula, average function, exponential moving average, database selected row

Concernant l'employeur :
( 1 commentaire ) Horsham, United Kingdom

N° du projet : #1070374