- 浏览: 358094 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (55)
- java基础 (18)
- oracle (13)
- PL/SQL (8)
- Hibernate (3)
- JDBC (1)
- JVM (1)
- Dump (1)
- windows (2)
- struts2 (2)
- SQL (3)
- OGNL (1)
- Web前端 (3)
- 开发工具 (4)
- weblogic (0)
- shell (0)
- linux (1)
- windows,项目实施 (0)
- eclipse (3)
- SVN (1)
- vmware (2)
- 虚拟机 (2)
- hadoop (0)
- Android (0)
- 大数据 (2)
- Jquery (3)
- js (5)
- js,ajax (4)
- CKeditor (1)
- HTML (1)
- mysql (0)
最新评论
-
alangxi:
非常详尽,非常感谢。
BigDecimal 使用方法详解 -
nich002:
内容很详尽。
BigDecimal 使用方法详解 -
nich002:
看着真蛋疼。
BigDecimal 使用方法详解 -
zhangyinhu8680:
jz20110918 写道想问问楼主,sqlserver的 S ...
Oracle SQL中实现indexOf和lastIndexOf功能,substr和instr用法 -
zhangyinhu8680:
数据库我只用过oracle,sqlServer我我没有接触过, ...
Oracle SQL中实现indexOf和lastIndexOf功能,substr和instr用法
文章转自 http://blog.csdn.net/huang_xw/article/details/6402396
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的结果集是一样的:
与
2 cube
cube的意思是立方,对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:
与
3 grouping sets
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的参数允许重复
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以便理解:
与
6 grouping()、grouping_id()、group_id()
6.1 grouping()
参数只有一个,而且必须为group by中出现的某一列,表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言,grouping()=0,反之为1;
6.2 grouping_id()
参数可以是多个,但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量,例如如果grouping(A)=1,grouping(B)=0,则grouping_id(A,B)的返回值就是二进制的10,转成10进制就是2。
6.3 group_id()
无参数。见上面的说明3),group by对某些列的集合会进行重复的grouping,而实际上绝大多数情况下对结果集中的这些重复行是不需要的,那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时,每行的group_id()分别是0,1,…,n,这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。
7 示例
7.1 建表与数据
7.2 查询语句
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)[color=green][/color]
与
Select A,B,C,sum(E) from test group by A,B,C union all Select A,B,null,sum(E) from test group by A,B union all Select A,null,null,sum(E) from test group by A union all Select null,null,null,sum(E) from test
2 cube
cube的意思是立方,对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,C union all Select A,B,null,sum(E) from test group by A,B union all Select A,null,C,sum(E) from test group by A,C union all Select A,null,null,sum(E) from test group by A union all Select null,B,C,sum(E) from test group by B,C union all Select null,B,null,sum(E) from test group by B union all Select null,null,C,sum(E) from test group by C union all Select null,null,null,sum(E) from test;
3 grouping sets
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的参数允许重复
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,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;
6 grouping()、grouping_id()、group_id()
6.1 grouping()
参数只有一个,而且必须为group by中出现的某一列,表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言,grouping()=0,反之为1;
6.2 grouping_id()
参数可以是多个,但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量,例如如果grouping(A)=1,grouping(B)=0,则grouping_id(A,B)的返回值就是二进制的10,转成10进制就是2。
6.3 group_id()
无参数。见上面的说明3),group by对某些列的集合会进行重复的grouping,而实际上绝大多数情况下对结果集中的这些重复行是不需要的,那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时,每行的group_id()分别是0,1,…,n,这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。
7 示例
7.1 建表与数据
SQL> create table test(department_id number, a varchar2(20), b varchar2(20)); Table created SQL> insert into test values(10, 'A', 'B'); 1 row inserted SQL> commit; Commit complete
7.2 查询语句
select department_id, a, b, grouping(department_id), grouping(a), grouping(b) from test group by rollup(department_id, a, b) order by 4, 5, 6; select department_id, a, b, grouping(department_id), grouping(a), grouping(b) from test group by cube(department_id, a, b) order by 4, 5, 6;
发表评论
-
Oracle的Replace函数与translate函数详解与比较
2014-12-10 14:12 1285Oracle的Replace函数与translate函数详解与 ... -
灰色预测java实现
2014-10-20 21:15 1855/** * 灰度预测模型 * * @author Se ... -
自动复制转换StringBuffer
2013-11-21 11:33 0网址http://www.cnblogs.com/coqn/a ... -
Hadoop使用技巧
2013-08-18 21:51 0http://blog.sina.com.cn/s/blog_ ... -
Hadoop 中利用 mapreduce 读写 mysql 数据
2013-08-18 21:34 0Hadoop 中利用 mapreduce 读写 mysql 数 ... -
hibernate通过映射文件生成表结构
2013-05-12 14:38 0文章来自 http://dianziermu. ... -
ORA-01219 :数据库未打开:仅允许在固定表/视图中查询 解决!
2013-01-26 01:01 17963ORA-01219:database not open:que ... -
Oracle同义词
2012-12-14 16:46 0Oracle同义词的创建与删除(2012-03-20 22:1 ... -
exp 用户名/密码@数据库名
2012-10-28 13:07 0在oracle中的bin目录下 exp ... -
恢复到update或者delete之前的数据(非常重要)
2012-10-22 20:28 0首先要开启ROW MOVEMENT功能 SQL>ALTE ... -
函数Ratio_to_reportde 的使用方法(举例说明)
2012-10-08 18:45 0分析函数Ratio_to_report( ) over()使用 ... -
Oracle中的MINUS和UNION用法(转)
2012-10-23 11:26 7939Oracle中的MINUS和UNION用法(转)Oracle中 ... -
解决PL/SQL中的中文乱码问题
2012-09-04 17:26 16291.在运行中输入:regedit,打开注册表编辑器; 2. ... -
oracle递归查询即树查询
2012-07-30 14:53 5784oracle树查询又称为递归查询,是最常用的查询方法之一,下文 ... -
Oracle 表删除恢复
2012-07-02 13:36 1971在Oracle 10g数据库中,引入了一个回收站(Recycl ... -
Oracle Connect By用法
2012-06-26 16:58 0oracle中的select语句可以 ... -
SQL 在常用报表业务中的归并、转换与信息汇总中的应用技巧
2012-06-06 22:24 1796本文阐述并分步骤汇总了 SQL 数据库报表系统中最常见的三类典 ... -
Oracle SQL中实现indexOf和lastIndexOf功能,substr和instr用法
2012-05-29 13:08 30348Oracle SQL中实现indexOf和lastIndexO ... -
PL/SQL对ORACLE的语言操作
2012-05-10 13:31 1931PL/SQL是ORACLE对标准数据 ... -
oracle SELECT INTO 和 INSERT INTO SELECT 区别
2012-05-10 13:16 3205我们经常会遇到需要表复制的情况,如将一个table1的数据的部 ...
相关推荐
rollup cube grouping sets的用法 详细,讲述与实例想结合
GROUP BY子句(rollup,cube,grouping sets)实例说明
NULL 博文链接:https://dolphin-ygj.iteye.com/blog/416356
rollup,cube,grouping sets()的个人理解 其中关于这三个的各种情况,举例论证这三个的区别。
oracle 聚合函数的扩展使用 1.ROLLUP子句 ROLLUP是GROUP BY子句的扩展,它是为每一个分组返回一条合计记录,并为全部分组返回总计。...3.GROUPING()函数与ROLLUP、CUBE的结合使用 4.GROUPING_ID()函数的使用
关于with cube ,with rollup 和 grouping 通过查看sql 2005的帮助文档找到了CUBE 和 ROLLUP 之间的具体区别: CUBE 生成的结果集显示了所选列中值的所有组合的聚合。ROLLUP 生成的结果集显示了所选列中值的某一层次...
本博客简单介绍一下oracle分组函数之rollup的用法,rollup函数常用于分组统计,也是属于oracle分析函数的一种 环境准备 create table dept as select * from scott.dept; create table emp as select * from ...
深入介结分析函数的内部实现cube,rollup,grouping,里面有具体的例子来说明,方便大家理解学习
使用ROLLUP函数生成报表的小计、合计 这个函数很不错 使用的范围其实蛮广的
oracle rollup及cube的使用,开发人员必备的资料
group by扩展包括 rollup、cube、grouping、grouping sets的用法和说明
Oracle的Rollup用法 Oracle的Rollup用法
详细介绍了cube、rollup、grouping sets的原理及用法
NULL 博文链接:https://wangjingyi.iteye.com/blog/1545090
目录 一、OLAP多维分析概念及函数 1、cube导论 2、cube核心操作 1)、cube核心操作 2)、DICE (切块) 3)、ROLL UP (上卷) 4)、DRILL DOWN (下... 2、grouping sets 函数 3、with rollup函数 三、with as
关于oracle的所有分析函数的使用,像rollup,cube之类的.
SQL 关于with cube,with rollup 和 grouping
SQL语句中Group BY 和Rollup以及cube用法