[GO] Detailed Oracle Advanced grouping functions (ROLLUP, CUBE, GROUPING sets)

Original address: http://blog.csdn.net/u014558001/article/details/42387929

This article mainly explains the main usages of ROLLUP, CUBE, GROUPING sets, which can be understood as the thin usage of the GROUPBY packet function encapsulation, which is equivalent to the combination of multiple union all, but more efficient than the multiple union all.

In fact, these functions in the time of the program development is not much, at least in my years of work has not been used a few times, because now the various development tools/platforms have come with these advanced group statistics, the ease of use and aesthetics are better than these. But if the data is temporarily checked, these functions are good.

Create a test environment

1. Create a table

[SQL]View PlainCopy

2. CreateTable EMP2
4. (
6. ID number, --Employee ID
8. name VARCHAR2, --name
10. Sex VARCHAR2 (2), --gender
12. HireDate Date , --entry dates
14. BASE VARCHAR2, --working mother land
16. DEPT VARCHAR2 (), --Department
18. SAL number -monthly salary
20. );

2. Inserting test data

[SQL]View PlainCopy

2. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
4. VALUES (107, ' Xiao Yue ', ' female ', to_date (' 01-09-2013 ', ' dd-mm-yyyy '), ' Beijing ',' operation ', 9000);
6. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
8. VALUES (108, ' Amy ', ' female ', to_date (' 01-06-2011 ', ' dd-mm-yyyy '), ' Shanghai ',' operations ', 11000);
10. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
12. VALUES (101, ' Zhang San ', ' male ', to_date (' 01-01-2011 ', ' dd-mm-yyyy '), ' Beijing ',' finance ', 8000);
14. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
16. VALUES (102, ' John Doe ', ' male ', to_date (' 01-01-2012 ', ' dd-mm-yyyy '), ' Beijing ',' operations ', 15000);
18. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
20. VALUES (103, ' Harry ', ' male ', to_date (' 01-01-2013 ', ' dd-mm-yyyy '), ' Shanghai ',' operations ', 6000);
22. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
24. VALUES (104, ' Zhao Liu ', ' male ', to_date (' 01-01-2014 ', ' dd-mm-yyyy '), ' Shanghai ',' finance ', 10000);
26. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
28. VALUES (" floret", ' female ', to_date (' 01-08-2014 ', ' dd-mm-yyyy '), ' Shanghai ',' finance ', 4000);
30. INSERT INTO emp2 (ID, NAME, SEX, Hiredate,base, DEPT, SAL)
32. VALUES (106, ' Xiao Jing ', ' female ', to_date (' 01-01-2015 ', ' dd-mm-yyyy '), ' Beijing ',' finance ', 6000);
34. Commit

3. Take a look at the data you just inserted

[SQL]View PlainCopy

1. SELECT * from emp2;

4. First look at the effect of the ordinary group

To count the total wages of each department according to the region

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by Base,dept;

View the results as follows:

ROLLUP (cumulative accumulation)

Rollup is an extension to group by, so it can only appear in the GROUP BY clause, depends on the grouped columns, generates summary data for each grouping, rollup and group by unions, achieves the ability to group by column order, and implements subtotals and totals. Rollup grouping or orderly, all groups first, then each grouping subtotal, the final total.

The order of the columns in the rollup is different, the results of the statistics are different. Because it is grouped by column from right decrement.

For example, group by ROLLUP (A, B, c) will first group by for (A, B, c), then group by (A, B), then (a) group by, and finally group by operations on the whole table

According to the regional statistics of the total wages of each department, according to the work of the parent to summarize, and then total

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Groupbyrollup (base,dept);

The result is equivalent

[SQL]View PlainCopy

2. Select Base,dept,sum (sal) from emp2
4. GROUP BY Base,dept
6. UnionAll
8. Select Base,null,sum (sal) from emp2
10. Group by base,null
12. UnionAll
14. Selectnull,null,sum (sal) from emp2
16. Group by NULL,null
18. Order by

If the rollup order is reversed, the result is as follows:

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by Rollup (dept,base);

If in the actual query, there are subtotals or totals we do not need, then use local rollup, local rollup is the need to set the fixed statistics in group by, rather than in the rollup.

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by Dept,Rollup (base);

Compared to GROUP by rollup (dept,base): The summary of the last line is removed because each rollup is either dept,base, or Dept,null, dept is fixed.

If you only want to see the totals, you can write this:

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by Rollup ((base,dept));

CUBE (cross-list)

Cube is also an extension to the group by operation, which is more granular than the rollup extension, with more combinations of types, rollup is grouped by a combination of columns from right to left, and cube is grouping all possible combinations, so that the grouping is more, covering all possible groupings, and calculates subtotals for all possible groupings.

For cube, the name of the column is the same, the order does not matter, the result is the same, because cube is a combination of various possible cases, but the results of the statistical order of different. But for rollup, the order of the columns is different, the result is different.

For example, cross-counting of working mothers and departments

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by Cube (base,dept)
3. Order by 1, 2;

Part of the cube is similar to some rollup, where the column that needs to be fixed is placed in group by, not in the cube.

If there is only one column in the cube, then the result is consistent with the rollup

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by Dept,Cube (base)
3. Order by1,2;

Rollup and Cube differences:

If it is rollup (a, B, C), the GROUP by order

(A, B, C)

(A, B)

A

Finally, the whole table is groupby operation.

If the group by CUBE (A, B, C), the group by sequence
(A, B, C)

(A, B)

(A, C)

(A),

(B, C)

B

(C),

Finally, the whole table is groupby operation.

GROUPING Sets

For another extension of group by, a subtotal is calculated separately for the grouping column, excluding totals. Used in the same way as rollup and cube, are placed in group by.

For example, it is necessary to count the total of working mother and department separately:

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by grouping sets (base,dept);

The result is:

Equivalent to

[SQL]View PlainCopy

1. Select Base,null,sum (sal) from emp2
2. Group by base,null
3. UnionAll
4. Select null,dept,sum (sal) from emp2
5. Group by Null,dept;

Understand the principle of groupingsets we use him to achieve rollup function is also possible:

[SQL]View PlainCopy

1. Select Base,dept,sum (sal) from emp2
2. Group by Grouping sets ((base,dept), dept,Null);

The effect is as follows:

grouping function

In the above example, the rollup and cube functions are used to generate null for the result set, and the grouping function is used to confirm which field the record is from.

Grouping function usage, with a parameter, the argument is the field name, the result is that the field will be returned to 1, and vice versa return 0

For example:

[SQL]View PlainCopy

1. Select Decode (grouping (base), 1,' all regions ', base) base,
2. Decode (Grouping (dept), 1,' All Departments ', Dept) dept,Sum (sal) from emp2
3. Group by Rollup (dept,base);

For more rollup,cube, GROUPING sets and group by can refer to the examples in Oracle's official documentation

http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8608

[GO] Detailed Oracle Advanced grouping functions (ROLLUP, CUBE, GROUPING sets)