PostgreSQL – CUBE

The CUBE operation is used in conjunction with the GROUP BY clause to generate a result set that includes aggregated data for all possible combinations of specified grouping columns. It’s a powerful way to perform multi-dimensional analysis and obtain summary data for various dimensions simultaneously.

The basic syntax of using CUBE in a query is as follows:

SELECT column_list, aggregate_function(column) FROM table_name GROUP BY CUBE (column1, column2, ...);

Here’s a breakdown of the components:

• SELECT column_list: This is the list of columns you want to retrieve in the result set. It can include both the columns you are using for grouping and the columns for which you want to apply aggregate functions.
• aggregate_function(column): This is an aggregate function (like SUM, AVG, COUNT, etc.) that you want to apply to the specified column.
• table_name: The name of the table from which you want to retrieve data.
• GROUP BY CUBE (column1, column2, ...): This is where you specify the columns that you want to use for grouping using the CUBE operation. The query will generate aggregated data for all possible combinations of these columns.

Example: Suppose you have a sales table with columns: “product”, “category”, “region”, and “amount”. You want to obtain aggregated data for total sales amount across different combinations of product, category, and region. You can use the CUBE operation as follows:

SELECT product, category, region, SUM(amount) AS total_sales FROM sales GROUP BY CUBE (product, category, region);

In this example, the query will generate a result set that includes aggregated sales data for all possible combinations of “product,” “category,” and “region,” as well as subtotals for various combinations. It will show the total sales for each dimension and their intersections, ultimately providing a comprehensive summary of the sales data.

The CUBE operation can be very helpful when you need to analyze data across multiple dimensions in a single query.