Use of rollup and cube Operators

Data aggregation is one of the tasks frequently used by databases. In addition to group by grouping, Aggregate functions, and Union all, SQL also provides group by col1, col2 .. with cube | rollup, and compute by. This article describes how to use the cube and rollup operators to achieve hierarchical data aggregation.

If object_id ('tb') is not null
Drop table TB;
Go
Create Table TB
(
Provider varchar (10)
, Materialno varchar (3)
, Quantity int

);
Insert into TB select 'canon', '001', 500
Union all select 'canon', '001', 200
Union all select 'canon', '002 ', 100
Union all select 'canon', '002 ', 300
Union all select 'sony ', '001', 200
Union all select 'ibm ', '002', 100
Union all select 'ibm ', '001', 600
Union all select 'ibm ', '001', 200

 

-- 1. Use rollup to summarize data
Select provider, materialno, sum (Quantity) as sum_quantity from TB
Group by provider, materialno
With Rollup
/* -- Result
Provider materialno sum_quantity
--------------------------------
Canon 001 700
Canon 002 400
Canon null 1100
IBM 001 800
IBM 002 100
IBM null 900
Sony 001 200
Sony null 200
Null null 2200
*/
The result shows that the rollup operator summarizes the provider level. The provider field in the result set is not empty, and the materialno field is empty, when both provider and materialno are empty, data is summarized for all providers.

 

-- 2. Use cube to summarize data
Select provider, materialno, sum (Quantity) as sum_quantity from TB
Group by provider, materialno
With cube
/* -- Result
Provider materialno sum_quantity
--------------------------------
Canon 001 700
Canon 002 400
Canon null 1100
IBM 001 800
IBM 002 100
IBM null 900
Sony 001 200
Sony null 200
Null null 2200
Null 001 1700
Null 002 500
*/
Compared with rollup, the result shows that the result set of cube adds two rows based on the result set of rollup,
Null 001 1700
Null 002 200
That is, different materialno are also summarized.

 

-- 3. Differences between rollup and cube
. The result set generated by rollup is the aggregation of a dimension in the selected column. In the preceding example, the provider dimension is summarized.
The result set generated by the cube is the aggregation of all dimensions of the selected column's median. In the preceding example, all dimensions of provider and materialno are summarized.
 
-- 4. Use the grouping function to process the null values generated by the summary.
For the null value generated by using rollup and cube to summarize data, it is easy to cause ambiguity when the actual data is null. For this, we can use the grouping function to distinguish it.
When null is generated by rollup or cube, the value returned by the grouping function is 1. If null comes from the actual data, the value returned by the grouping function is 0.
Select
Case when (grouping (provider) = 1) then 'all'
Else provider end as provider,
Case when (grouping (materialno) = 1) then 'all'
Else materialno end as materialno,
Sum (Quantity) as sum_quantity from TB
Group by provider, materialno
With cube
/* -- Result
Provider materialno sum_quantity
--------------------------------
Canon 001 700
Canon 002 400
Canon all 1100
IBM 001 800
IBM 002 100
IBM all 1, 900
Sony 001 200
Sony all 200
All all 2200
All 001 1700
All 002 500
*/

 

-- 5. Use the having clause to worry about useless data. For example, in this example, the total and materialno data are filtered out.
Select
Case when (grouping (provider) = 1) then 'all'
Else provider end as provider,
Case when (grouping (materialno) = 1) then 'all'
Else materialno end as materialno,
Sum (Quantity) as sum_quantity from TB
Group by provider, materialno with cube
Having grouping (provider) <> 1
/* -- Result
Provider materialno sum_quantity
--------------------------------
Canon 001 700
Canon 002 400
Canon all 1100
IBM 001 800
IBM 002 100
IBM all 1, 900
Sony 001 200
Sony all 200
*/

 

-- Filter out the subtotal data and final total data of different materialno with the same provider
Select
Case when (grouping (provider) = 1) then 'all'
Else provider end as provider,
Case when (grouping (materialno) = 1) then 'all'
Else materialno end as materialno,
Sum (Quantity) as sum_quantity from TB
Group by provider, materialno with cube
Having grouping (materialno) = 0

 

Summary: The cube and rollup provide the possibility to summarize a multi-dimensional dataset. To summarize all dimensions, use the cube operator to summarize a dimension, and then use the rollup operation.
Note that with cube | rollup must be followed by the group by col1 and col2 columns. Then, you can use the having clause with the grouping function to filter unwanted result sets.