[轉]詳解Oracle進階分組函數(ROLLUP, CUBE, GROUPING SETS)

標籤：rollup   into   oda   com   組合   sdn   關係   red   statistic   

原文地址:http://blog.csdn.net/u014558001/article/details/42387929

本文主要講解 ROLLUP, CUBE, GROUPING SETS的主要用法，這些函數可以理解為GroupBy分組函數封裝後的精簡用法，相當於多個union all 的組合顯示效果，但是要比 多個union all的效率要高。

其實這些函數在時間的程式開發中應用的並不多，至少在我工作的多年時間中沒用過幾次，因為現在的各種開發工具/平台都內建了這些進階分組統計功能，使用的方便性及美觀性都比這些要好。但如果臨時查下資料，用這些函數還是不錯的。

建立測試環境

1.      建立表

 

[sql] view plain copy

1. createtable EMP2  
2. (  
3.   ID       NUMBER,  -- 員工編號  
4.   NAME     VARCHAR2(20), --姓名  
5.   SEX     VARCHAR2(2),  --性別  
6.   HIREDATE DATE,         --入職日期  
7.   BASE    VARCHAR2(20), --工作母地  
8.   DEPT    VARCHAR2(20), --所在部門  
9.   SAL     NUMBER        --月工資  
10. );  

 

2.      插入測試資料

 

[sql] view plain copy

1. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
2. values (107, ‘小月‘, ‘女‘, to_date(‘01-09-2013‘, ‘dd-mm-yyyy‘), ‘北京‘,‘營運‘, 9000);  
3. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
4. values (108, ‘小美‘, ‘女‘, to_date(‘01-06-2011‘, ‘dd-mm-yyyy‘), ‘上海‘,‘營運‘, 11000);  
5. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
6. values (101, ‘張三‘, ‘男‘, to_date(‘01-01-2011‘, ‘dd-mm-yyyy‘), ‘北京‘,‘財務‘, 8000);  
7. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
8. values (102, ‘李四‘, ‘男‘, to_date(‘01-01-2012‘, ‘dd-mm-yyyy‘), ‘北京‘,‘營運‘, 15000);  
9. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
10. values (103, ‘王五‘, ‘男‘, to_date(‘01-01-2013‘, ‘dd-mm-yyyy‘), ‘上海‘,‘營運‘, 6000);  
11. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
12. values (104, ‘趙六‘, ‘男‘, to_date(‘01-01-2014‘, ‘dd-mm-yyyy‘), ‘上海‘,‘財務‘, 10000);  
13. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
14. values (105, ‘小花‘, ‘女‘, to_date(‘01-08-2014‘, ‘dd-mm-yyyy‘), ‘上海‘,‘財務‘, 4000);  
15. insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)  
16. values (106, ‘小靜‘, ‘女‘, to_date(‘01-01-2015‘, ‘dd-mm-yyyy‘), ‘北京‘,‘財務‘, 6000);  
17. commit;  

 

 

3.     查看一下剛才插入的資料

 

[sql] view plain copy

1. select * from emp2;  

 

 

 

4.      先看下普通分組的效果

按照地區統計每個部門的總工資

[sql] view plain copy

1. select base,dept ,sum(sal) from emp2   
2. group by base,dept;  

查看結果如下：

 

 

ROLLUP（累計累加）

ROLLUP是對group by的擴充，因此，它只能出現在group by子句中，依賴於分組的列，對每個分組會產生摘要資料, rollup和group by聯合一起使用，達到了按group by列順序分組，並且實現小計和合計的功能。rollup分組還是有序的，先全部分組，然後對每個分組小計，最後合計。

rollup中列的順序不同，則統計的結果不同。因為它是按列從右遞減分組的。

比如 Group by  ROLLUP(A, B, C)，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作

 

按照地區統計每個部門的總工資，按工作母地匯總，再合計

 

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. groupbyrollup(base,dept);  

 

 

 

結果相當於

 

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. group by base,dept  
3. unionall  
4. select base,null,sum(sal) from emp2   
5. group by base,null  
6. unionall  
7. selectnull,null,sum(sal) from emp2   
8. group by null,null  
9. order by 1,2  

 

 

 

如果顛倒下rollup順序則結果如下：

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. group by rollup(dept,base);  

 

 

如果在實際查詢中，有的小計或合計我們不需要，那麼就要使用局部rollup,局部rollup就是將需要固定統計的列放在group by中，而不是放在rollup中。

 

 

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. group by dept,rollup(base);  

 

與group by rollup(dept,base)相比：去掉了最後一行的匯總，因為每次匯總要麼是dept,base，要麼是dept,null ，dept是固定的。

 

 

如果只希望看到合計則可以這樣寫：

 

[sql] view plain copy

1. select base,dept ,sum(sal) from emp2   
2. group by rollup((base,dept));  

 

 

 

 

 

CUBE(交叉列表)

CUBE也是對group by運算的一種擴充，它比rollup擴充更加精細，組合類別型更多，rollup是按組合的列從右至左遞減分組計算，而CUBE則是對所有可能的組合情況進行分組，這樣分組的情況更多，覆蓋所有的可能分組，並計算所有可能的分組的小計。

 

對於CUBE來說，列的名字只要一樣，那麼順序無所謂，結果都是一樣的，因為cube是各種可能情況的組合,只不過統計的結果順序不同而已。但是對於rollup來說，列的順序不同，則結果不同。

 

 

比如對工作母地和部門的交叉統計

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. group by cube(base,dept)  
3. order by 1,2;  

 

 

 

部分CUBE和部分ROLLUP類似，把需要固定統計的列放到group by中，不放到cube中就可以了。

如果cube中只有一個列，那麼和rollup的結果一致

 

 

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. group by dept,cube(base)  
3. order by1,2;  

 

 

 

rollup和cube區別：

如果是ROLLUP(A,B, C)的話，GROUP BY順序

(A、B、C)

(A、B)

(A)

最後對全表進行GROUPBY操作。

如果是GROUP BY CUBE(A, B, C)，GROUP BY順序
(A、B、C)

(A、B)

(A、C)

(A)，

(B、C)

(B)

(C)，

最後對全表進行GROUPBY操作。

 

GROUPING SETS

對group by的另一個擴充，專門對分組列分別進行小計計算，不包括合計。使用方式和rollup和cube一樣，都是放在group by中。

  

比如需要分別統計工作母地與部門的合計：

 

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. group by grouping sets(base,dept);  

結果為：

 

等價於

 

[sql] view plain copy

1. select base,null,sum(sal) from emp2   
2. group by  base,null  
3. unionall  
4. select null,dept,sum(sal) from emp2   
5. group by  null,dept;  

 

 

理解了groupingsets的原理我們用他實現rollup的功能也是可以的：

 

[sql] view plain copy

1. select base,dept,sum(sal) from emp2   
2. group by grouping sets ((base,dept),dept,null);  

 

效果如下：

 

 

 

 

grouping函數

在以上例子中，是用rollup和cube函數都會對結果集產生null，這時候可用grouping函數來確認該記錄是由哪個欄位得出來的

grouping函數用法，帶一個參數，參數為欄位名，結果是根據該欄位得出來的就返回1，反之返回0

例如：

 

[sql] view plain copy

1. select decode(grouping(base),1,‘所有地區‘,base) base,  
2. decode(grouping(dept),1,‘所有部門‘,dept)dept ,sum(sal) from emp2   
3. group by rollup(dept,base);  

 

 

 

 

更多ROLLUP，CUBE, GROUPING SETS與GROUP BY的關係可以參考Oracle官方文檔中的例子

 

http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8608

[轉]詳解Oracle進階分組函數(ROLLUP, CUBE, GROUPING SETS)