PostgreSQL CUBE is a sub-clause of the GROUP BY clause which
allows us to generate multiple grouping sets.
As we know grouping set is a set of columns to which you
want to group, but we can not create multiple grouping set in a single statement (multiple
statements may be the cause of slow query performance), CUBE sub-clause resolve
these issues.
Syntax of the CUBE sub-clause:
SELECT
c1,
c2,
c3,
aggregate (c4)
FROM
table_name
GROUP BY
CUBE (c1, c2, c3);
|
The query is the same as GROUP BY except we added a new CUBE
sub-clause after GROUP BY clause. The query statement generates all possible
grouping sets based on the dimension columns specified in CUBE. If the number
of columns specified in the CUBE is n, then you will get the result in 2n
combinations.
Note: PostgreSQL
allows you to perform a partial cube to reduce the number of aggregates
calculated.
We can understand the use of the CUBE sub-clause by example.
Suppose we have a PRODUCTS table with columns like product
name, category, quantity, etc.
CREATE TABLE products
(
product_id
serial NOT NULL,
product_name
text,
category
text,
quantity
numeric
);
-- Inserted some records into PRODUCTS
table
INSERT INTO products(product_name,
category, quantity)
VALUES('prod_1','category_1',100);
INSERT INTO products(product_name,
category, quantity)
VALUES('prod_2','category_2',200);
INSERT INTO products(product_name,
category, quantity)
VALUES('prod_3','category_3',400);
INSERT INTO products(product_name,
category, quantity)
VALUES('prod_4','category_4',500);
|
CUBE sub-clause by example
SELECT
product_name,
category,
SUM(quantity)
FROM
products
GROUP BY
CUBE (product_name,category)
ORDER BY product_name, category;
|
Result:
The following query performs a partial cube:
SELECT
product_name,
category,
SUM(quantity)
FROM
products
GROUP BY
product_name, CUBE(category)
ORDER BY product_name, category;
|
Result:
