Oracle2-Rollup和Cube用法

在Oracle的聚合函数中，会有按照维度统计的情况,比如上图按照job 和 deptno统计 sal的sum

但是也会遇到同时要求统计只按照job维度统计的情况，并且做到一张表里

1 union 来实现维度不一致

首先反应过来的是分两步查询，再讲结果union起来

select 
  job,null as deptno,sum(sal)
from emp
group by job

union all 

select 
	job,deptno,sum(sal)
from emp
group by job,deptno

2 rollup 来实现变化维度

2.2 美化一下

SELECT
  job
  ,SUM(CASE WHEN deptno = '10' THEN "sum" END) AS "10"
  ,SUM(CASE WHEN deptno = '20' THEN "sum" END) AS "20"
  ,SUM(CASE WHEN deptno = '30' THEN "sum" END) AS "30"
  ,SUM(CASE WHEN deptno = '小计' or deptno = '-' THEN "sum" END) AS "小计"
FROM (
  SELECT 
    NVL(job, '总计') AS job,
    CASE WHEN job IS NULL THEN '-' ELSE NVL(TO_CHAR(deptno), '小计') END AS deptno,
    SUM(sal) AS "sum"
  FROM emp
  GROUP BY ROLLUP(job, deptno)
) a
GROUP BY job;

3 cube

SELECT 
    job,
    deptno,
    SUM(sal) AS "sum"
FROM emp
GROUP BY cube(job, deptno)

4 rollup vs cube

从以上的查询结果可以看到 rollup和 rullup的不同点在于
rollup只会对rollup (A,B) 做变化的维度的聚合

而 cube（A,B）对A和B都做变化维度的聚合