wiindow functions and recursive functions

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

wiindow functions and recursive functions

pilot pirx
> I don't see why this is such a great feature.  Without it, worst case,
> you could still write a simple little loop which would issue one
> update statement for each row, all within a single transaction.  No?

that would require writing in C, bindind etc. Or, for some other databases,
writing in some stored procedure language. Here I have that with
stadard-looking sql in a small database. One 'quirk' of the implementation
(i am not sure it was intended) gives enormous additional
programming facility without any additional work :-)

> > Vastly more useful for moving average and the like would be real
> > windowing/grouping functions, like Oracle's "analytic" functions.  I'm

Example of computing moving average with standard sql is in one of my earlier mails.
I think that adding OLAP functions to such a small engine would be an overkill,
especially as most such functionality can be expressed with standard SQL
(admittedly, convoluted a bit).
The owner of the project will ultimately decide.
It may not fit into the 'lite'
image this project is after.


P.S. I remember seeing a public domain code adding
correlation and regression to SQLite, but it was
about 3 years old - it was rather short. Yet, even that
did not make it into the mainstream project.

----- Original Message -----
From: "Andrew Piskorski" <[hidden email]>
To: [hidden email]
Subject: [sqlite] SQL Window/OLAP functions
Date: Wed, 12 Oct 2005 08:34:02 -0400

>
> On Wed, Oct 12, 2005 at 05:12:05AM -0500, pilot pirx wrote:
> > Subject: [sqlite] Please, please do _not_ remove this feature from SQLite...
>
> > While using SQLite for some time (with R package, www.r-project.org)
> > I did admire its functionality and speed. Then I did discover a
> > hidden SQLite feature of immense usefulness - not available in other
> > databases. SQLite can compute Fibonacci numbers! (I will explain why
>
> Transaction visibility features do vary, although often it doesn't
> matter anyway.  E.g., here's a dicussion of how (at least as of early
> 2004), PostgreSQL's docs were quite confused about certain subtleties,
> but what I find interesting, is this was still something that in
> practice had never really mattered to the mostly hard-core RDBMS
> programmers talking about it in that thread:
>
>    http://openacs.org/forums/message-view?message_id=176198
>
> > UPDATE fib SET
> >     val =  (SELECT h1.val FROM fib as h1 where pos = fib.pos - 1) +
> >            (SELECT h2.val FROM fib as h2 where pos = fib.pos - 2)
> > WHERE pos > 2;
>
> I don't see why this is such a great feature.  Without it, worst case,
> you could still write a simple little loop which would issue one
> update statement for each row, all within a single transaction.  No?
>
> > This is an _immensely_ useful functionality when one needs to
> > compute various recursive functions. For example exponential moving
> > average, used frequently in financials. Or Kalman filter (and many
>
> Vastly more useful for moving average and the like would be real
> windowing/grouping functions, like Oracle's "analytic" functions.  I'm
> not thrilled by their particular syntax, but the functionality is
> INCREDIBLY useful.  (And on the other hand, I haven't thought of any
> obviously better syntax, either.)
>
> Hm, an amendement to the SQL:1999 spec added windowing support, and
> SQL:2003 includes that, I think as features T611, "Elementrary OLAP
> functions" and T612, "Advanced OLAP functions".  Apparently Fred Zemke
> of Oracle was the author of that SQL spec, and IBM also supported it,
> so the SQL:2003 syntax and behavior is probably very similar (maybe
> identical?) to what Oracle 8i, 9i, and 10g and IBM's DB2 already have.
> PostgreSQL, as of 8.0, doesn't support it yet.
>
>    http://www.wintercorp.com/rwintercolumns/SQL_99snewolapfunctions.html
>    http://www.ncb.ernet.in/education/modules/dbms/SQL99/OLAP-99-154r2.pdf
>    http://www.wiscorp.com/sql/SQL2003Features.pdf
>    http://troels.arvin.dk/db/rdbms/#select-limit-offset
>    http://www.postgresql.org/docs/8.0/interactive/features.html
>    http://en.wikipedia.org/wiki/SQL
>    http://www.sigmod.org/sigmod/record/issues/0403/E.JimAndrew-standard.pdf
>    http://www.oracle.com/oramag/oracle/01-jul/o41industry.html
>
> SQLite basically supports just SQL-92, it doesn't have any of these
> newer SQL:1999 or SQL:2003 features, right?
>
> Using SQLite in conjunction with a powerful statistical data analysis
> programming language like R is an excellent example of a use where
> windowing functions can be hugely helpful.  Unfortunately, I've never
> had a compelling need to use SQLite for that, otherwise I'd probably
> take a shot at adding support for the SQL:2003 Window/OLAP stuff.  :)
>
> --
> Andrew Piskorski <[hidden email]>
> http://www.piskorski.com/


--
___________________________________________________________
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm