Thank you and AndrewP for the pointers - very interesting read. Some clarifications. Coming from my perspective - that of R user, not an API user - it is natural to do more complicated operations in R than to try to write additional functions in C/tcl etc. I do not know how common will such attitude be among SQLite users not using R. (In general R users tend to be statisticians with little knowledge of SQL.) This is why my mail was not an attempt to propose some extensions to core SQLite [it is so good for the intended purpose that adding more stuff can spoil it :-)]
Were any additions for computing considered for SQLITE it seems plausible to argue that the should be easy to implemenent and do not change the spirit of the project. So more like 'adding a log function' rather than something (probably) much bigger like Oracle olap. It could be further argued that, for all such ideas for SQLite extentions, the strategic approach of having 'the core sqlite' and 'the extended sqlite' is the best way to proceed. Any non-conventional extensions (like log or OLAP) could be implemented in the 'extended sqlite' version, used from there - and eventually migrated to the core if there is a sufficient interest. ----- Original Message ----- From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Subject: Re: [sqlite] windowing functions != recursive functions Date: Wed, 12 Oct 2005 22:48:35 -0400 > > "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]> -- ___________________________________________________ Play 100s of games for FREE! http://games.mail.com/
