Oracle rollup & amp; cube & amp; grouping

The following table is available: selects. id, s. name, s. grade, s. clazz, s. scorefromstudentsorderbyid1.rollupgroupbyrollup (column1, column2 ....) if groupbyrollup (A, B, C), first perform groupby on A, B, C, then perform groupby On A, B, then perform groupby On A, and finally do not group

The following table is available: select s. id, s. name, s. grade, s. clazz, s. score from student s order by id 1. rollup group by rollup (column1, column2 ....) if group by rollup (A, B, C), group by is first performed on A, B, and C, then group by is performed on A and B, then perform group by on A, and finally do not group

There is one of the following tables:

select s.id,s.name,s.grade,s.clazz,s.score from student s order by id 

1. rollup

Group by rollup (column1, column2 ....)

If group by rollup (A, B, C), group by is first performed on A, B, and C, then group by is performed on A and B, then perform group by on A, and finally stop group by, that is, right to left for the selected columns (such as A, B, C, if one column is missing at a time, the group by statement is not used.

It can be understood as follows:

Rollup (a, B) Statistical columns include: (a, B), (),()
Rollup (a, B, c) Statistical columns include: (a, B, c), (a, B), (),()

Unless it is not the beginning, there will be a foundation. Otherwise, you will be confused about some conceptual things, so please read the following data and use the data to speak.

The above table uses group by rollup (grade, clazz) to calculate the total score and average score. First, let's look at the results and then analyze them.

Select s. grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by rollup (s. grade, s. clazz)

According to the above statement, group by rollup (grade, clazz) performs group by on grade and clazz first. Then, let's take a look at the columns obtained by group by on grade and clazz.

Select s. grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by s. grade, s. clazz

The result is the column 1, 2, 4, and 5. Then, perform group by on grade to calculate the total score and average score, and check the results and columns.

Select s. grade, ''class, sum (s. score) total score, avg (s. score) average score from student s group by s. grade

The result is the columns 3 and 6 in the column. The total score and average score are calculated without using group.

Select ''grade,'' class, sum (s. score) total score, avg (s. score) average score from student s

The result is the 7th column in.

We can see that

Select s. grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by rollup (s. grade, s. clazz); ------- equivalent to --------- select s. grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by s. grade, s. clazz union allselect s. grade, ''class, sum (s. score) total score, avg (s. score) average score from student s group by s. gradeunion all select ''grade,'' class, sum (s. score) total score, avg (s. score) average score from student s;

2. cube

Group by cube (column1, column2 ....)

Cube (a, B) Statistical columns include: (a, B), (a), (B ),()

Cube (a, B, c) Statistical columns include: (a, B, c), (a, B), (a, c), (B, c), (), (B), (c ),()

Select s. grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by cube (s. grade, s. clazz) order by grade;

And comparison, it is found that there are two more rows, because the cube performs a group by operation on each column, that is, group by (clazz) is added ). in this case, we don't know how to map the results of each column. It's similar to rollup above, but it's just a group.

Select s. grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by cube (s. grade, s. clazz) order by grade; --------- equivalent to -------- select s. grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by s. grade, s. clazz union allselect s. grade, ''class, sum (s. score) total score, avg (s. score) average score from student s group by s. gradeunion allselect ''grade, s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by s. clazzunion all select ''grade,'' class, sum (s. score) total score, avg (s. score) average score from student s

3. grouping

The GROUPING function can accept a column, which must appear in group by and return 0 or 1. If the column value is empty, GROUPING () returns 1; if the column value is not empty, returns 0. GROUPING can only be used in queries using ROLLUP or CUBE. GROUPING () is useful when a value needs to be displayed in the return of a null value.

Select grouping (s. grade), s. grade, grouping (s. clazz), s. clazz class, sum (s. score) total score, avg (s. score) average score from student s group by rollup (s. grade, s. clazz );

In the preceding section, we found that the column values of some statistical rows are empty, which is unfriendly and not nice-looking. You can use grouping for determination. If grouping () is 1, some values are displayed.

Select case when grouping (s. grade) = 1 then' total 'else s. grade end grade, case when grouping (s. clazz) = 1 and grouping (s. grade) = 0 then' subtotal 'else s. clazz end class, sum (s. score) total score, avg (s. score) average score from student s group by rollup (s. grade, s. clazz );

The results look a lot better.

4. grouping_id ()

Grouping () can only receive one parameter column (must appear in group by). grouping_id () can contain multiple parameter columns (must all appear in group ).

Case when grouping (s. clazz) = 1 and grouping (s. grade) = 0 then' subtotal 'else s. clazz end class, -- you can use grouping_id to rewrite ---- case when grouping_id (s. grade, s. clazz) = 1 then' subtotal 'else s. clazz end class,

The result is the same. Here we will make up the texture.

So how is the value of grouping_id (a, B, c...) calculated? In fact, it returns a binary consisting of grouping (x) (x represents any column) values. For example, the grouping_id (s. grade, s. clazz) grouping (s. grade) = 0, grouping (s. clazz) = 1, then grouping_id (s. grade, s. clazz) = 01, which is 1 in decimal format.