"pilot pirx" <[EMAIL PROTECTED]> wrote: > Now, for the recursive function like exponential moving average > the defintion is that > > ema(i+1) = val(i) * coef + ema(i) * (1-coef). > > That is I have to know the previous value of both EMA _and_ VALUE > (while for moving avearage I need to know _only_ the previous value(s) > of VALUE. >
You could write an "ema()" function for SQLite using the scarcely documented API functions sqlite3_get_auxdata() and sqlite3_set_auxdata(). (Those routines were intended to allow functions like "regexp" to compile a constant regular expression once and then reused the compiled regular expression on subsequent calls. But they have never been used for anything, as far as I am aware.) The ema() function would work like this: SELECT ema(x, 0.45) FROM table1; Where 0.45 is the "coef". I was wondering if it would be possible to write a "prev()" function that returned the value of a column in the result set from the previous row. prev() could be used to implement ema() in pure SQL. But, alas, I do not see how you could write a general-purpose prev() function using the current API. Some kind of extension would be required, I think. -- D. Richard Hipp <[EMAIL PROTECTED]>
