1. 什么是Rollup

在PL/sql 中,? Rollup 這個關鍵字通常用于select 語句的 group by 后面.

在group by 后面使用rollup關鍵字,? select語句就可以對指定的分組的數(shù)據(jù)左匯總小計(求和).

一旦使用rollup,?? 在數(shù)據(jù)集中, rollup的列會被自動排序.

rollup 會創(chuàng)建n+1 層次的匯總. n是rollup 匯總的列的個數(shù).? 在實際操作中. 假如我們在rollup后面指定匯總 時間, 區(qū)域, 部門的匯總的數(shù)據(jù).(n=3), 那么數(shù)據(jù)集的會含有4級的匯集行數(shù)據(jù).

當對多個列進行匯集匯總時, 方向只能根據(jù)group by 的方向從右向左. 例如, 當使用group by( year, month, day),? 匯總方向就是從day -> month -> year 的方向匯總.

2. 什么情況下應該使用rollup

上面已經提過了, 當我們需要對group by 分組數(shù)據(jù)集進行匯總小計時,? 可以使用rollup 擴展.

a.? rollup 會幫助我們對數(shù)據(jù)集進行按照時間或地理區(qū)域進行分層次的匯總.? 實際操作中,? 我們可以在查詢語句中指定要匯總小計的列. e.g. Rollup(y, m, day) or (country, state, city)

b. 對于數(shù)據(jù)倉庫,? rollup關鍵字能提升管理員維護匯總表數(shù)據(jù)的效率.

3. Rollup 語法以及例子

語法很簡單:? Rollup 必須在selet 語句中 group by子句下使用. 語法如下:

SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)

下面用1個例子說明:

首先我們使用如下的表sales_amount作為檢索的數(shù)據(jù)源:

這個標很簡單具有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?

可以見到輸出的數(shù)據(jù)中有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進行匯總.? 這時數(shù)據(jù)集的行具有兩層, 也就是本文一開始提到的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的上一層.

整個數(shù)據(jù)集有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

可以見到數(shù)據(jù)集又多了1行(1層),??? 那個就是對dep的上一層. 就是所有的總數(shù)據(jù)了.

數(shù)據(jù)集共有4層 還是n+1哦

4.小結:

本文只是簡單介紹,??

但起碼我們可以了解到rollup的幾個基本特性:

1. 用于group by后面

2. 用于匯總分組數(shù)據(jù)

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個方向.