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 -----
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!

Reply via email to