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), the CUBE sub-clause resolves these issues.
Syntax of the PostgreSQL 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 PostgreSQL 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: