GROUPING SETS分组集
- GROUPING SETS是GROUP BY子句更进一步的扩展, 它让你能够定义多个数据分组。这样做使聚合更容易, 并且因此使得多维数据分析更容易。
- 你能够用GROUPING SETS在同一查询中定义多个分组
- ORACLE服务器计算在GROUPING SETS子句中指定的所有分组; 用UNION ALL操作组合单个的分组结果
- 分组集合的效率
- 对基表仅进行一次查询
- 不需要写复杂的UNION语句
- GROUPING SETS有更多的元素, 更好的执行性能
一个单个的SELECT语句如果用GROUPING SETS写, 就可以指定不同的分组(其中也可以包括ROLLUP或CUBE操作), 这样做比用UNION ALL操作组合多个SELECT语句更好。 例如: 你可以写:
SELECT DEPARTMENT_ID, JOB_ID, MANAGER_ID, AVG(SALARY)
FROM EMPLOYEES
GROUP BY GROUPING SETS
((DEPARTMENT_ID, JOB_ID, MANAGER_ID), (DEPARTMENT_ID, MANAGER_ID),(JOB_ID, MANAGER_ID)) ;
该语句计算三个分组的合计:
(DEPARTMENT_ID, JOB_ID, MANAGER_ID)、(DEPARTMENT_ID, MANAGER_ID)和(JOB_ID, MANAGER_ID)
如果没有在ORACLE9i中的这个增强, 就需要用UNION ALL将多饿查询组合在一起, 来得到上面的SELECT语句的输出。多查询方式是效率很低的, 因为它需要多次相同数据的扫描。
CUBE和ROLLUP可以看做带非常特殊的语句的分组集。下面的例子显示了这个事实。
CUBE(a,b,c) 相当于GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())
ROLLUP(a,b,c)相当于GROUPING SETS((a,b,c),(a,b),(a),())
在幻灯片中的查询计算在两个分组上的合计。该表分为下面两组:
- DEPARTMENT_ID, JOB_ID
- JOB_ID, MANAGER_ID
计算每个分组的平均工资。结果集显示两个分组各自的平均工资。
在输出中, 标记为1的分组可以被解释为:
- 在部门10中工作JOB_ID为AD_ASST的所有雇员的平均工资是4400。
- 在部门20中工作JOB_ID为MK_MAN的所有雇员的平均工资是13000。
- 在部门20中工作JOB_ID为MK_REP的所有雇员的平均工资是6000。
- 在部门50中工作JOB_ID为ST_CLERK的所有雇员的平均工资是2925, 等等。
在输出中, 标记为2的分组可以被解释为:
- 那些JOB_ID为SA_MAN, 并且他们的经理的MANAGER_ID是100的所有雇员的平均工资是10500, 等等。
- 那些JOB_ID为SA_REP, 并且他们的经理的MANAGER_ID是149的所有雇员的平均工资是8866.66667。
幻灯片中的例子也可以被写做:
SELECT DEPARTMENT_ID, JOB_ID, NULL AS MANAGER_ID, AVG(SALARY) AS AVGSAL
FROM EMPLOYESS
GROUP BY DEPARTMENT_ID, JOB_ID
UNION ALL
SELECT NULL, JOB_ID, MANAGER_ID, AVG(SALARY) AS AVGSAL
FROM EMPLOYESS
GROUP BY JOB_ID, MANAGER_ID ;
在缺少查看交叉查询块产生执行计划的优化器时, 前面的插销内将需要两次基表EMPLOYEES扫描, 这将是非常低效的。因此, 建议使用GROUPING SETS语句。