Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、grouping sets。
三种函数都能对分组列实现自动汇总。
- 如果是Group by ROLLUP(A,B,C)的话,roll up的意思是“卷起”,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。对于n个参数的rollup,有n+1次的grouping。
- 如果使用group by cube(A,B,C),cube的意思是立方,则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作,一共是2^3=8次grouping。
- 如果使用grouping sets就是对参数中的每个参数做grouping,也就是有几个参数做几次grouping,例如使用group by grouping sets(A,B,C),则对(A),(B),(C)进行group by,如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的,也就是对(A)进行2次group by,grouping sets的参数允许重复
Oracle为了解决引入ROLLUP和CUBE后,汇总和NULL值无法区分的情况,提供了GROUPING、GROUPING_ID和GROUP_ID三个函数
- Grouping用法,必须要有参数
Grouping是用来判断当前Column是否是一个合计列,1为是,0为否
Select Decode(Grouping(area),1,'所有地区',area) area,
Decode(Grouping(month),1,'所有月份',month), sum(money)
From SaleOrder
Group by RollUp(area,month); - grouping_id()用法,必须要有参数
grouping_id是向量值,可以认为是二进值的数,例:grouping_id(a,b,c),如果列a为空就是0 非空为1,列b和列c也一样,
结果会得到一个三位数,用二进制转换成十进制就是了。
select job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno)
having grouping_id(job,deptno)<=2 - group_id()用法,没有参数
group by对某些列的集合会进行重复的grouping,而实际上绝大多数情况下对结果集中的这些重复行是不需要的,那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时,每行的group_id()分别是0,1,…,n,这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。
ROLLUP在数据统计和报表生成过程中带来极大的便利,而且效率比起来Group By + Union组合方法效率高得多。这也体现了Oracle在SQL统计分析上人性化、自动化、高效率的特点。
Select A,B,sum(E) from test1 group by A, rollup(A,B);
与
Select A,B,sum(E) from test1 group by A,B
Union all
Select A,null,sum(E) from test1 group by A
Union all
Select A,null,sum(E) from test1 group by A;