分多级聚合数据
在Oracle中可以使用CUBE、ROLLUP和分组的功能来计算小计或其他聚合运算的中间结果。
以现实生活中的需求为例:计算按部门和工种分组后的平均薪水,合计薪水,然后是部门级别的平均薪水、合计薪水(不再考虑工种区别),最后是全公司范围的平均与合计。
select department_id, job_id, avg(salary), sum(salary)
from hr.employees
group by rollup (department_id,job_id);
ROLLUP函数按照从右到左的顺序,提供了向上汇集过程中不同级别的分组合计的功能。
语句使用这样的收起特性,相当于执行如下3条独立语句,然后用UNION功能或应用层代码把三条语句的结果合在一起。
select department_id , job_id , avg(salary) , sum(salary)
from hr.employees
group by department_id , job_id;
select department_id , avg(salary) , sum(salary)
from hr.employees
group by department_id;
select avg(salary) , sum(salary)
from hr.employees;
细心的读者会发现,因为ROLLUP函数是根据给定的字段由右到左收起的,当收起JOB_ID字段时,我们看到的是根据JOB_ID收起的汇总结果,但看不到原始数据根据DEPARTMENT_ID收起的汇总结果。我们可以用修改后的语句来改变这个结果:
select department_id, job_id, avg(salary), sum(salary)
from hr.employees
group by rollup (job_id,department_id);
执行这条语句,我们得到了DEPARTMENT_ID收起时的汇总结果,但又没有了原始数据根据JOB_ID收起时的结果,我们只能在最高级别(公司级别)的结果中看到JOB_ID的汇总结果,但二者意义不同。为了看到各个维度的收起结果,用CUBE函数重写语句:
select department_id , job_id , min(salary) , avg(salary) , max(salary)
from hr.employees
group by cube(department_id,job_id);
结果显示了每个级别的收起汇总结果。
ROLLUP和CUBE函数都可以包含我们希望的任意多的维度。再把例子扩展一下,还可以使用CUBE函数在部门,工种,经理和雇佣时间4个维度上计算平均薪水。
select department_id,job_id,manager_id,
extract(year from hire_date) as "START_YEAR",avg(salary)
from hr.employee
group by cube(department_id,job_id,manager_id,extract(year from hire_date));
语句的结果能提供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.。
转载于:https://blog.51cto.com/songchenguang/1623631