注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

饥民2011

一直在搬砖

 
 
 

日志

 
 
 
 

Oracle rollup 关键字用法简介.  

2013-10-07 22:54:28|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

1. 什么是Rollup

在PL/sql 中,  Rollup 这个关键字通常用于select 语句的 group by 后面.

在group by 后面使用rollup关键字,  select语句就可以对指定的分组的数据左汇总小计(求和).


一旦使用rollup,   在数据集中, rollup的列会被自动排序.

rollup 会创建n+1 层次的汇总. n是rollup 汇总的列的个数.  在实际操作中. 假如我们在rollup后面指定汇总 时间, 区域, 部门的汇总的数据.(n=3), 那么数据集的会含有4级的汇集行数据.


当对多个列进行汇集汇总时, 方向只能根据group by 的方向从右向左. 例如, 当使用group by( year, month, day),  汇总方向就是从day -> month -> year 的方向汇总.



2. 什么情况下应该使用rollup

上面已经提过了, 当我们需要对group by 分组数据集进行汇总小计时,  可以使用rollup 扩展.


a.  rollup 会帮助我们对数据集进行按照时间或地理区域进行分层次的汇总.  实际操作中,  我们可以在查询语句中指定要汇总小计的列. e.g. Rollup(y, m, day) or (country, state, city)


b. 对于数据仓库,  rollup关键字能提升管理员维护汇总表数据的效率.


3. Rollup 语法以及例子

语法很简单:  Rollup 必须在selet 语句中 group by子句下使用. 语法如下:

SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)


下面用1个例子说明:

首先我们使用如下的表sales_amount作为检索的数据源:

这个标很简单具有5个列, 分别是部门编号, 姓名, 年, 月 ,销售额.



DEP  EMP       YEAR  MONTH     SALES
-------------------- ---------- ---------- ----------
     1 Jack         2012     11      3000
     1 Gordon    2012     11      2000
     1 Jack         2012     12      1000
     1 Gordon    2012     12      3000
     1 Jack         2013      1      2000
     1 Gordon    2013      1      1000
     2 Bill             2012     12      3000
     2 Ruler        2012     12      2000
     2 Bill            2013      1      1000
     2 Ruler        2013      1      3000
     3 Anna         2012     12      2000
     3 Cindy        2012     12      1000
     3 Anna         2013      1      3000
     3 Cindy        2013      1      4000

建表sql:

  1. create table sales_amount( dep number(3),  
  2.                             emp varchar(10),  
  3.                             year number(4),  
  4.                             month number(2),  
  5.                             sales number(8,2))  
  6.                               
  7. insert into sales_amount values(1, 'Jack',   2012, 11, 3000);  
  8. insert into sales_amount values(1, 'Gordon', 2012, 11, 2000);  
  9. insert into sales_amount values(1, 'Jack',   2012, 12, 1000);  
  10. insert into sales_amount values(1, 'Gordon', 2012, 12, 3000);  
  11. insert into sales_amount values(1, 'Jack',   2013, 1,  2000);  
  12. insert into sales_amount values(1, 'Gordon', 2013, 1,  1000);  
  13. insert into sales_amount values(2, 'Bill',   2012, 12, 3000);  
  14. insert into sales_amount values(2, 'Ruler',  2012, 12, 2000);  
  15. insert into sales_amount values(2, 'Bill',   2013, 1,  1000);  
  16. insert into sales_amount values(2, 'Ruler',  2013, 1,  3000);  
  17. insert into sales_amount values(3, 'Anna',   2012, 12, 2000);  
  18. insert into sales_amount values(3, 'Cindy',  2012, 12, 1000);  
  19. insert into sales_amount values(3, 'Anna',   2013, 1,  3000);  
  20. insert into sales_amount values(3, 'Cindy',  2013, 1,  4000);     
  21. commit;  

case 1:

我们首先用最常用group by语句对销售额按照部门- > 年 -> 月来进行销售额求和:


select dep, year, month, sum(sales)
from sales_amount
group by dep, year, month
order by dep, year, month    ##
group by 子句后一般跟随排序子句


输出:


       DEP YEAR      MONTH SUM(SALES)
---------- ---------- ---------- ----------
         1       2012         11       5000
         1       2012         12       4000
         1       2013          1       3000
         2       2012         12       5000
         2       2013          1       4000
         3       2012         12       3000
         3       2013          1       7000 


可以见到输出的数据中有7行,  实际上只对 mount 这一列做了汇总,  意思是如果我想知道部门1的总销售额, 部门1 2013年的销售额还必须进行进一步的计算.



case 2:

我们试下为group by中最后1个列 month 加上 rollup

select dep, year, month, sum(sales)
from sales_amount
group by  dep, year, rollup(month)
#不需要排序子句   n=1


       DEP       YEAR      MONTH SUM(SALES)
---------- ---------- ---------- ----------
         1       2012         11       5000
         1       2012         12       4000
         1       2012                  9000
         1       2013          1       3000
         1       2013                  3000
         2       2012         12       5000
         2       2012                  5000
         2       2013          1       4000
         2       2013                  4000
         3       2012         12       3000
         3       2012                  3000
         3       2013          1       7000
         3       2013                  7000


 看到result 中多了若干行对 month的上一级  的year进行汇总.  这时数据集的行具有两层, 也就是本文一开始提到的n+1层.


case 3:

这次我们在rollup里增加1个列. n=2

select dep, year, month, sum(sales)
from sales_amount
group by  dep, rollup(year, month)




       DEP       YEAR      MONTH SUM(SALES)
---------- ---------- ---------- ----------
         1       2012         11       5000
         1       2012         12       4000
         1       2012                  9000
         1       2013          1       3000
         1       2013                  3000
         1                                 12000
         2       2012         12       5000
         2       2012                  5000
         2       2013          1       4000
         2       2013                  4000
         2                                 9000
         3       2012         12       3000
         3       2012                  3000
         3       2013          1       7000
         3       2013                  7000
         3                                 10000


这次是对 month 和 year的上一层(就是year 和 dep) 进行汇总.

 可以见到这case比上1个case多了3行对于部门编号 dep 的汇总.  也就是year的上一层.

整个数据集有3层, 就是n+1 层啦.


case 4:

这次n=3.  把group by 的3个列都放入rollup 中.

select dep, year, month, sum(sales)
from sales_amount
group by  rollup(dep, year, month)


     DEP       YEAR      MONTH SUM(SALES)
---------- ---------- ---------- ----------
         1       2012         11       5000
         1       2012         12       4000
         1       2012                  9000
         1       2013          1       3000
         1       2013                  3000
         1                                 12000
         2       2012         12       5000
         2       2012                  5000
         2       2013          1       4000
         2       2013                  4000
         2                                 9000
         3       2012         12       3000
         3       2012                  3000
         3       2013          1       7000
         3       2013                  7000
         3                                 10000

                                            31000



可以见到数据集又多了1行(1层),    那个就是对dep的上一层. 就是所有的总数据了.

数据集共有4层 还是n+1哦



4.小结:

本文只是简单介绍,  

但起码我们可以了解到rollup的几个基本特性:

1. 用于group by后面

2. 用于汇总分组数据

3. 方向只能基于 group by的反方向 从右到左.


所以ocp有一题如下:

20. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
 
A. to find the groups forming the subtotal in a row
B. to create groupwise grand totals for the groups specified within a GROUP BY clause
C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is crosstabular report for calculating the subtotals


分析1:

A. in a row错误,  是从group by result中所有rows的每1个分组进行汇总

B. 不是create groupwise grand totals(总的汇总),  而是对指定的列都进行分层次的汇总.

D. 只能从右到左1个方向.

答案: C
  评论这张
 
阅读(143)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017