Oracle PL/SQL之GROUP BY GROUPING SETS

使用GROUP BY GROUPING SETS相當於把需要GROUP的集合用UNION ALL聯合起來。

當GROUPING SETS裡面的分組元素越多時，使用GROUPING SETS比使用UNION ALL效能更好，這可能和使用GROUPING SETS只需要訪問一次表有關。

如下兩段查詢的結果是相等的：

 

q1（GROUPING SETS）：

SELECT department_id, job_id, manager_id, AVG (salary)<br /> FROM employees<br />GROUP BY GROUPING SETS ((department_id, job_id), (job_id, manager_id));</p><p>DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)<br />------------- ---------- ---------- -----------<br /> SH_CLERK 122 3200<br /> AC_MGR 101 12000<br /> ST_MAN 100 7280<br /> ST_CLERK 121 2675<br /> SA_REP 148 8650<br /> SH_CLERK 120 2900<br /> SH_CLERK 124 2825<br /> MK_MAN 100 13000<br /> AD_PRES 24000<br /> FI_MGR 101 12000<br /> SA_REP 146 8500<br /> SH_CLERK 123 3475<br /> AD_ASST 101 4400<br /> IT_PROG 102 9000<br /> IT_PROG 103 4950<br /> FI_ACCOUNT 108 7920<br /> PU_MAN 100 11000<br /> ST_CLERK 122 2700<br /> SA_REP 145 8500<br /> AC_ACCOUNT 205 8300<br /> AD_VP 100 17000</p><p>DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)<br />------------- ---------- ---------- -----------<br /> ST_CLERK 120 2625<br /> ST_CLERK 124 2925<br /> SA_REP 147 7766.66667<br /> SA_REP 149 8333.33333<br /> HR_REP 101 6500<br /> PR_REP 101 10000<br /> ST_CLERK 123 3000<br /> SH_CLERK 121 3675<br /> PU_CLERK 114 2780<br /> SA_MAN 100 12200<br /> MK_REP 201 6000<br /> 110 AC_ACCOUNT 8300<br /> 90 AD_VP 17000<br /> 50 ST_CLERK 2785<br /> 80 SA_REP 8396.55172<br /> 50 ST_MAN 7280<br /> 80 SA_MAN 12200<br /> 110 AC_MGR 12000<br /> 90 AD_PRES 24000<br /> 60 IT_PROG 5760<br /> 100 FI_MGR 12000</p><p>DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)<br />------------- ---------- ---------- -----------<br /> 30 PU_CLERK 2780<br /> 50 SH_CLERK 3215<br /> 20 MK_MAN 13000<br /> 100 FI_ACCOUNT 7920<br /> SA_REP 7000<br /> 70 PR_REP 10000<br /> 30 PU_MAN 11000<br /> 10 AD_ASST 4400<br /> 20 MK_REP 6000<br /> 40 HR_REP 6500</p><p>52 rows selected.

 

q2（UNION ALL）：

SELECT NULL department_id, job_id, manager_id, AVG (salary)<br /> FROM employees<br />GROUP BY (job_id, manager_id)<br />UNION ALL<br />SELECT department_id, job_id, NULL manager_id, AVG (salary)<br /> FROM employees<br />GROUP BY (department_id, job_id);</p><p>DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)<br />------------- ---------- ---------- -----------<br /> SH_CLERK 122 3200<br /> AC_MGR 101 12000<br /> ST_MAN 100 7280<br /> ST_CLERK 121 2675<br /> SA_REP 148 8650<br /> SH_CLERK 120 2900<br /> SH_CLERK 124 2825<br /> MK_MAN 100 13000<br /> AD_PRES 24000<br /> FI_MGR 101 12000<br /> SA_REP 146 8500<br /> SH_CLERK 123 3475<br /> AD_ASST 101 4400<br /> IT_PROG 102 9000<br /> IT_PROG 103 4950<br /> FI_ACCOUNT 108 7920<br /> PU_MAN 100 11000<br /> ST_CLERK 122 2700<br /> SA_REP 145 8500<br /> AC_ACCOUNT 205 8300<br /> AD_VP 100 17000</p><p>DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)<br />------------- ---------- ---------- -----------<br /> ST_CLERK 120 2625<br /> ST_CLERK 124 2925<br /> SA_REP 147 7766.66667<br /> SA_REP 149 8333.33333<br /> HR_REP 101 6500<br /> PR_REP 101 10000<br /> ST_CLERK 123 3000<br /> SH_CLERK 121 3675<br /> PU_CLERK 114 2780<br /> SA_MAN 100 12200<br /> MK_REP 201 6000<br /> 110 AC_ACCOUNT 8300<br /> 90 AD_VP 17000<br /> 50 ST_CLERK 2785<br /> 80 SA_REP 8396.55172<br /> 50 ST_MAN 7280<br /> 80 SA_MAN 12200<br /> 110 AC_MGR 12000<br /> 90 AD_PRES 24000<br /> 60 IT_PROG 5760<br /> 100 FI_MGR 12000</p><p>DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)<br />------------- ---------- ---------- -----------<br /> 30 PU_CLERK 2780<br /> 50 SH_CLERK 3215<br /> 20 MK_MAN 13000<br /> 100 FI_ACCOUNT 7920<br /> SA_REP 7000<br /> 70 PR_REP 10000<br /> 30 PU_MAN 11000<br /> 10 AD_ASST 4400<br /> 20 MK_REP 6000<br /> 40 HR_REP 6500</p><p>52 rows selected.