rollup与cube分析函数学习总结

 

cube: 有2的n次方种组合:

select empno,ename,job,
       sum(sal),
       grouping(empno) gempno,
       grouping(ename) gename,
       grouping(job) gjob
 from scott.emp group by cube(empno,ename,job)
 order by gempno,gename,gjob;

等效于：以上SQL总共有8种组合

empno,ename,job
empno,ename,null
empno,null,job
empno,null,null
null,ename,job
null,ename,null
null,null,job
null,null,null
 

select empno,ename,job,0 gempno,0 gename,0 gjob, sum(sal) from scott.emp group by empno,ename,job
union all
select empno,ename,null,0 gempno,0 gename,1 gjob ,sum(sal) from scott.emp group by empno,ename
union all
select empno,null,job,0 gempno,1 gename,0 gjob ,sum(sal) from scott.emp group by empno,job
union all
select empno,null,null,0 gempno,1 gename,1 gjob,sum(sal) from scott.emp group by empno
union all
select null,ename,job,1 gempno,0 gename,0 gjob,sum(sal) from scott.emp group by ename,job
union all
select null,ename,null,1 gempno,0 gename,1 gjob,sum(sal) from scott.emp group by ename
union all
select null,null,job,1 gempno,1 gename,0 gjob,sum(sal) from scott.emp group by job
union all
select null,null,null,1 gempno,1 gename,1 gjob,sum(sal) from scott.emp
order by gempno,gename,gjob;

rollup:有2*n种组合，从左到右的方法
select empno,ename,job,
       sum(sal),
       grouping(empno) gempno,
       grouping(ename) gename,
       grouping(job) gjob
 from scott.emp group by rollup(empno,ename,job)
 order by gempno,gename,gjob;

等效于：以上SQL总共有4种组合
empno,ename,job
empno,ename,null
empno,null,null
null,null,null

select empno,ename,job,0 gempno,0 gename,0 gjob, sum(sal) from scott.emp group by empno,ename,job
union all
select empno,ename,null,0 gempno,0 gename,1 gjob ,sum(sal) from scott.emp group by empno,ename
union all
select empno,null,null,0 gempno,1 gename,1 gjob,sum(sal) from scott.emp group by empno
union all
select null,null,null,1 gempno,1 gename,1 gjob,sum(sal) from scott.emp
order by gempno,gename,gjob;