v.18.14New Feature

WITH CUBE Modifier for GROUP BY in ClickHouse

Official CH changelog text
The WITH CUBE modifier for GROUP BY (the alternative syntax GROUP BY CUBE(...) is also available). #3172
Introduces the WITH CUBE modifier for GROUP BY, enabling aggregation of result sets over all combinations of specified grouping columns.

Why it matters

This feature simplifies the generation of subtotals and totals across multiple dimensions in aggregation queries. It addresses the need for comprehensive multidimensional summaries, making it easier for users to produce detailed reports without manually specifying multiple grouping sets.

How to use it

Users can apply the feature by adding the WITH CUBE modifier after the GROUP BY clause or by using the alternative syntax GROUP BY CUBE(...). For example:

SELECT column1, column2, SUM(metric) 
FROM table 
GROUP BY column1, column2 WITH CUBE

or
SELECT column1, column2, SUM(metric)
FROM table
GROUP BY CUBE(column1, column2)

Related resources

PreviousNext