Rollup

The Rollup operation takes n number of columns and produces n + 1 aggregates.

For example, given a Rollup on columns A, B, & C, it computes the requested aggregates for the following combinations of columns:

• {ABC} - unique A B C triplets
• {AB } - unique A B pairs
• {A  } - unique A values
• {   } - all values

The Rollup 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 ROLLUP function. Be mindful that Rollup is an expensive operation.

Grouping and Nulls

If using the Rollup 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 Rollup operation. There will be an example of this in the Example section.

Example

The following examples use the REST /aggregate/groupby endpoint to perform a Rollup operation. For SQL examples, see the ROLLUP section in SQL Support.

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

• Each market sector & stock symbol pair
• Each market sector
• All sectors and symbols

{
  "table_name":"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"
  ],
  "offset": 0,
  "limit": -9999,
  "encoding": "json",
  "options": {
    "rollup": "(Sector, Symbol)"
  }
}

Limitations

• Store-only columns cannot be grouped via Rollup 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 Rollup function must be listed as columns in the column_names parameter
• As the Rollup function is executed via /aggregate/groupby, all Aggregation Limitations also apply