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

-Advertisement-

原文地址:http://blog.csdn.net/u014558001/article/details/42387929 本文主要講解 ROLLUP, CUBE, GROUPING SETS的主要用法，這些函數可以理解為GroupBy分組函數封裝後的精簡用法，相當於多個union all 的組合顯 ...

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

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

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

創建測試環境

1. 創建表

[sql] view plain copy

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

2. 插入測試數據

[sql] view plain copy

insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (107, '小月', '女', to_date('01-09-2013', 'dd-mm-yyyy'), '北京','營運', 9000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (108, '小美', '女', to_date('01-06-2011', 'dd-mm-yyyy'), '上海','營運', 11000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (101, '張三', '男', to_date('01-01-2011', 'dd-mm-yyyy'), '北京','財務', 8000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (102, '李四', '男', to_date('01-01-2012', 'dd-mm-yyyy'), '北京','營運', 15000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (103, '王五', '男', to_date('01-01-2013', 'dd-mm-yyyy'), '上海','營運', 6000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (104, '趙六', '男', to_date('01-01-2014', 'dd-mm-yyyy'), '上海','財務', 10000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (105, '小花', '女', to_date('01-08-2014', 'dd-mm-yyyy'), '上海','財務', 4000);
insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
values (106, '小靜', '女', to_date('01-01-2015', 'dd-mm-yyyy'), '北京','財務', 6000);
commit;

3. 查看一下剛纔插入的數據

[sql] view plain copy

select * from emp2;

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

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

[sql] view plain copy

select base,dept ,sum(sal) from emp2
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

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

結果相當於

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by base,dept
unionall
select base,null,sum(sal) from emp2
group by base,null
unionall
selectnull,null,sum(sal) from emp2
group by null,null
order by 1,2

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

[sql] view plain copy

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

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

[sql] view plain copy

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

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

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

[sql] view plain copy

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

CUBE(交叉列表)

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

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

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

[sql] view plain copy

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

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

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

[sql] view plain copy

select base,dept,sum(sal) from emp2
group by dept,cube(base)
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

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

結果為：

等價於

[sql] view plain copy

select base,null,sum(sal) from emp2
group by base,null
unionall
select null,dept,sum(sal) from emp2
group by null,dept;

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

[sql] view plain copy

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

效果如下：

grouping函數

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

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

例如：

[sql] view plain copy

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

您的分享是我們最大的動力!

-Advertisement-

更多相關文章

mysql存儲之int

開始之前給大家出個問題，資料庫表test中兩個欄位 a int(2),b int(3)，現在想執行下麵的插入語句思考是否可以插入？答案是能插入再看下麵的語句思考能不能插入？註意第一個數字多了一位變成了11位資料庫會報如下錯誤 “Out of range value for column ' ...
轉換CLOB欄位類型為VARCHAR2, lob類型不支持的sql語句

轉自：https://blog.csdn.net/e_wsq/article/details/7561209 步驟： 1.建立一個臨時varchar2欄位用來保存數據 2.將clob的內容截取後更新到varchar2欄位中 update table_wonder set tempcolumn = d ...
命令行操作mysql 未完待續......

複製數據表刪除表中某個欄位查看表結構未完待續...... ...
SQL Server 查找統計信息的相關採樣信息

SQL Server 查找統計信息的相關採樣信息有時候我們會遇到，由於統計信息不准確導致優化器生成了一個錯誤的執行計劃（或者這樣表達：一個較差的執行計劃），從而引起了系統性能問題。那麼如果我們懷疑這個錯誤的執行計劃是由於統計信息不准確引起的。那麼我們如何判斷統計信息不准確呢？當然首先得去查看實際執... ...
不一樣的 SQL Server 日期格式化

最近統計一些數據，需要按天/按小時/按分鐘來統計，涉及到一些日期的格式化，網上看了一些文章大部分都是使用 `CONVERT` 來轉換的，SQL Server 從 2012 開始增加了 `FORMAT` 方法，可以使用 `FORMAT` 來格式化日期，更標準化，更具可定製性，而且和 C# 里的日期格式... ...
Postgres 優雅存儲樹形數據

碰到一個樹形數據需要存儲再數據控制，碰到以下兩個問題：在PG資料庫中如何表達樹形數據如何有效率的查詢以任意節點為Root的子樹測試數據為了更加簡單一些，我們將使用一下數據簡單的自引用當設計自引用表(有時候自己join自己)。最簡單明瞭的就是有一個欄位。然後插入一些樣例數據，用來關聯 ...
Postgres全文搜索功能

當構建一個Web應用時，經常被要求加上搜索功能。其實有時候我們也不知道我要搜索個啥，反正就是要有這個功能。搜索確實很重要的特性，這也是為什麼像Elasticsearch和Solr這樣基於Lucene的資料庫越來越流行。這兩個是好用，但是在構建Web應用時，有時候感覺像是殺雞用牛刀。所以我們需要選擇輕 ...
SQL Server中是否可以準確獲取最後一次索引重建的時間？

在SQL Server中，我們能否找到索引的創建時間？最後一次索引重建（Index Rebuild)的時間？最後一次索引重組（INDEX REORGANIZE)的時間呢？答案是我們無法準確的找到索引的創建時間、最後一次索引重組時間，最後一次索引重建的時間。其實就目前SQL Server的各個版... ...