PostgreSQL CUBE
Summary: in this tutorial, you will learn how to use the PostgreSQLCUBE
to generate multiple grouping sets.
Introduction to the PostgreSQL CUBE
PostgreSQLCUBE
is a subclause of theGROUP BY
clause. TheCUBE
allows you to generate multiple grouping sets.
A grouping set is a set of columns to which you want to group. For more information on the grouping sets, check it out theGROUPING SETS
tutorial.
The following illustrates the syntax of theCUBE
subclause:
SELECT c1, c2, c3, aggregate (c4)FROM table_nameGROUP BY CUBE (c1, c2, c3);
In this syntax:
- First, specify the
CUBE
subclause in theGROUP BY
clause of theSELECT
statement. - Second, in the select list, specify the columns (dimensions or dimension columns) which you want to analyze andaggregation function expressions.
- Third, in the
GROUP BY
clause, specify the dimension columns within the parentheses of theCUBE
subclause.
The query generates all possible grouping sets based on the dimension columns specified inCUBE
. TheCUBE
subclause is a short way to define multiple grouping sets so the following are equivalent:
CUBE(c1,c2,c3)GROUPINGSETS ( (c1,c2,c3), (c1,c2), (c1,c3), (c2,c3), (c1), (c2), (c3), () )
In general, if the number of columns specified in theCUBE
isn
, then you will have 2ncombinations.
PostgreSQL allows you to perform a partial cube to reduce the number of aggregates calculated. The following shows the syntax:
SELECT c1, c2, c3, aggregate (c4)FROM table_nameGROUP BY c1, CUBE (c1, c2);
PostgreSQL CUBE examples
We will use thesales
table created in theGROUPING SETS
tutorial for the demonstration.
The following query uses the
CUBE
subclause to generate multiple grouping sets:
SELECT brand, segment, SUM (quantity)FROM salesGROUP BY CUBE (brand, segment)ORDER BY brand, segment;
Here is the output:
The following query performs a partial cube:
SELECT brand, segment, SUM (quantity)FROM salesGROUP BY brand, CUBE (segment)ORDER BY brand, segment;
In this tutorial, you have learned how to use the PostgreSQL
CUBE
to generate multiple grouping sets.
Last updated on