Oracle rollup 关键字用法简介.

忘是亡心i 2022-09-17 07:28 155阅读 0赞

## 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 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)**

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