CUBE is used to generate aggregation of all dimension combinations, suitable for cross-analysis; ROLLUP is gradually summarized at hierarchical levels, suitable for data with hierarchical relationships. CUBE generates a total of 8 combinations according to Region, Product, and Quarter, while ROLLUP generates a summary of year, month, day and other levels according to Year, Month, and Day. CUBE is suitable for viewing all cross-dimensional results, ROLLUP is suitable for displaying hierarchies. Note that CUBE may cause the result set to explode, and ROLLUP depends on the field order. The summary row can be identified through the GROUPING() function, and the total row is named with COALESCE to improve readability.

SQL's CUBE and ROLLUP are two very practical multi-dimensional aggregation tools, especially useful when doing data reporting and analysis. They can generate summary results at multiple levels in a query, saving the hassle of multiple grouping statistics.

If you are dealing with multi-dimensional data such as sales, inventory, and user behavior, mastering these two keywords will allow you to complete tasks more efficiently.

What is CUBE? When to use it?

CUBE is used to generate aggregate results for all possible combinations of dimensions. It works for you to see a summary of all possible cross-dimensionalities.

For example, if you want to count sales in different regions, product categories and quarters, CUBE can return the sum of all the following combinations at one time:

• Each Region Product Quarter
• Each Region Product
• Each Region Quarter
• Each Product Quarter
• Separate Region, Product, Quarter
• Total of all data

Syntax example:

 SELECT Region, Product, Quarter, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY CUBE (Region, Product, Quarter);

Tip: If you have N fields placed in CUBE, 2^N combinations will be generated. So don't have too many fields, otherwise the result will explode.

What is ROLLUP doing?

ROLLUP is more suitable for data with hierarchical relationships. It does not exhaust all combinations like CUBE , but gradually "volume" from the finest grain to the highest level in the order you list the fields.

For example, if you press (Year, Month, Day) to do ROLLUP, you will get:

• Year Month Day
• Year Month
• Year
• Total (no Year)

This structure is very suitable for analysis of time series or organizational structure classes.

Syntax example:

 SELECT Year, Month, Day, SUM(Sales) AS DailyTotal
FROM Sales
GROUP BY ROLLUP (Year, Month, Day);

Note: ROLLUP results depend on the field order. Put the most important level in front, and the latter as sub-levels.

Comparison of practical application scenarios between CUBE and ROLLUP

In addition, some databases also support GROUPING SETS , which allows you to manually specify the required combinations, making it more flexible.

A few tips to make it easier for you to use

• Identifying null values means : In the results of CUBE or ROLLUP, some columns may have NULL, indicating that the dimension is ignored. For example, in a row Region is NULL, indicating that this is a total across regions.

• In conjunction with the GROUPING() function : You can use this function to determine whether a column is a summary row. For example, GROUPING(Region) returns 1, which means that this row is not the data of a specific region.

• Name summary line : For easy reading, you can add a CASE WHEN to SELECT to label the summary line:

   SELECT 
    COALESCE(Region, 'All Regions') AS Region,
    ...

  ---

  Basically that's it. CUBE and ROLLUP do not seem complicated, but they do save a lot of repeated queries and splicing in multi-dimensional analysis. The key is to understand their logical differences and choose the right way based on business needs.

  The above is the detailed content of SQL Cube and Rollup for Multi-Dimensional Aggregation. For more information, please follow other related articles on the PHP Chinese website!