The fourth day of SQL Learning -- explain and demonstrate SQL with cube, with rollup and grouping

About with cube, with rollup and grouping

View the help document of SQL 2005 to find the specific difference between CUBE and ROLLUP.:

The result set generated by CUBE displays the aggregation of all the combinations of the values in the selected column. The result set generated by ROLLUP displays the aggregation of a certain hierarchy of values in the selected column.

Let's look at the explanation of grouping:

When a row is added by the CUBE or ROLLUP operator, this function causes the output value of the appended column to 1. When a row is not added by the CUBE or ROLLUP operator, this function will cause the output value of the appended column to be 0.

Grouping is allowed only in the selection list associated with the group by clause that contains the CUBE or ROLLUP operator.

When we see that the above explanation is very vague, and Alibaba Cloud does not know how to use it, we will try it through instance operations below:

Create a table first (dbo. leleinfo ):
Copy codeThe Code is as follows:
Create table [dbo]. [PeopleInfo] (
[Id] [int] IDENTITY (1, 1) not null,
[Name] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[Numb] [nchar] (10) COLLATE Chinese_PRC_CI_AS not null,
[Phone] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[FenShu] [int] NULL
) ON [PRIMARY]

Insert data to a table:
Copy codeThe Code is as follows:
Insert into peopleinfo ([name], numb, phone, fenshu) values ('Li Huan ', '20160301', '20160301', 80)
Insert into peopleinfo ([name], numb, phone, fenshu) values ('Li Huan ', '20170101', '1', 90)
Insert into peopleinfo ([name], numb, phone, fenshu) values ('Lee name', '123', '123', 56)
Insert into peopleinfo ([name], numb, phone, fenshu) values ('Lee name', '123', '123', 60)
Insert into peopleinfo ([name], numb, phone, fenshu) values ('wang hua', '123', '123', 80)

Query all the inserted data:
Copy codeThe Code is as follows:
Select * from dbo. PeopleInfo

Result

Operation 1: First try: 1. query all data; 2. Use group by to query all data; 3. Use with cube. Comparison of the three cases

The SQL statement is as follows:
Copy codeThe Code is as follows:
Select * from dbo. leleinfo -- 1, query all data;
Select [name], numb, sum (fenshu) from dbo. PeopleInfo group by [name], numb -- 2, Use group by to query all data;
Select [name], numb, sum (fenshu) from dbo. PeopleInfo group by [name], numb with cube -- 3, with cube. Comparison of the three cases

Result

Result Analysis:

Why does the third (with cube) show null field values? By analyzing the combination of values on the graph, we can find out what happened. Take the third data (Li Huan, null, 170) as an example: it only divides the name "Li Huan" into a group without considering "numb". Therefore, the third piece of data is added, which also shows how 170 came from. This is also true for others. Let's review the help document's explanation: the result set generated by the CUBE shows the aggregation of all the combinations of the values in the selected column, and many of them are found.

Operation 2:1. Use with cube; 2, and use with rollup for comparison

The SQL statement is as follows:
Copy codeThe Code is as follows:
Select [name], numb, sum (fenshu) from dbo. PeopleInfo group by [name], numb with cube -- use with cube.
Select [name], numb, sum (fenshu) from dbo. PeopleInfo group by [name], numb with rollup -- use with rollup.

Result

Result Analysis:

Why is with cube more than with rollup? It is not displayed in the past. It is grouped by [numb] without considering the data of [name. Let's review the help document: the result set generated by ROLLUP displays the aggregation of a certain hierarchy of values in the selected column, so what is the standard for this [level?My guess is: The field closest to group up must be considered in the group.

Example of proof conjecture:

Operation: compare an SQL statement that exchanges fields with two group up statements and an SQL statement that adds a field after group up:

The SQL statement is as follows:
Copy codeThe Code is as follows:
Select [name], numb from dbo. PeopleInfo group by [name], numb with rollup
Select [name], numb from dbo. PeopleInfo group by numb, [name] with rollup
Select [name], numb, phone from dbo. PeopleInfo group by [name], numb, phone with rollup

Result

The result graph shows that the conjecture is correct.

---------------------------------------------------Grouping-------------------------------------------------

Now let's take a look at the grouping instance:

Check the combination of SQL statements and with rollup (the same as with cube ):
Copy codeThe Code is as follows:
Select [name], numb, grouping (numb) from dbo. PeopleInfo group by [name], numb with rollup

Result

Result Analysis:

Combined with the help documentation: When a row is added by the CUBE or ROLLUP operator, this function will cause the output value of the appended column to 1. When a row is not added by the CUBE or ROLLUP operator, this function will cause the output value of the appended column to be 0. It's easy to understand.