Aggregate

aggregateExpressions does not need to be AggregateExpression. PhysicalAggregation will extract the real AggregateExpression and generate resultExpressions.

Cube GROUP BY a, b, c WITH CUBE is equivalent to GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ) ). Group Count: 2 ^ N (N is the number of group expressions)

Rollup GROUP BY a, b, c WITH ROLLUP is equivalent to GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ) ). Group Count: N + 1 (N is the number of group expressions)

Grouping If user put this one into agg, the corresponding column indicates whether the column is aggregated or not.

GroupingID When aggregates are displayed for a column its value is null. This may conflict in case the column itself has some null values. There needs to be some way to identify NULL in column, which means aggregate and NULL in column, which means value. GROUPING__ID function is the solution to that.

GroupingSets A GROUP BY clause with GROUPING SETS can generate a result set equivalent to generated by a UNION ALL of multiple simple GROUP BY clauses. Cube/Rollup is rewrite to GroupSets in Analyzer.

Pivot

Example: courseSales.cube("course", "year") .agg(grouping("course"), grouping("year"), grouping_id("course", "year"))