Oracle PL/SQL之GROUP BY GROUPING SETS

最新推荐文章于 2022-05-19 14:56:52 发布

最新推荐文章于 2022-05-19 14:56:52 发布 · 85 阅读

文章标签：

#数据库

本文对比了SQL中GROUP BY GROUPING SETS与UNION ALL在处理多维度分组聚合时的区别及性能差异。通过具体示例展示了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) 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）：

SELECT NULL department_id, job_id, manager_id, AVG (salary) FROM employees GROUP BY (job_id, manager_id) UNION ALL SELECT department_id, job_id, NULL manager_id, AVG (salary) FROM employees GROUP BY (department_id, job_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.