Oracle PL/SQL之GROUP BY GROUPING SETS

[转自] http://blog.csdn.net/t0nsha/article/details/6538838

使用GROUP BY GROUPING SETS相当于把需要GROUP的集合用UNION ALL联合起来。

当GROUPING SETS里面的分组元素越多时，使用GROUPING SETS比使用UNION ALL性能更好，这可能和使用GROUPING SETS只需要访问一次表有关。

如下两段查询的结果是相等的：

q1（GROUPING SETS）：

1. SELECT   department_id, job_id, manager_id, AVG (salary)
2. FROM employees
3. GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id));
4. DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
5. ------------- ---------- ---------- -----------
6. SH_CLERK          122        3200
7. AC_MGR            101       12000
8. ST_MAN            100        7280
9. ST_CLERK          121        2675
10. SA_REP            148        8650
11. SH_CLERK          120        2900
12. SH_CLERK          124        2825
13. MK_MAN            100       13000
14. AD_PRES                     24000
15. FI_MGR            101       12000
16. SA_REP            146        8500
17. SH_CLERK          123        3475
18. AD_ASST           101        4400
19. IT_PROG           102        9000
20. IT_PROG           103        4950
21. FI_ACCOUNT        108        7920
22. PU_MAN            100       11000
23. ST_CLERK          122        2700
24. SA_REP            145        8500
25. AC_ACCOUNT        205        8300
26. AD_VP             100       17000
27. DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
28. ------------- ---------- ---------- -----------
29. ST_CLERK          120        2625
30. ST_CLERK          124        2925
31. SA_REP            147  7766.66667
32. SA_REP            149  8333.33333
33. HR_REP            101        6500
34. PR_REP            101       10000
35. ST_CLERK          123        3000
36. SH_CLERK          121        3675
37. PU_CLERK          114        2780
38. SA_MAN            100       12200
39. MK_REP            201        6000
40. 110 AC_ACCOUNT                   8300
41. 90 AD_VP                       17000
42. 50 ST_CLERK                     2785
43. 80 SA_REP                 8396.55172
44. 50 ST_MAN                       7280
45. 80 SA_MAN                      12200
46. 110 AC_MGR                      12000
47. 90 AD_PRES                     24000
48. 60 IT_PROG                      5760
49. 100 FI_MGR                      12000
50. DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
51. ------------- ---------- ---------- -----------
52. 30 PU_CLERK                     2780
53. 50 SH_CLERK                     3215
54. 20 MK_MAN                      13000
55. 100 FI_ACCOUNT                   7920
56. SA_REP                       7000
57. 70 PR_REP                      10000
58. 30 PU_MAN                      11000
59. 10 AD_ASST                      4400
60. 20 MK_REP                       6000
61. 40 HR_REP                       6500
62. 52 rows selected.

SELECT department_id, job_id, manager_id, AVG (salary)
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id));

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
SH_CLERK 122 3200
AC_MGR 101 12000
ST_MAN 100 7280
ST_CLERK 121 2675
SA_REP 148 8650
SH_CLERK 120 2900
SH_CLERK 124 2825
MK_MAN 100 13000
AD_PRES 24000
FI_MGR 101 12000
SA_REP 146 8500
SH_CLERK 123 3475
AD_ASST 101 4400
IT_PROG 102 9000
IT_PROG 103 4950
FI_ACCOUNT 108 7920
PU_MAN 100 11000
ST_CLERK 122 2700
SA_REP 145 8500
AC_ACCOUNT 205 8300
AD_VP 100 17000

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
ST_CLERK 120 2625
ST_CLERK 124 2925
SA_REP 147 7766.66667
SA_REP 149 8333.33333
HR_REP 101 6500
PR_REP 101 10000
ST_CLERK 123 3000
SH_CLERK 121 3675
PU_CLERK 114 2780
SA_MAN 100 12200
MK_REP 201 6000
110 AC_ACCOUNT 8300
90 AD_VP 17000
50 ST_CLERK 2785
80 SA_REP 8396.55172
50 ST_MAN 7280
80 SA_MAN 12200
110 AC_MGR 12000
90 AD_PRES 24000
60 IT_PROG 5760
100 FI_MGR 12000

DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
30 PU_CLERK 2780
50 SH_CLERK 3215
20 MK_MAN 13000
100 FI_ACCOUNT 7920
SA_REP 7000
70 PR_REP 10000
30 PU_MAN 11000
10 AD_ASST 4400
20 MK_REP 6000
40 HR_REP 6500

52 rows selected.

q2（UNION ALL）：

1. SELECT   NULL department_id, job_id, manager_id, AVG (salary)
2. FROM employees
3. GROUP BY (job_id, manager_id)
4. UNION ALL
5. SELECT   department_id, job_id, NULL manager_id, AVG (salary)
6. FROM employees
7. GROUP BY (department_id, job_id);
8. DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
9. ------------- ---------- ---------- -----------
10. SH_CLERK          122        3200
11. AC_MGR            101       12000
12. ST_MAN            100        7280
13. ST_CLERK          121        2675
14. SA_REP            148        8650
15. SH_CLERK          120        2900
16. SH_CLERK          124        2825
17. MK_MAN            100       13000
18. AD_PRES                     24000
19. FI_MGR            101       12000
20. SA_REP            146        8500
21. SH_CLERK          123        3475
22. AD_ASST           101        4400
23. IT_PROG           102        9000
24. IT_PROG           103        4950
25. FI_ACCOUNT        108        7920
26. PU_MAN            100       11000
27. ST_CLERK          122        2700
28. SA_REP            145        8500
29. AC_ACCOUNT        205        8300
30. AD_VP             100       17000
31. DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
32. ------------- ---------- ---------- -----------
33. ST_CLERK          120        2625
34. ST_CLERK          124        2925
35. SA_REP            147  7766.66667
36. SA_REP            149  8333.33333
37. HR_REP            101        6500
38. PR_REP            101       10000
39. ST_CLERK          123        3000
40. SH_CLERK          121        3675
41. PU_CLERK          114        2780
42. SA_MAN            100       12200
43. MK_REP            201        6000
44. 110 AC_ACCOUNT                   8300
45. 90 AD_VP                       17000
46. 50 ST_CLERK                     2785
47. 80 SA_REP                 8396.55172
48. 50 ST_MAN                       7280
49. 80 SA_MAN                      12200
50. 110 AC_MGR                      12000
51. 90 AD_PRES                     24000
52. 60 IT_PROG                      5760
53. 100 FI_MGR                      12000
54. DEPARTMENT_ID JOB_ID     MANAGER_ID AVG(SALARY)
55. ------------- ---------- ---------- -----------
56. 30 PU_CLERK                     2780
57. 50 SH_CLERK                     3215
58. 20 MK_MAN                      13000
59. 100 FI_ACCOUNT                   7920
60. SA_REP                       7000
61. 70 PR_REP                      10000
62. 30 PU_MAN                      11000
63. 10 AD_ASST                      4400
64. 20 MK_REP                       6000
65. 40 HR_REP                       6500
66. 52 rows selected.