Oracle rollup 关键字用法简介.

来源：互联网发布：安徽预算软件编辑：程序博客网时间：2024/04/25 15:44

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:

create table sales_amount( dep number(3),                            emp varchar(10),                            year number(4),                            month number(2),                            sales number(8,2))                            insert into sales_amount values(1, 'Jack',   2012, 11, 3000);insert into sales_amount values(1, 'Gordon', 2012, 11, 2000);insert into sales_amount values(1, 'Jack',   2012, 12, 1000);insert into sales_amount values(1, 'Gordon', 2012, 12, 3000);insert into sales_amount values(1, 'Jack',   2013, 1,  2000);insert into sales_amount values(1, 'Gordon', 2013, 1,  1000);insert into sales_amount values(2, 'Bill',   2012, 12, 3000);insert into sales_amount values(2, 'Ruler',  2012, 12, 2000);insert into sales_amount values(2, 'Bill',   2013, 1,  1000);insert into sales_amount values(2, 'Ruler',  2013, 1,  3000);insert into sales_amount values(3, 'Anna',   2012, 12, 2000);insert into sales_amount values(3, 'Cindy',  2012, 12, 1000);insert into sales_amount values(3, 'Anna',   2013, 1,  3000);insert into sales_amount values(3, 'Cindy',  2013, 1,  4000);   commit;

case 1:

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

select dep, year, month, sum(sales)
from sales_amount
group by dep, year, month
order bydep, 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)

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