Grouping sets, rollup, cube

---- Start

You should be familiar with group by. If you feel that you do not fully understand group by, this article is not suitable for you. I still remember that when I first learned SQL, I always couldn't understand the role of group by. After a long time, I finally understood the true meaning of group. Of course, this is also related to my own stupidity, but group by is really hard to understand. This article describes how to use DB2 grouping sets, rollup, and cube. These keywords are more difficult to understand than group by. Therefore, when reading this article, you must be slow and analyze them carefully, the more you understand, the less you need to remember.

Let's first look at the usage of grouping sets. Please refer to the example below.

Group by grouping sets (A, B, C) equivalent to group by a <br/> Union all <br/> group by B <br/> Union all <br/> group by C 

Literally, grouping sets are the meaning of a group set. From the above example, we can easily understand how to use grouping sets, but we need to pay special attention when using parentheses. Please refer to the example below.

Group by grouping sets (a, B, c) are equivalent to group by a, B, c </P> <p> group by grouping sets (A, (B, c, c) is equivalent to group by a <br/> Union all <br/> group by B, c 

We shouldAll the content in the brackets is regarded as a whole., The whole must be in the same group by statement. For example, in statement 2, B, C are in parentheses, and B and C must be in the same group by statement, don't take them apart and write group by B Union all group by C. That's a big mistake.

We can also use grouping sets multiple times in a group by statement, as shown below:

Group by grouping sets (a) is equivalent to group by a, B, c <br/>, grouping sets (B) <br/>, grouping sets (c) </P> <p> group by grouping sets (a) are equivalent to group by a, B, c <br/>, grouping sets (B, c )) </P> <p> group by grouping sets (a) are equivalent to group by a, B <br/>, grouping sets (B, c) union all <br/> group by a, c 

We can also use them in combination, as shown below:

Group by a is equivalent to group by a <br/>, B, B <br/>, grouping sets (B, c )), c </P> <p> group by a is equivalent to group by a, B, c <br/>, B Union all <br/>, grouping sets (B, c) group by a, B </P> <p> group by a is equivalent to group by a, B, c <br/>, B Union all <br/>, C group by a, B, c <br/>, grouping sets (B, c) <br/> 

Pay special attention to the above 3rd statements.

The following describes the rollup and cube keywords. They are used in a similar way and have a similar effect. They are used to add summary information to the results returned by the Group by statement. You can also say that, they are secondary grouping of grouping results. The following is a simple example:

Select <br/> dept as department, <br/> sex as gender, <br/> AVG (salary) as average salary <br/> from <br/> (<br/> -- name: Gender Department salary <br/> values <br/> ('zhang san', 'male ', 'marketing Department ', 4000), <br/> ('zhao hong', 'male', 'technical Department', 2000), <br/> ('Li si', 'male ', 'marketing Department ', 5000), <br/> ('Li Bai', 'female ', 'Technology Department', 5000), <br/> ('wang 5', 'female ', 'marketing Department ', 3000), <br/> ('wang Lan', 'female', 'technical Department ', 4000) <br/> As employ (name, sex, DEPT, salary) <br/> group by rollup (Dept, sex) <br/> order by department, gender </P> <p> query result: <br/> average gender salary of the Department <br/> female 3000 in the marketing department <br/> male 4500 in the marketing department <br/> null 4000 in the marketing department <br/> female 4500 in the technical department <br/> technical Department Male 2000 <br/> Technical Department null 3666 <br/> null 3833 

It is worth noting that in the preceding rollup statement, the order of department (Dept) and sex (sex) is very important. If we swap the order between them, we will get different results, as follows:

Select <br/> sex as gender, <br/> dept as department, <br/> AVG (salary) as average salary <br/> from <br/> (<br/> -- name: Gender Department salary <br/> values <br/> ('zhang san', 'male ', 'marketing Department ', 4000), <br/> ('zhao hong', 'male', 'technical Department', 2000), <br/> ('Li si', 'male ', 'marketing Department ', 5000), <br/> ('Li Bai', 'female ', 'Technology Department', 5000), <br/> ('wang 5', 'female ', 'marketing Department ', 3000), <br/> ('wang Lan', 'female', 'technical Department ', 4000) <br/> As employ (name, sex, DEPT, salary) <br/> group by rollup (sex, Dept) <br/> order by gender, Department </P> <p> query result: <br/> average salary of gender departments <br/> female marketing department 3000 <br/> female Technical Department 4500 <br/> female null 4000 <br/> male marketing department 4500 <br/> male Technical Department 2000 <br/> male null 3666 <br/> null 3833 

The cube statement returns more content than the rollup statement. The following is the result after replacing the rollup of the preceding statement with the Cube:

Select <br/> dept as department, <br/> sex as gender, <br/> AVG (salary) as average salary <br/> from <br/> (<br/> -- name: Gender Department salary <br/> values <br/> ('zhang san', 'male ', 'marketing Department ', 4000), <br/> ('zhao hong', 'male', 'technical Department', 2000), <br/> ('Li si', 'male ', 'marketing Department ', 5000), <br/> ('Li Bai', 'female ', 'Technology Department', 5000), <br/> ('wang 5', 'female ', 'marketing Department ', 3000), <br/> ('wang Lan', 'female', 'technical Department ', 4000) <br/> As employ (name, sex, DEPT, salary) <br/> group by cube (Dept, sex) <br/> order by department, gender </P> <p> query result: <br/> average gender salary of the Department <br/> female 3000 in the marketing department <br/> male 4500 in the marketing department <br/> null 4000 in the marketing department <br/> female 4500 in the technical department <br/> technical Department Male 2000 <br/> Technical Department null 3666 <br/> Null Female 4000 <br/> null male 3666 <br/> null 3833 

If we replace the order of department (Dept) and sex (sex) in the cube statement, we will get the same result.

---- For more information, see:DB2 SQL

----Statement: indicate the source for reprinting.

---- Last updated on 2010.1.15

---- Written by shangbo on 2010.1.15

---- End