How-to: Analytic Features - Grouping Sets

Grouping Sets - Instead of a UNION ALL statement (that would require multiple table scans), define a grouping set - the new syntax will result in only a single pass over the base table.

Grouping Sets are specified in the GROUP BY clause.

Syntax:

   SELECT group_function(column1), column2, group_function(column3)…
      FROM table_list
         [WHERE conditions]
            GROUP BY GROUPING SETS (group_by_list)

   SELECT group_function(column1), column2, group_function(column3)…
      FROM table_list
         [WHERE conditions]
            GROUP BY CUBE (group_by_list)

   SELECT group_function(column1), column2, group_function(column3)…
      FROM table_list
         [WHERE conditions]
            GROUP BY ROLLUP (group_by_list)

Examples:

Instead of this UNION query…

   SELECT
       manager_id, null hire_date, count(*)
   FROM 
       employees
   GROUP BY manager_id, 2
 UNION ALL
   SELECT
       null, hire_date, count(*)
   FROM 
       employees
   GROUP BY 1, hire_date

The above rewritten as a Grouping Set…

   SELECT
       manager_id, hire_date, count(*)
   FROM 
       employees
   GROUP BY GROUPING SETS (manager_id, hire_date);

The GROUPING SET clause allows you to specify the EXACT groups.

CUBE

Where a large number of groupings are needed then the CUBE and ROLLUP statements extend this idea by calculating multiple groupings in a single statement.

e.g. GROUP BY CUBE (hire_date, manager_id, product) will produce 2^3 =8 groupings
1) hire_date, manager_id, product
2) hire_date, manager_id
3) hire_date, product
4) manager_id, product
5) hire_date
6) manager_id
7) product
8) Grand Total

GROUP BY CUBE always calculates ALL the combinations - which might be far more than needed.

ROLLUP

e.g. GROUP BY ROLLUP (hire_date, manager_id, product) will produce 4 groupings
1) hire_date, manager_id, product
2) hire_date, manager_id
3) hire_date,
4) Grand Total

GROUP BY ROLLUP calculates all combinations for the first column listed in the ROLLUP clause.

This can be further tuned by using parentheses to remove some of the combinations

e.g. GROUP BY ROLLUP (hire_date, (manager_id, product)) will produce
1) hire_date, manager_id, product
2) hire_date
3) Grand Total

Grouping function

CUBE and ROLLUP will generate NULLs for each dimension at the subtotal levels.
The Grouping() function can be used to identify these rows, which can be very useful when performing additional calculations such as Ranking within a group.

The values returned by grouping() are:
0 for NULL data values
1 for NULL indicating a dimension subtotal

The results of Grouping() can be passed into a decode() e.g.
SELECT … PARTITION BY GROUPING(column1) ..
SELECT … PARTITION BY DECODE(GROUPING(column1), 1, ‘My SubTotal’, column1)) …

Combining (concatenating) Groupings:

The CUBE and ROLLUP clauses can be combined as part of a standard GROUP BY clause

e.g. GROUP BY manager_id, ROLLUP (hire_date, product)

Grouping sets are typically 80 - 90% more efficient at producing sub-totals than equivalent SQL code.

ROLLUP/CUBE can be used with all aggregate functions (MAX, MIN, AVG, etc.)

A HAVING clause will apply to all the data returned.

# Roll Up, Roll up for the Mystery Tour # - The Beatles

Related

Analytic features
Oracle SQL Functions