One of the features coming in PostgreSQL 9.5 is the triumvirate
ROLLUP nicely covered in Bruce's recent slide deck. The neatest thing about PostgreSQL development is that when improvements happen, they don't just affect the core, but can be taken advantage of by extensions, without even lifting a finger. Such is the case with these features.
One of the things I was curious about with these new set of predicates is Would they work with any aggregate function?. I assumed they would, so decided to put it to the test, by using it with PostGIS ST_Union function (using PostGIS 2.2.0 development). This feature was not something the PostGIS Development group planned on supporting, but by the magic of PostgreSQL, PostGIS accidentally supports it. The grouping sets feature is particularly useful if you want to aggregate data multiple times, perhaps for display using the same dataset. It allows you to do it with a single query that in other PostgreSQL versions would require a UNION query. This is a rather boring example but hopefully you get the idea.
SELECT COALESCE('County ' || countyfp, 'State ' || statefp) As region, ST_Union(the_geom) As geom, count(*) AS cnt FROM cousub GROUP BY GROUPING SETS (countyfp, statefp) ORDER BY countyfp, statefp;
Output looks like:
region | geom | count ------------+-----------------------------------+------- County 001 | 0103000020AD10000001000000EB00... | 3 County 003 | 0103000020AD10000001000000B307... | 5 County 005 | 0103000020AD100000010000005902... | 7 County 007 | 0103000020AD100000010000006F07... | 16 County 009 | 0106000020AD100000020000000103... | 9 State 44 | 0106000020AD100000020000000103... | 40 (6 rows)
Note in the example, I use COALESCE for labeling because in the
GROUPING SETS will force only one of the columns to be filled in that is for that group and the rest will be NULL.