> 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" <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org 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 <[EMAIL PROTECTED]> > http://www.piskorski.com/ -- ___________________________________________________________ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm