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?
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?
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.
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
--
Andrew Piskorski