Grouping Sets

The grouping sets operation calculates aggregates over any user-specified dimensions. It offers much more flexibility and customizability over the Cube and Rollup functions, while allowing both as possible dimensions over which aggregation can be performed.

For example, given grouping sets of B, (D,E), & (ROLLUP(A,C)), it computes the requested aggregates for the following combinations of columns:

  • Group B:
    • { B   } - unique B values
  • Group (D,E):
    • {   DE} - unique D E pairs
  • Group ROLLUP(A,C):
    • {A C  } - unique A C pairs
    • {A    } - unique A values
    • {     } - all values

The grouping sets operation is an aggregate function that can be invoked natively in the options map of the /aggregate/groupby endpoint. It is also available via the SQL GROUPING SETS function. Be mindful that grouping sets is an expensive operation.

Grouping and Nulls

If using the grouping sets operation with a column that contains null values, it may not be apparent which aggregations pertain to the column's null values and which pertain to all the column's values, as both will be represented by null in the result set. To avoid this confusion, it's recommended to use the GROUPING() function to make a distinction between null values in the data and null grouping values generated by the grouping sets operation. There will be an example of this below.

Examples

The following example uses the REST /aggregate/groupby endpoint to perform a grouping sets operation. For SQL examples, see the GROUPING SETS section in SQL Support.

The following example uses the Python API to perform a grouping sets operation. For SQL examples, see the GROUPING SETS section in SQL Support.

The following request will aggregate the average opening stock price for these groups:

  • Each market sector
  • Each stock symbol
  • All sectors and symbols
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

h_db.aggregate_group_by(
    table_name = "demo.stocks",
    column_names = [
        "Sector",
        "Symbol",
        "IF(Grouping(Sector) = 0, Sector, '<ALL SECTORS>') as SectorGroup",
        "IF(Grouping(Symbol) = 0, Symbol, '<ALL SYMBOLS>') as SymbolGroup",
        "AVG(Open) as AvgOpen"
    ],
    options = {"grouping_sets": "((Sector), (Symbol), ())"}
)

Limitations

  • Store-only columns cannot be grouped via grouping sets call, nor can aggregation functions be applied to them
  • The maximum number of dimensions that can be computed is 256; the maximum number of columns that can be aggregated is 61
  • The column(s) used in the grouping sets operation must be listed as columns in the column_names parameter
  • As the grouping sets operation is executed via /aggregate/groupby, all Aggregation Limitations also apply