Oracle rollup and cube Analysis

Rollup is an extension of group by, which performs subtotal and total operations. cube contains rollup, which is a more granular subtotal and total. When there is only one field, rollup and CuBE are the same.

You can use grouping to test rollup and Cube: 0; 1. do not view [view indicates that the column is displayed; otherwise, it indicates that the column is not displayed]

① Rollup
(I) read nothing
(Ii) read the first 1st records from left to right.
│ Check the first two
Refer to the first three
② Cube: 0, 1 in full order

Rollup

The columns specified after rollup are separated by commas. The calculation result of rollup is related to the sequence of the columns specified after rollup, because the grouping of rollup is directional. If n columns are specified, n + 1 grouping method is available. You can change the column order to meet different business needs. Be sure to keep in mind the impact of column order on results!

Example:

[SQL]
View plaincopyprint?

1. At 20:32:51 Scott @ orcl (^ ω ^) Select a. dname, B. Job, sum (B. Sal) sum_sal, grouping (A. dname), grouping (B. Job)
   
2. 20:33:06 2 from dept a, EMP B
   
3. 20:33:06 3 where a. deptno = B. deptno
   
4. 20:33:06 4 group
   Rollup (A. dname, B. Job)
5. 20:33:08 5/
7. Dname job sum_sal grouping (A. dname)
   Grouping (B. Job)
8. --------------------------------------------------------------
   
9. Sales clerk 950 0 0
   
10. Sales Manager 2850 0 0
11. Sales salesman 5600 0 0
    
12. Sales 9400 0 1
13. Research clerk 1200 0 0
    
14. Research Analyst 7000 0 0
15. Research Manager 2975 0 0
    
16. Research 11175 0 1
17. Accounting Clerk 1300 0 0
    
18. Accounting Manager 2450 0 0
19. Accounting President 5000 0 0
    
20. Accounting 8750 0 1
21. 29325 1 1
    
23. 13 rows have been selected.

At 20:32:51 Scott @ orcl (^ ω ^) Select. dname, B. job, sum (B. sal) sum_sal, grouping (. dname), grouping (B. job) 20:33:06 2 from dept a, EMP B20: 33: 06 3 where. deptno = B. deptno20: 33: 06 4 group by rollup (. dname, B. job) 20:33:08 5/dname job sum_sal grouping (. dname) Grouping (B. job) ---------- ----------------- --------------- sales clerk 950 0 0 Sales Manager 2850 0 0 Sales salesman 5600 0 Sales 9400 0 1 Research clerk 1200 0 0 research analyst 7000 0 0 Research Manager 2975 0 research 11175 0 1 accounting clerk 1300 0 0 Accounting Manager 2450 0 0 accounting President 5000 0 0 accounting 8750 0 1 29325 1 1 13 rows selected.

Explanation: Do not look at anything (); from left to right, first look at the first)

You can remove columns that do not require subtotal or aggregate from rollup, and leave the columns to be subtotal in rollup.

For example:

[SQL]
View plaincopyprint?

1. At 20:48:37 Scott @ orcl (^ ω ^) Select a. dname, B. Job, sum (B. Sal) sum_sal, grouping (A. dname), grouping (B. Job)
   
2. 20:57:43 2 from dept a, EMP B
   
3. 20:57:43 3 where a. deptno = B. deptno
   
4. 20:57:43 4 group by A. dname, rollup (B. Job)
   
5. 20:57:45 5/
7. Dname job sum_sal
   Grouping (A. dname) Grouping (B. Job)
   
8. ----------------------------------------------------------------------------------------
   
9. Sales clerk 950 0 0
   
10. Sales Manager 2850 0 0
    
11. Sales salesman 5600 0 0
    
12. Sales 9400 0 1
    
13. Research clerk 1200 0 0
    
14. Research Analyst 7000 0 0
    
15. Research Manager 2975 0 0
    
16. Research 11175 0 1
    
17. Accounting Clerk 1300 0 0
    
18. Accounting Manager 2450 0 0
    
19. Accounting President 5000 0 0
    
20. Accounting 8750 0 1
    
22. 12 rows have been selected.

At 20:48:37 Scott @ orcl (^ ω ^) Select. dname, B. job, sum (B. sal) sum_sal, grouping (. dname), grouping (B. job) 20:57:43 2 from dept a, EMP B20: 57: 43 3 where. deptno = B. deptno20: 57: 43 4 group by. dname, rollup (B. job) 20:57:45 5/dname job sum_sal grouping (. dname) Grouping (B. job) ---------------------------- ------------------ ---------- ----------------- --------------- sales clerk 950 0 0 Sales Manager 2850 0 0sal Es salesman 5600 0 0 Sales 9400 0 1 Research clerk 1200 0 0 research analyst 7000 0 0 Research Manager 2975 0 research 11175 0 0 1 accounting clerk 1300 0 0 Accounting Manager 2450 0 0 accounting President 5000 0 0 accounting 8750 0 1 12 rows have been selected.

Cube

Unlike rollup, the computation result and sequence of the cube are independent. If n columns exist, the grouping method has the Npower of 2.

[SQL]
View plaincopyprint?

1. At 20:57:46 Scott @ orcl (^ ω ^) Select a. dname, B. Job, sum (B. Sal) sum_sal, grouping (A. dname), grouping (B. Job)
   
2. 21:27:03 2 from dept a, EMP B
   
3. 21:27:03 3 where a. deptno = B. deptno
   
4. 21:27:03 4 group
   Cube (A. dname, B. Job)
5. 21:27:04 5/
7. Dname job sum_sal
   Grouping (A. dname) Grouping (B. Job)
   
8. ----------------------------------------------------------------------------------------
   
9. 29325 1 1
   
10. Clerk 3450 1 0
    
11. Analyst 7000 1 0
    
12. Manager 8275 1 0
    
13. Salesman 5600 1 0
    
14. President 5000 1 0
    
15. Sales 9400 0 1
    
16. Sales clerk 950 0 0
    
17. Sales Manager 2850 0 0
    
18. Sales salesman 5600 0 0
    
19. Research 11175 0 1
    
20. Research clerk 1200 0 0
    
21. Research Analyst 7000 0 0
    
22. Research Manager 2975 0 0
    
23. Accounting 8750 0 1
    
24. Accounting Clerk 1300 0 0
    
25. Accounting Manager 2450 0 0
    
26. Accounting President 5000 0 0
    
28. 18 rows have been selected.

At 20:57:46 Scott @ orcl (^ ω ^) Select. dname, B. job, sum (B. sal) sum_sal, grouping (. dname), grouping (B. job) 21:27:03 2 from dept a, EMP B21: 27: 03 3 where. deptno = B. deptno21: 27: 03 4 group by cube (. dname, B. job) 21:27:04 5/dname job sum_sal grouping (. dname) Grouping (B. job) ---------------------------- ------------------ ---------- ----------------- --------------- 29325 1 1 clerk 3450 1 0 analyst 7000 1 0 man Ager 8275 1 0 salesman 5600 1 0 President 5000 1 0 Sales 9400 0 1 sales clerk 950 0 0 Sales Manager 2850 0 0 Sales salesman 5600 0 research 11175 0 1 Research clerk 1200 0 0 research Analyst 7000 0 0 Research Manager 2975 0 0 accounting 8750 0 1 accounting clerk 1300 0 0 0 Accounting Manager 2450 0 0 accounting President 5000 0 0 0 selected 18 rows.

Note: compared with the result of rollup, all possible grouping of cube goes through.

You can remove the aggregate and some unwanted subtotal, which are implemented through some cubes. Some cubes are more useful than some rollup.

[SQL]
View plaincopyprint?

1. At 21:27:06 Scott @ orcl (^ ω ^) Select a. dname, B. Job, sum (B. Sal) sum_sal, grouping (A. dname), grouping (B. Job)
   
2. 21:32:20 2 from dept a, EMP B
   
3. 21:32:20 3 where a. deptno = B. deptno
   
4. 21:32:20 4 group by A. dname, cube (B. Job)
   
5. 21:32:21 5/
7. Dname job sum_sal
   Grouping (A. dname) Grouping (B. Job)
   
8. ----------------------------------------------------------------------------------------
   
9. Sales 9400 0 1
   
10. Sales clerk 950 0 0
    
11. Sales Manager 2850 0 0
    
12. Sales salesman 5600 0 0
    
13. Research 11175 0 1
    
14. Research clerk 1200 0 0
    
15. Research Analyst 7000 0 0
    
16. Research Manager 2975 0 0
    
17. Accounting 8750 0 1
    
18. Accounting Clerk 1300 0 0
    
19. Accounting Manager 2450 0 0
    
20. Accounting President 5000 0 0
    
22. 12 rows have been selected.

Address: http://blog.csdn.net/linwaterbin/article/details/7985717