- 浏览: 109949 次
- 性别:
- 来自: 武汉
最新评论
-
lijunwyf:
不错,实用
JAVA 开发内存设置 -
xls:
yang190637133 写道我也碰到这个问题了 不知道怎么 ...
CAS单点登录SSO异常 -
yang190637133:
我也碰到这个问题了 不知道怎么解决
CAS单点登录SSO异常 -
carrey360:
我也遇到这问题<Error> <WebLog ...
配置期间超过了 WebLogic Server StuckThreadMaxTime 值 -
xm_bluesky:
...
html收集
1. 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,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;
2. cube(),rollup(),grouping sets() 是oracle提供的分析函数,看看下面的例子就知道他们的作用了.
创建测试表 并准备数据
CREATE TABLE test_sales (
location_name VARCHAR2(20),
month_flag number,
sales NUMBER,
manager VARCHAR2(20),
create_stamp DATE);
/
insert into test_sales values('SH',5,1,'Kevin',sysdate);
/
insert into test_sales values('SH',6,9,'Kevin',sysdate);
/
insert into test_sales values('SH',7,9,'Kevin',sysdate);
/
insert into test_sales values('SH',5,1,'JT',sysdate);
/
insert into test_sales values('GZ',6,9,'JT',sysdate);
/
insert into test_sales values('SH',7,8,'JT',sysdate);
/
insert into test_sales values('GZ',5,1,'Miles',sysdate);
/
insert into test_sales values('GZ',6,9,'Miles',sysdate);
/
insert into test_sales values('GZ',7,8,'Miles',sysdate);
/
insert into test_sales values('SH',5,1,'Collion',sysdate);
/
insert into test_sales values('GZ',6,9,'Collion',sysdate);
/
insert into test_sales values('GZ',7,9,'Collion',sysdate);
/
1.查询总销售额,地区销售额和各经理销售情况
solution 1 -- cube()
select manager,location_name,sum(sales) from test_sales
group by cube(manager ,location_name)
结果如下:
MANAGER LOCATION_NAME SUM(SALES)
74 -- 各地区总销售额
GZ 45 -- GZ 地区销售额
SH 29 -- SH 地区销售额
JT 18 -- 经理JT销售额
JT GZ 9
JT SH 9
Kevin 19 -- 经理Kevin总销售额
Kevin SH 19
Miles 18 -- 经理Miles总销售额
Miles GZ 18
Collion 19 -- 经理Collion总销售额
Collion GZ 18
Collion SH 1
solution 2 -- rollup()
select manager,location_name,sum(sales) from test_sales
group by rollup(manager ,location_name)
结果同上,只不过是汇总信息的现实顺序不同:
MANAGER LOCATION_NAME SUM(SALES)
JT GZ 9
JT SH 9
JT 18
Kevin SH 19
Kevin 19
Miles GZ 18
Miles 18
Collion GZ 18
Collion SH 1
Collion 19
74
2. 查询各经理销售情况 即指定分组
solution 1-- grouping sets()
select manager,location_name,sum(sales) from test_sales
group by grouping sets((manager ,location_name),manager)
结果如下:
MANAGER LOCATION_NAME SUM(SALES)
JT GZ 9
JT SH 9
JT 18
Kevin SH 19
Kevin 19
Miles GZ 18
Miles 18
Collion GZ 18
Collion SH 1
Collion 19
自己看看下面script的运行结果
select manager,location_name,sum(sales) from test_sales
group by grouping sets((manager ,location_name),manager
,location_name,())
聚合函数是oracle数据仓库的基础。为了提高距合的性能,oracle提供了group by条款的扩展。 ü cube ü rollup ü grouping ü grouping sets 这几个对sql的扩展使得查询和报告都变得简单和迅速。用户通过使用这几个扩展功能,可以1,简化代码编程;2,快速有效的查询处理;3,减少客户端和网络负载。本文以实例的方式深入解析这几种扩展的具体含义和使用环境。 考虑如下关系表。 create table mytest( subcompany_id varchar2(10), subcompany_name varchar2(40), branch_id varchar2(10), branch_name varchar2(40), region_id varchar2(10), region_name varchar2(40), customer_id varchar2(10), customer_name varchar2(40), market_id varchar2(10), market_name varchar2(49), sales_count numeric(10,3) ); comment on table mytest is '测试表'; comment on column mytest.subcompany_id is '分公司编号'; comment on column mytest.subcompany_name is '分公司名称'; comment on column mytest.branch_id is '经营部编号'; comment on column mytest.branch_name is '经营部名称'; comment on column mytest.region_id is '片区编号'; comment on column mytest.region_name is '片区名称'; comment on column mytest.customer_id is '客户编号'; comment on column mytest.customer_name is '客户名称'; comment on column mytest.market_id is '所属市场级别'; comment on column mytest.market_name is '市场级别名称'; comment on column mytest.sales_count is '销售数量'; insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count) values('01','分公司1','0101','经营部1','010101','片区1','01010101','客户1','01','片区1',1); insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count) values('01','分公司1','0101','经营部1','010101','片区1','01010102','客户2','02','片区2',1); insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count) values('01','分公司1','0101','经营部1','010102','片区2','01010201','客户1','01','片区1',1); insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count) values('01','分公司1','0101','经营部1','010102','片区2','01010202','客户2','02','片区2',1); insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count) values('01','分公司1','0102','经营部1','010201','片区1','01020101','客户1','01','片区1',1); insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count) values('01','分公司1','0102','经营部1','010202','片区2','01020202','客户2','02','片区2',1); rollup的行为非常直接,它根据grouping list的rollup条款创建合计: 首先,它计算grouping条款的标准聚合。 然后,它按照grouping list列从右到左进行更高层的聚合。 最后,创建n+1层的总计。 例如: group by rollup(A,B,C),则oracle最后得到的聚合结果为(A,B,C), (A,B), (A),() Rollup对group by 的扩展比较简单,但非常高效,对一个查询增加的开销非常少。 考虑如下查询。 select subcompany_name,branch_name,region_name,customer_name,sum(sales_count) 分公司1 经营部1 片区1 客户1 3 分公司1 经营部1 片区1 客户2 2 分公司1 经营部1 片区1 5 分公司1 经营部1 片区2 客户1 2 分公司1 经营部1 片区2 客户2 3 分公司1 经营部1 片区2 5 分公司1 经营部1 10 分公司1 经营部2 片区1 客户1 1 分公司1 经营部2 片区1 1 分公司1 经营部2 片区2 客户2 1 分公司1 经营部2 片区2 1 分公司1 经营部2 2 分公司1 12 12 上面的查询返回如下结果行: 1, 基于subcompany_name,branch_name,region_name,customer_name的聚合。 2, 基于subcompany_name,branch_name,region_name的聚合。 3, 基于subcompany_name,branch_name的聚合。 4, 基于subcompany_name的聚合。 5, 总计 用户还可以使用rollup包含有限的几个小计,语法如下: Group by expr1,rollup(expr2,expr3)这种情况下,group by条款创建2+1层小计。层次为(expr1,expr2,expr3)(expr1,expr2)(expr1) Cube进行grouping列规定的grouping,创建所有可能的聚合,例如规定了cube(customer_name,market_name)那么返回的结果将是2的2次方个。分别为(customer_name,market_name)(market_name)(customer_name)() Cube扩展在计算交叉报表时非常有用,当然我们可以使用select union all替代rollup或cube,但这需要很多的select union all语句。这样做不仅不够高效,而且加长了sql语句,不方便维护和扩充。 考虑如下查询: select subcompany_name,branch_name,region_name, 分公司1 经营部1 片区1 5 分公司1 经营部1 片区1 片区1 3 分公司1 经营部1 片区1 片区2 2 分公司1 经营部1 片区1 客户1 3 分公司1 经营部1 片区1 客户1 片区1 3 分公司1 经营部1 片区1 客户2 2 分公司1 经营部1 片区1 客户2 片区2 2 分公司1 经营部1 片区2 5 分公司1 经营部1 片区2 片区1 2 分公司1 经营部1 片区2 片区2 3 分公司1 经营部1 片区2 客户1 2 分公司1 经营部1 片区2 客户1 片区1 2 分公司1 经营部1 片区2 客户2 3 分公司1 经营部1 片区2 客户2 片区2 3 分公司1 经营部2 片区1 1 分公司1 经营部2 片区1 片区1 1 分公司1 经营部2 片区1 客户1 1 分公司1 经营部2 片区1 客户1 片区1 1 分公司1 经营部2 片区2 1 分公司1 经营部2 片区2 片区2 1 分公司1 经营部2 片区2 客户2 1 分公司1 经营部2 片区2 客户2 片区2 1 rollup 和cube带来的一个问题是,在返会的结果中如何能准确区分出那些是小计,哪些是汇总数据呢。这点可以使用grouping和grouping_id函数解决。 另外,我们还可以通过having过虑掉我们不希望在结果中出现的数据。 考虑查询:给出所有机构的小计,并在此基础上给出各市场级别的小计。 方案1: 使用cube计算所有的结果,然后用having过虑得出符合条件的结果集合。 select 这种方案的缺点在于,首先使用cube计算所有可能的汇总结果需要花费相当长的时间;其次由于结果给出了所有可能的汇总,而我们需要的只是其中很小一部分,这种情况下使用having过虑结果集也是一件很麻烦的事情。 方案2 使用嵌套查询,先得出rollup的结果,然后再利用现有结果跟market进行cube的group by计算。 方案3 联合rollup和cube。 grouping sets提供了指定汇总集合条件的功能。例如在上面的查询中,我们可以通过为select group by语句制定汇总条件() select 3. Oracle的group by聚合函数扩展cube rollup和grouping sets
rollup
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name);cube
customer_name,market_name,sum(sales_count)
from mytest
group by subcompany_name,branch_name,region_name,cube(customer_name,market_name) ;联合使用rollup和cube来解决特殊查询需求
grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name),
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count)
from mytest
group by cube(subcompany_name,branch_name,region_name,customer_name,market_name)
having(grouping(subcompany_name) <= grouping(branch_name) and
grouping(branch_name) <= grouping(region_name) and
grouping(region_name) <= grouping(customer_name));
select subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count)
from (
select
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) ,market_name
)
group by subcompany_name,branch_name,region_name,customer_name,cube(market_name)
这种方式多执行了一次查询,代码长度增加,可读性也不够强。另外还要主意过虑掉在里层查询中已经汇总的结果。
select
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) , cube(market_name)
order by grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name);grouping sets
subcompany_name,branch_name,region_name,
customer_name,market_name,sum(sales_count) sales_count
from mytest
group by grouping sets((subcompany_name,branch_name,region_name,customer_name) ,
(subcompany_name,branch_name,region_name) ,
(subcompany_name,branch_name) ,
(subcompany_name) ,
(market_name) ,
(subcompany_name,branch_name,region_name,customer_name,market_name) ,
(subcompany_name,branch_name,region_name,market_name) ,
(subcompany_name,branch_name,market_name) ,
(subcompany_name,market_name) ,
() )
发表评论
-
java操作数据库异常
2013-09-14 10:00 816mysql 1449 : The user specif ... -
hibernate常见异常总结
2012-05-22 13:47 0问:Hibernate初始化时总是报错:java.lang.N ... -
oracle job详解
2012-02-29 11:22 3170一、设置初始化参数 job_queue_process ... -
ORACLE系统表处理
2012-02-21 17:41 01.取得指定用户的所有表名: SELECT OWNER ... -
ORACLE用户的创建与授只读权
2011-12-12 18:01 1419Create user UserName Ident ... -
jdbc 连接本地oracle10G 的ORA-12505问题解决方法
2011-10-12 17:32 3757连接 jdbc Oracle10g 错误 ,JDBC代码无问题 ... -
PL/SQL的高级查询
2011-09-22 10:39 1575PL/SQL的高级查询 1 With子句 ... -
oracle常用的sql语句(转)
2011-09-17 09:47 937如何远程判断Oracle数据库的安装平台 select ... -
ORA-28000: 帐户已被锁定
2011-09-06 10:20 2750ORA-28000: 帐户已被锁定 原因:oracle ...
相关推荐
与`GROUP BY ROLLUP`和`GROUP BY CUBE`相比,`GROUPING SETS`更具有选择性,可以精确地控制生成的汇总组。`ROLLUP`生成所有可能的子集,而`CUBE`则生成所有可能的组合,这两者可能会产生大量的结果,尤其是在处理多...
因此,为了优化性能和减少不必要的结果,我们可能需要配合使用ROLLUP或GROUPING SETS,它们提供了更细粒度的控制来生成特定的分组组合。同时,我们还可以使用HAVING子句来过滤不感兴趣的结果,比如去除包含NULL的...
Rollup函数用于生成多级汇总数据,它可以创建一个类似于SQL GROUP BY语句的“GROUP BY GROUPING SETS”的效果。例如,可以计算每个区域、每个网络类型以及整个数据集的总费用。 Cube函数则更进一步,它不仅提供...
在Oracle数据库中,`GROUPING`函数被广泛应用于复杂的分组查询之中,尤其是在存储过程的开发中。本文将详细解释`GROUPING`函数的使用方法及其应用场景,并通过一个示例来帮助理解其工作原理。 #### 一、`GROUPING`...
在 Oracle 中,除了 Grouping Sets 之外,还有其他两种分组统计方法:Rollup 和 Cube。Rollup 是一种统计方法,它可以对数据进行分组和聚合计算,但它只能对单个列进行分组。Cube 是一种特殊的分组统计方法,它可以...
这个函数通常与GROUPING SETS、CUBE或ROLLUP等高级分组技术结合使用,帮助用户更灵活地处理NULL值和其他分组情况。 例如,如果我们有一个包含产品信息的数据表,其中有些产品的供应商信息可能是NULL,我们可能想要...
在某些情况下,你可能需要同时使用WITH CUBE和WITH ROLLUP,这可以通过GROUPING SETS实现。你可以创建一个包含所有所需分组的集合,包括CUBE和ROLLUP的所有组合。 4. **GROUP_ID 函数**: Oracle特有的GROUP_ID...
Oracle 聚合函数及其扩展使用 Oracle 聚合函数是指在 Oracle 数据库中对数据进行...Oracle 聚合函数及其扩展使用可以帮助开发人员和数据库管理员更好地对数据进行统计、分析和计算,从而获取有价值的信息和 insights。
6. GROUPING SETS, CUBE, ROLLUP:这些是更高级的分组功能,可以生成多个分组组合,用于多维数据分析。 三、使用注意事项 1. 分组函数不能与非分组列一起出现在SELECT语句的非聚合表达式中,除非该列被包含在`...
Oracle 提供了一组用于 OLAP 的 SQL 函数。 常用的 OLAP 函数有: * ROLLUP:对查询结果进行 rollup 操作 * CUBE:对查询结果进行 cube 操作 * GROUPING SETS:对查询结果进行 grouping sets 操作
`GROUP BY` 和 `HAVING` 子句是SQL中的基础工具,但在Oracle 10g中,你可以使用 `CUBE()`, `ROLLUP()`, `GROUPING SETS()` 进行多维度分析,创建更复杂的分组。`CONNECT BY` 用于构建层次结构,如组织结构或产品分类...
GROUP BY with ROLLUP and CUBE Operators 3-6 ROLLUP Operator 3-7 ROLLUP Operator Example 3-8 CUBE Operator 3-9 CUBE Operator: Example 3-10 GROUPING Function 3-11 GROUPING Function: Example 3-12 ...
- Les17_对 GROUP BY 子句的扩展.ppt可能深入讨论了GROUP BY的高级用法,如HAVING子句和ROLLUP/CUBE/GROUPING SETS。 通过这套培训资料,学习者可以从基础的SQL语法到复杂的数据库管理技巧逐步进阶,了解并掌握...
- `GROUP BY`和`ROLLUP`、`CUBE`、`GROUPING SETS`配合聚合函数实现分组汇总。 3. **分析函数**: - `RANK()`、`DENSE_RANK()`、`ROW_NUMBER()`用于排序和行编号。 - `LEAD()`、`LAG()`获取行的前后值,`FIRST_...
7. 高级查询技巧:这包括联接操作(INNER JOIN, OUTER JOIN, SELF JOIN)、子查询、集合操作(UNION, INTERSECT, EXCEPT)、分组和聚合函数(GROUP BY, HAVING, ROLLUP, CUBE, GROUPING SETS)以及窗口函数。...
8. **GROUPING SETS**: GROUPING SETS结合了ROLLUP和CUBE的功能,允许你指定一组特定的分组组合。 9. **DISTRIBUTE BY**: 虽然不是标准SQL的一部分,但某些Oracle版本支持DISTRIBUTE BY,它用于并行查询,帮助在多...
10. **Les17分组查询扩展.ppt** - 分组查询的高级用法,可能包含GROUP BY ROLLUP、CUBE和GROUPING SETS,这些功能允许更灵活的数据汇总和分析。 通过这个教学课件,学习者不仅可以掌握Oracle SQL的基本语法,还能...
10. 增强GROUP BY功能,使用GROUP BY扩展选项,如GROUPING SETS、CUBE和ROLLUP等,可以实现更复杂的聚合计算。 11. 分析函数(ANALYTICAL FUNCTIONS)是Oracle SQL的高级特性之一,允许在数据集上进行窗口计算,...