I'm curious if anyone has ever looked into adding OLAP functionality (per
the SQL99 specs) into PostGreSQL. I don't actually own the proper SQL99
specifications, and since the newer sql2003 ones are coming out I don't know
which to purchase. Could someone point me in the right direction? I've
looked in the mailing lists and the docs and found some interest in olap
like functionality, but not like what I found in other databases (such as
Oracle and DB2).
More specifically I would like to add grouping sets, and the CUBE and ROLLUP
operators, into postgresql. Since modifying such the GROUP BY operation
would necessitate changing the query structure, wouldn't that affect the
query rewrites and genetic optimizer? On a superficial level yes, but would
any existing query rewriting care about additional grouping being done in a
GROUP BY operation? More specifically, what would changing the query
structure affect by making the GROUP BY clause a list of lists (the list of
the grouping sets) instead of an expression list as it currently is?
An example of a ROLLUP result might be (pseudoresults..):
CREATE TABLE SALES_SUMMARY (NAME TEXT, DEPARTMENT TEXT, SALES INTEGER);
-- populate with data
SELECT DEPARTMENT, NAME, SUM(SALES) FROM SALES_SUMMARY GROUP BY
ROLLUP(DEPARTMENT,NAME);
DEPARTMENT NAME SUM(SALES)
-------------- -------- ----------
Dept one Bob 13
Dept one Jeff 12
Dept one NULL 25
Dept two Jim 10
Dept two Mary 11
Dept two NULL 21
NULL NULL 46
-- Where the rows with NULLs represent the subtotals and grandtotals,
respectively.
Any thoughts?
Along the same vein, window partitioning would be nice for aggregates.
Aggregate expressions like: RANK() OVER (PARTITION BY DEPARTMENT ORDER BY
SALARY DESC). They get rid of a lot of subselect operations quite nicely.
These are not simple projects, I know. There are a lot of features in high
databases I would like to have in open source tools, and I would like to
make a contribution towards getting them there ;)
PS - ...no, I won't even mention materialized views...
-----------------
Robert Bedell
