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