分多级聚合数据

在Oracle中可以使用CUBE、ROLLUP和分组的功能来计算小计或其他聚合运算的中间结果。

以现实生活中的需求为例：计算按部门和工种分组后的平均薪水，合计薪水，然后是部门级别的平均薪水、合计薪水（不再考虑工种区别），最后是全公司范围的平均与合计。

selectdepartment_id,job_id,avg(salary),sum(salary)
fromhr.employees
groupbyrollup(department_id,job_id);@H_301_15@ 
 ROLLUP函数按照从右到左的顺序，提供了向上汇集过程中不同级别的分组合计的功能。
 
  语句使用这样的收起特性，相当于执行如下3条独立语句，然后用UNION功能或应用层代码把三条语句的结果合在一起。
 
 
 
selectdepartment_id,sum(salary)
fromhr.employees
groupbydepartment_id,job_id;
selectdepartment_id,sum(salary)
fromhr.employees
groupbydepartment_id;
selectavg(salary),sum(salary)
fromhr.employees;@H_301_15@ 

 
 细心的读者会发现，因为ROLLUP函数是根据给定的字段由右到左收起的，当收起JOB_ID字段时，我们看到的是根据JOB_ID收起的汇总结果，但看不到原始数据根据DEPARTMENT_ID收起的汇总结果。我们可以用修改后的语句来改变这个结果：
 
 
 
selectdepartment_id,sum(salary)
fromhr.employees
groupbyrollup(job_id,department_id);@H_301_15@ 

 
  执行这条语句，我们得到了DEPARTMENT_ID收起时的汇总结果，但又没有了原始数据根据JOB_ID收起时的结果，我们只能在最高级别（公司级别）的结果中看到JOB_ID的汇总结果，但二者意义不同。为了看到各个维度的收起结果，用CUBE函数重写语句：
 
 
 
selectdepartment_id,min(salary),max(salary)
fromhr.employees
groupbycube(department_id,job_id);@H_301_15@ 

 
  结果显示了每个级别的收起汇总结果。
 
  ROLLUP和CUBE函数都可以包含我们希望的任意多的维度。再把例子扩展一下，还可以使用CUBE函数在部门，工种，经理和雇佣时间4个维度上计算平均薪水。
 
 
 
selectdepartment_id,manager_id,extract(yearfromhire_date)as"START_YEAR",avg(salary)
fromhr.employee
groupbycube(department_id,extract(yearfromhire_date));@H_301_15@ 

 
  语句的结果能提供4个维度上平均薪水的数据！
 
 
Union与Union All 的比较
 
有必要记住UNION与UNION ALL的区别。UNION会删除结果集中的重复数据（在去重复的时候对数据进行排序操作），UNION ALL则会保留源集中的所有数据，不管是否重复。
 

 
nvl() 函数
 
语法：NVL(eExpression1,eExpression2)
 
  如果 eExpression1 的计算结果为 null 值，则 NVL()返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL()返回 .NULL.。