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;

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;

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;

• 博文量
7
• 访问量
4950