Use of the cube operator in SQL Server

I. Introduction

The cube operator automatically performs grouping and summarizing operations on the fields listed in the group by clause.

The result set generated by the cube operator is a multi-dimensional dataset. A multi-dimensional dataset is an extension of fact data. Fact data is the data that records individual events. Extensions are built on columns that you plan to analyze. These columns are called dimensions. A multi-dimensional dataset is a result set that contains all possible combinations of cross tables for each dimension.

The cube operator is specified in the group by clause of the SQL statement. The statement selection list should contain dimension columns and aggregate function expressions. The dimension column and keyword with cube should be specified for group. The result set contains all possible combinations of values in the dimension column, and the aggregated values in the basic rows that match the combination of these dimension values.

For example, a simple inventory information table contains the following content:

Project	Color	Quality
Table	Blue	124
Table	Red	223
Chair	Blue	101
Chair	Red	210

 

The returned result set of the following query contains the Total Quality of all possible combinations of items and colors. The SQL statement is as follows:

Select item, color, sum (quality) as total quality

From inventory table

Group by project, color with cube

 

The result is displayed as follows:

Project	Color	Total Quality
Chair	Blue	101.00
Chair	Red	210.00
Chair	(Null)	311.00
Table	Blue	124.00
Table	Red	223.00
Table	(Null)	347.00
(Null)	(Null)	658.00
(Null)	Blue	225.00
(Null)	Red	433.00

 

Ii. Examples

Create a table

Create Table [DBO]. [tb_stuachievement07] (

[Student ID] [int] Null,

[Student name] [nvarchar] (50) Collate chinese_prc_ci_as null,

[Gender] [nvarchar] (50) Collate chinese_prc_ci_as null,

[Language] [int] Null,

[Algebra] [int] Null,

[Ry] [int] Null,

[English] [int] Null,

[Class] [char] (10) Collate chinese_prc_ci_as null

) On [primary]

 

Go

Shows the input data:

 

Enter the following SQL statement:

Select * From tb_stuachievement07

 

Select class, gender, AVG (Chinese) as average Chinese score,

AVG (algebra) as algebra average score, AVG (ry) as geometric average score,

AVG (English) as average English score

From tb_stuachievement07

Group by class, gender

 

Select class, gender, AVG (Chinese) as average Chinese score,

AVG (algebra) as algebra average score, AVG (ry) as geometric average score,

AVG (English) as average English score

From tb_stuachievement07

Group by class, gender with cube

 

The execution result is as follows:

 

 

 

The data in the last graph is parsed as follows:

 

Query the average scores of girls in the second class.

 

The average scores of all subjects of all gender (boys and girls) in the second class are queried.

 

The average scores of all subjects of all gender (boys and girls) in the three classes are queried.

 

Query the average scores of all subjects of all gender in all classes.

 

The average score of boys in all classes is queried.

 

The average score of boys in all classes is queried.

 

In summary, 'null' In the last table does not mean a null value, but all of 'all.

 

Use grouping to differentiate null values
The null value generated by the cube operation brings about a problem: how to distinguish between the null value generated by the cube operation and the null value returned from the actual data? This problem can be solved by the grouping function. If the value in the column comes from the fact data, the grouping function returns 0. If the value in the column is null generated by the cube operation, 1 is returned. In the cube operation, the generated null represents the whole value. You can use the 'select' statement to replace the generated null with the string 'all. Because null in the fact data indicates that the data value is unknown, the SELECT statement can also be decoded as the returned string unknown to replace null from the fact data.

 

Select * From tb_stuachievement07

 

Select class, gender, AVG (Chinese) as average Chinese score,

AVG (algebra) as algebra average score, AVG (ry) as geometric average score,

AVG (English) as average English score

From tb_stuachievement07

Group by class, gender

 

Select case when (grouping (class) = 1) then 'all'

Else isnull (class, 'unknown ')

End as class,

Case when (grouping (gender) = 1) then 'all'

Else isnull (gender, 'unknown ')

End as gender,

AVG (Chinese) as average Chinese score,

AVG (algebra) as algebra average score, AVG (ry) as geometric average score,

AVG (English) as average English score

From tb_stuachievement07

Group by class, gender with cube

 

The execution result is as follows: