GROUPING SETS分组集

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语句。