Hello Andrew,
Thanks for you extensive comments. They are highly appreciated.
Andrew Piskorski wrote:
Hello, I believe MonetDB/SQL does not currently support the SQL:2003 OLAP (aka, windowing) functions. Do you have any plans to add such support, or thoughts on how to do so?
MonetDB originated as the backend engine for the datamining/OLAP functions of SPSS/Data Distilleries (www.datadistilleries.com).
Coul this support be added via MIL, and if so how difficult do you think that would be? E.g., is it something that a dedicated end-user/devloper might plausibly attempt, or would it require deep knowledge of and hacking on the MonetDB core itself?
A dedicated developer should be able to do it at the MIL level. A person with C experience could package key routines as SQL C-extended functions. [documentation pending]
These windowing functions were added as an amendement to the SQL:1999 spec, 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 those SQL specs, and IBM also supported it, so the SQL:2003 syntax and behavior is probably identical to what Oracle and DB2 already have. PostgreSQL 8.0 doesn't yet support it, and I don't know if/when they plan to.
Oracle calls this their "analytic" SQL functions, and has had it starting in 8i with improvements in 9i. I've used it extensively on Oracle, and although its syntax seems kind of klunky and non-intuitive, the functionality is EXTREMELY useful.
Yes we know this functionality, but it is not high on our short term development list.
An alternative track is to couple Mondrian(mondrian.sf.net) with a MonetDB backend. It seems possible, but I still haven't found a student willing to take the challenge. Such a coupling should be easy using SQL/JDBC, but most likely significant improvements are possible if the system would emit MIL directly.
regards, Martin Kersten
Here are a bunch of links about the SQL OLAP/windowing functions:
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
And here are a few more examples of using it. "lead" and "lag" are just the beginning, there is more. I find "dense_rank" particularly useful:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg08223.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg10855.html