Oracle数据分组:group by,having,rollup,cube,grouping sets
Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、grouping sets。
假设有一个表test,有A、B、C、D、E5列。
1)如果使用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的每个参数,都可以理解为取值为参与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,也就是有几个参数做几次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的参数允许重复
需要说明的几点:
1)在rollup和cube的说明中分别给出了用基本group by加结果集union all给出了结果集相同的sql,但这只是为了理解的方便而给出的sql,并不说明rollup和cube与基本group by加结果集union all等价。实际上两者的内部机制是安全不一样的,前者除了写法简洁以外,运行时不需多次扫描表,效率远比后者高。
2)3种扩展用法的参数可以是源表中的某一个具体的列,也可以是若干列经过计算而形成的一个新列(比如说A+B,A||B),也可以是这两种列的一个集合(例如(A+B,C)),对于grouping set更是特殊,可以是空集合(),表示对全表进行group by。
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;
相关函数grouping(),grouping_id(),group_id()的用法:
1)grouping():参数只有一个,而且必须为group by中出现的某一列,表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言,grouping()=0,反之为1;
2)grouping_id():参数可以是多个,但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量,例如如果grouping(A)=1,grouping(B)=0,则grouping_id(A,B)的返回值就是二进制的10,转成10进制就是2。
3)group_id():无参数。见上面的说明3),group by对某些列的集合会进行重复的grouping,而实际上绝大多数情况下对结果集中的这些重复行是不需要的,那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时,每行的group_id()分别是0,1,…,n,这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。
扩展用法及相关函数的应用例子:
数据库ACCINF表结构为:区域号area_no,机构号org_no,账户号acc_no,账户余额acc_bal,现有一个 报表要求先按机构统计每个机构的总余额,最后是所有区域的总余额,也就是说报表格式为:
区域1机构1余额数
区域1机构2余额数
区域2机构3余额数
区域2机构4余额数
总合计余额数
以往的sql是通过先计算出各机构的总余额,然后在程序里将所有的余额相加计算出所有区域的总余额。但使用扩展用法之后的sql就非常简单了:
Select nvl(area_no,’总合计’),org_no,sum(acc_bal) from acc_inf group by rollup((area_no,org_no));
如果使用上面的函数,还可以有其它的写法,一个例子是:
Select nvl(area_no,’总合计’),org_no,sum(acc_bal) from acc_inf group by rollup(area_no,org_no) having (grouping_id(area_no,org_no)=0 or grouping_id(area_no,org_no)=3)
###############################################################
2005-05-10 09:40 First Publish.
Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。 grouping_id()可以美化效果:
Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。
除本文内容外,你还可参考:
分析函数参考手册: http://xsb.itpub.net/post/419/33028
分析函数使用例子介绍:http://xsb.itpub.net/post/419/44634
SQL> create table t as select * from dba_indexes;
表已创建。
SQL> select index_type, status, count(*) from t group by index_type, status;
INDEX_TYPE STATUS COUNT(*)
--------------------------- -------- ----------
LOB VALID 51
NORMAL N/A 25
NORMAL VALID 479
CLUSTER VALID 11
下面来看看ROLLUP和CUBE语句的执行结果。
SQL> select index_type, status, count(*) from t group by rollup(index_type, status);
INDEX_TYPE STATUS COUNT(*)
--------------------------- -------- ----------
LOB VALID 51
LOB 51
NORMAL N/A 25
NORMAL VALID 479
NORMAL 504
CLUSTER VALID 11
CLUSTER 11
566
已选择8行。
SQL> select index_type, status, count(*) from t group by cube(index_type, status);
INDEX_TYPE STATUS COUNT(*)
--------------------------- -------- ----------
566
N/A 25
VALID 541
LOB 51
LOB VALID 51
NORMAL 504
NORMAL N/A 25
NORMAL VALID 479
CLUSTER 11
CLUSTER VALID 11
已选择10行。
查询结果不是很一目了然,下面通过Oracle提供的函数GROUPING来整理一下查询结果。
SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)
2 from t group by rollup(index_type, status) order by 1, 2;
G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB VALID 51
0 0 NORMAL N/A 25
0 0 NORMAL VALID 479
0 0 CLUSTER VALID 11
0 1 LOB 51
0 1 NORMAL 504
0 1 CLUSTER 11
1 1 566
已选择8行。
这个查询结果就直观多了,和不带ROLLUP语句的GROUP BY相比,ROLLUP增加了对INDEX_TYPE的GROUP BY统计和对所有记录的GROUP BY统计。
也就是说,如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。
下面看看CUBE语句。
SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count(*)
2 from t group by cube(index_type, status) order by 1, 2;
G_IND G_ST INDEX_TYPE STATUS COUNT(*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB VALID 51
0 0 NORMAL N/A 25
0 0 NORMAL VALID 479
0 0 CLUSTER VALID 11
0 1 LOB 51
0 1 NORMAL 504
0 1 CLUSTER 11
1 0 N/A 25
1 0 VALID 541
1 1 566
已选择10行。
和ROLLUP相比,CUBE又增加了对STATUS列的GROUP BY统计。
如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。
除了使用GROUPING函数,还可以使用GROUPING_ID来标识GROUP BY结果。
SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)
2 from t group by rollup(index_type, status) order by 1;
G_IND INDEX_TYPE STATUS COUNT(*)
---------- --------------------------- -------- ----------
0 LOB VALID 51
0 NORMAL N/A 25
0 NORMAL VALID 479
0 CLUSTER VALID 11
1 LOB 51
1 NORMAL 504
1 CLUSTER 11
3 566
已选择8行。
SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)
2 from t group by cube(index_type, status) order by 1;
G_IND INDEX_TYPE STATUS COUNT(*)
---------- --------------------------- -------- ----------
0 LOB VALID 51
0 NORMAL N/A 25
0 NORMAL VALID 479
0 CLUSTER VALID 11
1 LOB 51
1 NORMAL 504
1 CLUSTER 11
2 N/A 25
2 VALID 541
3 566
已选择10行。
grouping_id()可以美化效果:
select DECODE(GROUPING_ID(C1), 1, '合计', C1) D1,
DECODE(GROUPING_ID(C1, C2), 1, '小计', C2) D2,
DECODE(GROUPING_ID(C1, C2, C1 + C2), 1, '小计', C1 + C2) D3,
count(*),
GROUPING_ID(C1, C2, C1 + C2, C1 + 1, C2 + 1),
GROUPING_ID(C1)
from T2
group by rollup(C1, C2, C1 + C2, C1 + 1, C2 + 1);
===========================================================
1.报表合计专用的Rollup函数
销售报表
广州1月2000元
广州2月2500元
广州4500元
深圳1月1000元
深圳2月2000元
深圳3000元
所有地区7500元
以往的查询SQL:
Selectarea,month,sum(money) from SaleOrder group by area,month
然后广州,深圳的合计和所有地区合计都需要在程序里自行累计
1.其实可以使用如下SQL:
Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)
就能产生和报表一模一样的纪录
2.如果year不想累加,可以写成
Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)
另外Oracle 9i还支持如下语法:
Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)
3.如果使用Cube(area,month)而不是RollUp(area,month),除了获得每个地区的合计之外,还将获得每个月份的合计,在报表最后显示。
4.Grouping让合计列更好读
RollUp在显示广州合计时,月份列为NULL,但更好的做法应该是显示为"所有月份"
Grouping就是用来判断当前Column是否是一个合计列,1为yes,然后用Decode把它转为"所有月份"
SelectDecode(Grouping(area),1,'所有地区',area) area,Decode(Grouping(month),1,'所有月份',month),sum(money)From SaleOrderGroup by RollUp(area,month);
2.对多级层次查询的start with.....connect by
比如人员组织,产品类别,Oracle提供了很经典的方法
SELECT LEVEL, name, emp_id,manager_emp_id FROM employee START WITH manager_emp_id is null CONNECT BY PRIOR emp_id = manager_emp_id;
上面的语句demo了全部的应用,start with指明从哪里开始遍历树,如果从根开始,那么它的manager应该是Null,如果从某个职员开始,可以写成emp_id='11'
CONNECT BY就是指明父子关系,注意PRIOR位置
另外还有一个LEVEL列,显示节点的层次
3.更多报表/分析决策功能
3.1分析功能的基本结构
分析功能() over( partion子句,order by子句,窗口子句)
概念上很难讲清楚,还是用例子说话比较好.
3.2 Row_Number和 Rank, DENSE_Rank
用于选出Top 3 sales这样的报表
当两个业务员可能有相同业绩时,就要使用Rank和Dense_Rank
比如
金额RowNumRankDense_Rank
张三 4000元111
李四 3000元222
钱五 2000元333
孙六 2000元433
丁七 1000元554
这时,应该把并列第三的钱五和孙六都选进去,所以用Ranking功能比RowNumber保险.至于Desnse还是Ranking就看具体情况了。
SELECT salesperson_id, SUM(tot_sales) sp_sales,RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rankFROM ordersGROUP BY salesperson_id
3.3 NTILE把纪录平分成甲乙丙丁四等
比如我想取得前25%的纪录,或者把25%的纪录当作同一个level平等对待,把另25%当作另一个Level平等对待
SELECT cust_nbr, SUM(tot_sales) cust_sales,NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartileFROM ordersGROUP BY cust_nbrORDER BY 3,2 DESC;
NTITLE(4)把纪录以 SUM(tot_sales)排序分成4份.
3.4辅助分析列和Windows Function
报表除了基本事实数据外,总希望旁边多些全年总销量,到目前为止的累计销量,前后三个月的平均销量这样的列来参考.
这种前后三个月的平均和到目前为止的累计销量就叫windows function,见下例
SELECT month, SUM(tot_sales) monthly_sales,SUM(SUM(tot_sales)) OVER (ORDER BY monthROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_preceedingFROM ordersGROUP BY month ORDER BY month;
SELECT month, SUM(tot_sales) monthly_sales,AVG(SUM(tot_sales)) OVER (ORDER BY monthROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) rolling_avgFROM ordersGROUP BY month ORDER BY month;
Windows Function的关键就是Windows子句的几个取值
1 PRECEDING之前的一条记录
1 FOLLOWING之后的一条记录
UNBOUNDED PRECEDING之前的所有记录
CURRENT ROW当前纪录
4.SubQuery总结
SubQuery天天用了,理论上总结一下.SubQuery分三种
1.Noncorrelated子查询最普通的样式.
2.Correlated Subqueries把父查询的列拉到子查询里面去,头一回cyt教我的时候理解了半天.
3.Inline View也被当成最普通的样式用了.
然后Noncorrelated子查询又有三种情况
1.返回一行一列where price < (select max(price) from goods )
2.返回多行一列where price>= ALL (select price from goods where type=2)
or where NOT price< ANY(select price from goods where type=2)
最常用的IN其实就是=ANY()
3.返回多行多列一次返回多列当然就节省了查询时间
UPDATE monthly_ordersSET (tot_orders, max_order_amt) =(SELECT COUNT(*), MAX(sale_price)FROM cust_order)DELETE FROM line_item WHERE (order_nbr, part_nbr) IN(SELECT order_nbr, part_nbr FROM cust_order c)
========================================
/*--------理解grouping sets
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )
等效于
select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b
union all
select null, null, c, sum( d ) from t group by c
)
*/
select DECODE(GROUPING_ID(C1), 1, '合计', C1) D1,
DECODE(GROUPING_ID(C1, C2), 1, '小计', C2) D2,
DECODE(GROUPING_ID(C1, C2, C1 + C2), 1, '小计', C1 + C2) D3,
count(*),
GROUPING_ID(C1, C2, C1 + C2, C1 + 1, C2 + 1),
GROUPING_ID(C1)
from T2
group by rollup(C1, C2, C1 + C2, C1 + 1, C2 + 1);
转载、整理自:http://blog.csdn.net/roland_wg/article/details/4319323
相关推荐
rollup cube grouping sets的用法 详细,讲述与实例想结合
GROUP BY子句(rollup,cube,grouping sets)实例说明
关于with cube ,with rollup 和 grouping 通过查看sql 2005的帮助文档找到了CUBE 和 ROLLUP 之间的具体区别: ...仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择列表中才允许分组。 当看到以上
rollup,cube,grouping sets()的个人理解 其中关于这三个的各种情况,举例论证这三个的区别。
NULL 博文链接:https://dolphin-ygj.iteye.com/blog/416356
ROLLUP是GROUP BY子句的扩展,它是为每一个分组返回一条合计记录,并为全部分组返回总计。 2.CUBE子句也是对GROUP BY子句进行扩展,返回CUBE中所有列组合的小计信息,同时在最后显示总计信息。 3.GROUPING()函数...
group by 后 带 rollup 子句的功能可以理解为:先按一定的规则产生多种分组,然后按各种分组统计数据(至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数)。因此要搞懂group by 后 带 ...
SQL Server中的用法为group by colomn with [rollup|cube],首先要弄明白rollup 和cube,就要知道group by的用法,group by 为对列进行分组,只展现分组统计的值,而 rollup 为分层次展现,cube 为展现列中所有层次...
SQL语句中Group BY 和Rollup以及cube用法
group by扩展包括 rollup、cube、grouping、grouping sets的用法和说明
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 ...
本博客简单介绍一下oracle分组函数之rollup的用法,rollup函数常用于分组统计,也是属于oracle分析函数的一种 环境准备 create table dept as select * from scott.dept; create table emp as select * from ...
GROUP BY子句要和聚合函数配合使用才能完成分组查询,在SELECT查询的字段中,如果没有使用聚合函数就必须出现在ORDER BY子句中。分组查询后,查询结果为一个或多个列分组后的结果集。 GROUP BY语法 SELECT 列名, ...
NULL 博文链接:https://wangjingyi.iteye.com/blog/1545090
group by后使用rollup子句总结,oracle开发人员值得一看
用union all方式对上面代码改写如下:SQL> select group_id,job,sum(salary) from group_test 2 g
深入介结分析函数的内部实现cube,rollup,grouping,里面有具体的例子来说明,方便大家理解学习
详细介绍了cube、rollup、grouping sets的原理及用法
实际项目中因表数据量大,发现查询速度很慢。记录此次排查和优化过程。希望对阅读到此文章的朋友有所帮助。