Oracle的group by聚合函數擴充cube rollup和grouping sets
聚合函數是oracle資料倉庫的基礎。為了提高距合的性能，oracle提供了group by條款的擴充。
ü cube
ü rollup
ü grouping
ü grouping sets
這幾個對sql的擴充使得查詢和報告都變得簡單和迅速。使用者通過使用這幾個擴充功能，可以1，簡化代碼程式設計；2，快速有效的查詢處理；3，減少用戶端和網絡負載。本文以執行個體的方式深入解析這幾種擴充的具體含義和使用環境。
考慮如下關系表。
create table mytest(
subcompany_id varchar2(10),
subcompany_name varchar2(40),
branch_id varchar2(10),
branch_name varchar2(40),
region_id varchar2(10),
region_name varchar2(40),
customer_id varchar2(10),
customer_name varchar2(40),
market_id varchar2(10),
market_name varchar2(49),
sales_count numeric(10,3)
);
comment on table mytest is '測試表';
comment on column mytest.subcompany_id is '分公司編号';
comment on column mytest.subcompany_name is '分公司名稱';
comment on column mytest.branch_id is '經營部編号';
comment on column mytest.branch_name is '經營部名稱';
comment on column mytest.region_id is '片區編号';
comment on column mytest.region_name is '片區名稱';
comment on column mytest.customer_id is '客戶編号';
comment on column mytest.customer_name is '客戶名稱';
comment on column mytest.market_id is '所屬市場級别';
comment on column mytest.market_name is '市場級别名稱';
comment on column mytest.sales_count is '銷售數量';
insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)
values('01','分公司1','0101','經營部1','010101','片區1','01010101','客戶1','01','片區1',1);
insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)
values('01','分公司1','0101','經營部1','010101','片區1','01010102','客戶2','02','片區2',1);
insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)
values('01','分公司1','0101','經營部1','010102','片區2','01010201','客戶1','01','片區1',1);
insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)
values('01','分公司1','0101','經營部1','010102','片區2','01010202','客戶2','02','片區2',1);
insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)
values('01','分公司1','0102','經營部1','010201','片區1','01020101','客戶1','01','片區1',1);
insert into mytest(subcompany_id,subcompany_name,branch_id,branch_name,region_id,region_name,customer_id,customer_name,market_id,market_name,sales_count)
values('01','分公司1','0102','經營部1','010202','片區2','01020202','客戶2','02','片區2',1);
rollup
rollup的行為非常直接，它根據grouping list的rollup條款建立合計：
首先，它計算grouping條款的标準聚合。
然後，它按照grouping list列從右到左進行更高層的聚合。
最後，建立n＋1層的總計。
例如： group by rollup(A,B,C),則oracle最後得到的聚合結果為(A,B,C), (A,B), (A),()
Rollup對group by 的擴充比較簡單，但非常高效，對一個查詢增加的開銷非常少。
考慮如下查詢。
selectsubcompany_name,branch_name,region_name,customer_name,
sum(sales_count)
frommytest
group by rollup(subcompany_name,branch_name,region_name,customer_name);
分公司1 經營部1 片區1 客戶1 3
分公司1 經營部1 片區1 客戶2 2
分公司1 經營部1 片區1 5
分公司1 經營部1 片區2 客戶1 2
分公司1 經營部1 片區2 客戶2 3
分公司1 經營部1 片區2 5
分公司1 經營部1 10
分公司1 經營部2 片區1 客戶1 1
分公司1 經營部2 片區1 1
分公司1 經營部2 片區2 客戶2 1
分公司1 經營部2 片區2 1
分公司1 經營部2 2
分公司1 12
12
上面的查詢傳回如下結果行：
1， 基于subcompany_name,branch_name,region_name,customer_name的聚合。
2， 基于subcompany_name,branch_name,region_name的聚合。
3， 基于subcompany_name,branch_name的聚合。
4， 基于subcompany_name的聚合。
5， 總計
使用者還可以使用rollup包含有限的幾個小計，文法如下：
Group by expr1,rollup(expr2,expr3)這種情況下，group by條款建立2＋1層小計。層次為（expr1，expr2，expr3）（expr1，expr2）（expr1）
cube
Cube進行grouping列規定的grouping，建立所有可能的聚合，例如規定了cube（customer_name,market_name）那麼傳回的結果将是2的2次方個。分别為（customer_name,market_name）(market_name)(customer_name)()
Cube擴充在計算交叉報表時非常有用，當然我們可以使用select union all替代rollup或cube，但這需要很多的select union all語句。這樣做不僅不夠高效，而且加長了sql語句，不友善維護和擴充。
考慮如下查詢：
selectsubcompany_name,branch_name,region_name,
customer_name,market_name,
sum(sales_count)
frommytest
group bysubcompany_name,branch_name,region_name,
cube(customer_name,market_name) ;
分公司1 經營部1 片區1 5
分公司1 經營部1 片區1 片區1 3
分公司1 經營部1 片區1 片區2 2
分公司1 經營部1 片區1 客戶1 3
分公司1 經營部1 片區1 客戶1 片區1 3
分公司1 經營部1 片區1 客戶2 2
分公司1 經營部1 片區1 客戶2 片區2 2
分公司1 經營部1 片區2 5
分公司1 經營部1 片區2 片區1 2
分公司1 經營部1 片區2 片區2 3
分公司1 經營部1 片區2 客戶1 2
分公司1 經營部1 片區2 客戶1 片區1 2
分公司1 經營部1 片區2 客戶2 3
分公司1 經營部1 片區2 客戶2 片區2 3
分公司1 經營部2 片區1 1
分公司1 經營部2 片區1 片區1 1
分公司1 經營部2 片區1 客戶1 1
分公司1 經營部2 片區1 客戶1 片區1 1
分公司1 經營部2 片區2 1
分公司1 經營部2 片區2 片區2 1
分公司1 經營部2 片區2 客戶2 1
分公司1 經營部2 片區2 客戶2 片區2 1
聯合使用rollup和cube來解決特殊查詢需求
rollup 和cube帶來的一個問題是，在返會的結果中如何能準确區分出那些是小計，哪些是彙總資料呢。這點可以使用grouping和grouping_id函數解決。
另外，我們還可以通過having過慮掉我們不希望在結果中出現的資料。
考慮查詢：給出所有機構的小計，并在此基礎上給出各市場級别的小計。
方案1 ：使用cube計算所有的結果，然後用having過慮得出符合條件的結果集合。
select grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name),
subcompany_name,branch_name,region_name,
customer_name,market_name,
sum(sales_count)
frommytest
group by cube(subcompany_name,branch_name,region_name,customer_name,market_name)
having(
grouping(subcompany_name) <=
grouping(branch_name)
and grouping(branch_name) <=
grouping(region_name)
and grouping(region_name) <=
grouping(customer_name));
這種方案的缺點在于，首先使用cube計算所有可能的彙總結果需要花費相當長的時間；其次由于結果給出了所有可能的彙總，而我們需要的隻是其中很小一部分，這種情況下使用having過慮結果集也是一件很麻煩的事情。
方案2
使用嵌套查詢，先得出rollup的結果，然後再利用現有結果跟market進行cube的group by計算。
selectsubcompany_name,branch_name,region_name,
customer_name,market_name,
sum(sales_count)
from(
selectsubcompany_name,branch_name,region_name,
customer_name,market_name,
sum(sales_count) sales_count
frommytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) ,market_name
)
group bysubcompany_name,branch_name,region_name,customer_name,
cube(market_name)
這種方式多執行了一次查詢，代碼長度增加，可讀性也不夠強。另外還要主意過慮掉在裡層查詢中已經彙總的結果。
方案3
聯合rollup和cube。
selectsubcompany_name,branch_name,region_name,
customer_name,market_name,
sum(sales_count) sales_count
frommytest
group by rollup(subcompany_name,branch_name,region_name,customer_name) ,
cube(market_name)
order by grouping(subcompany_name),
grouping(branch_name),
grouping(region_name),
grouping(customer_name),
grouping(market_name);
grouping sets
grouping sets提供了指定彙總集合條件的功能。例如在上面的查詢中，我們可以通過為select group by語句制定彙總條件（）
selectsubcompany_name,branch_name,region_name,
customer_name,market_name,
sum(sales_count) sales_count
frommytest
group by grouping sets((subcompany_name,branch_name,region_name,customer_name) ,
(subcompany_name,branch_name,region_name) ,
(subcompany_name,branch_name) ,
(subcompany_name) ,
(market_name) ,
(subcompany_name,branch_name,region_name,customer_name,market_name) ,
(subcompany_name,branch_name,region_name,market_name) ,
(subcompany_name,branch_name,market_name) ,
(subcompany_name,market_name) ,
() )
![捉 蟲 曆 險 記[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)