Oracle的rollup、cube、grouping sets函数

Oracle的group by除了基本用法以外，还有3种扩展用法，分别是rollup、cube、grouping sets。1 rollup假设有一个表test，有A、B、C、D、E5列。如果使用group by rollup（A,B,C），首先会对（A、B、C）进行GROUP BY，然后对（A、B）进行GROUP BY，然后是（A）进行GROUP BY，最后对全表进行GROUP BY操作。roll up的意思是“卷起”，这也可以帮助我们理解group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping（也就是全表grouping），对于n个参数的rollup，有n+1次的grouping。以下2个sql的结果集是一样的：Select A,B,C,sum（E） from test group by rollup（A,B,C）与Select A,B,C,sum（E） from test group by A,B,Cunion allSelect A,B,null,sum（E） from test group by A,Bunion allSelect A,null,null,sum（E） from test group by Aunion allSelect null,null,null,sum（E） from test2 cubecube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group by cube（A,B,C）,，则首先会对（A、B、C）进行GROUP BY，然后依次是（A、B），（A、C），（A），（B、C），（B），（C），最后对全表进行GROUP BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql：Select A,B,C,sum（E） from test group by cube（A,B,C）;与Select A,B,C,sum（E） from test group by A,B,Cunion allSelect A,B,null,sum（E） from test group by A,Bunion allSelect A,null,C,sum（E） from test group by A,Cunion allSelect A,null,null,sum（E） from test group by Aunion allSelect null,B,C,sum（E） from test group by B,Cunion allSelect null,B,null,sum（E） from test group by Bunion allSelect null,null,C,sum（E） from test group by Cunion allSelect null,null,null,sum（E） from test;3 grouping setsgrouping 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的参数允许重复4 总结rollup （N+1个分组方案）cube （2^N个分组方案）grouping sets （自定义罗列出分组方案）5 注意点5.1 机制不同在rollup和cube的说明中分别给出了用基本group by加结果集union all给出了结果集相同的sql，但这只是为了理解的方便而给出的sql，并不说明rollup和cube与基本group by加结果集union all等价。实际上两者的内部机制是安全不一样的，前者除了写法简洁以外，运行时不需多次扫描表，效率远比后者高。5.2 集合可运算3种扩展用法的参数可以是源表中的某一个具体的列，也可以是若干列经过计算而形成的一个新列（比如说A+B，A||B），也可以是这两种列的一个集合（例如（A+B，C）），对于grouping set更是特殊，可以是空集合（），表示对全表进行group by。5.3 group by 与 rollup, cube组合使用3）Group by的基本用法以及这3种扩展用法可以组合使用，也就是说可以出现group by A,rollup（A,B）这样的用法，oracle将对出现在group by中的每种用法的grouping列集合做笛卡尔积然后对其中的每一个元素做group by。这话说起来挺绕口，举例说明吧，group by A, rollup（A,B），基本用法的grouping集合是（A）,rollup（A,B）的grouping集合是（（A,B）,（A）,（））,两个集合的笛卡尔积集合是（（A,A,B）,（A,A）,（A）），所以会首先对（A,A,B）做group by，然后对（A,A）做group by，最后对（A）做group by。实际上对（A,A,B）做group by和对（A,B）做group by两者是完全等价的（group by A,A,B结果和group by A,B完全一样），同理对（A,A）做group by和对（A）做group by也是等价的。简化后的结果就是首先对（A,B）做group by，然后对（A）做group by，最后再对（A）做group by。下面给出两个等价的sql以便理解：Select A,B,sum（E） from test1 group by A, rollup（A,B）;与Select A,B,sum（E） from test1 group by A,BUnion allSelect A,null,sum（E） from test1 group by AUnion allSelect A,null,sum（E） from test1 group by A;

查看Oracle表空间大小及所剩空间大小Oracle约束的关键字Enable/Disable/Validate/Novalidate相关资讯 Oracle数据库 Oracle入门教程 oracle数据库教程

Oracle数据库全球化（03月01日）
Oracle数据库日期过滤方法性能比较（02/02/2015 13:20:26）
Oracle数据库安装中端口被占用问题（10/29/2014 07:42:24）

在CentOS 6.6上搭建C++运行环境并（10/10/2015 19:44:40）
Oracle数据库无法使用localhost和（11/14/2014 16:39:10）
使用SQLT来构建Oracle测试用例（08/28/2014 06:17:41）

本文评论查看全部评论（0）