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:
Select area,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把它转为"所有月份"
Select Decode(Grouping(area),1,'所有地区',area) area, Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group 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
比如
金额 RowNum Rank Dense_Rank
张三 4000元 1 1 1
李四 3000元 2 2 2
钱五 2000元 3 3 3
孙六 2000元 4 3 3
丁七 1000元 5 5 4
这时,应该把并列第三的钱五和孙六都选进去,所以用Ranking功能比RowNumber保险.至于Desnse还是Ranking就看具体情况了。
SELECT salesperson_id, SUM(tot_sales) sp_sales, RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank FROM orders GROUP 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_quartile FROM orders GROUP BY cust_nbr ORDER 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 month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_preceeding FROM orders GROUP BY month ORDER BY month;
SELECT month, SUM(tot_sales) monthly_sales, AVG(SUM(tot_sales)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) rolling_avg FROM orders GROUP 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_orders SET (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
)
*/
分享到:
相关推荐
Rollup 是一种统计方法,它可以对数据进行分组和聚合计算,但它只能对单个列进行分组。Cube 是一种特殊的分组统计方法,它可以对数据进行多维度分组和聚合计算。 无论是 Grouping Sets、Rollup 还是 Cube,分组统计...
作者通过总结各自多年的软件开发和教学培训经验,与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在,内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧:...
14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据:插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 ...
14.3 使用ROLLUP、GROUPING和CUBE 14.4 家族树和COlLrlectby 14.4.1 排除个体和分支 14.4.2 向根遍历 14.4.3 基本规则 第15章 更改数据:插入、更新、合并和删除 第16章 DECODE和CASE.SQL中的if-fhen-else 第17章 ...
使用SQL语句 4.1 使用基本查询 4.1.1 简单查询语句 4.1.2 ...语句 4.3.1 事务和锁 4.3.2 提交事务 4.3.3 回退事务 4.3.4 只读事务 4.3.5 顺序事务 4.4 数据分组 4.4.1...
实验28:高级分组rollup,cube操作 65 实验29:树结构的查询start with子句 66 实验30:高级dml操作 68 第二部分pl/sql基础 69 匿名块的编写 69 实验31:书写一个最简单的块,运行并查看结果 69 实验32:在块中操作...
8.3.3 ROLLUP运算符和CUBE运算符 8.3.4 GROUP BY子句中的NULL值处理 8.3.5 HAVING子句 8.3.6 HAVING子句与WHERE子句 8.3.7 SELECT语句各查询子句总结 第9章 多表查询 9.1 本章用到的实例表 ...
8.3.3 rollup运算符和cube运算符 151 8.3.4 group by子句中的null值处理 153 8.3.5 having子句 153 8.3.6 having子句与where子句 154 8.3.7 select语句各查询子句总结 156 第9章 多表查询 157 9.1 本章用到的...
20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个字段...
精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后附录提供快速索引,即查、即学、...
250 实例170 电子相册屏幕保护程序 250 实例171 歌曲播放屏幕保护程序 251 第6章 文件系统 253 6.1 创建和删除文件 254 实例172 创建和删除文件夹 254 实例173 建立临时文件 255 ...
精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后附录提供快速索引,即查、即学、...
精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后附录提供快速索引,即查、即学、...
精选570个典型范例,全面覆盖实用和热点技术,涉及面广,实用性强源于实际项目开发,帮助读者短时间掌握更多实用技术,提高编程水平范例经过精心编排,重点、难点突出,易学易懂书后附录提供快速索引,即查、即学、...