GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例
Oracle的GROUP BY語句除了最基本的語法外，還支援ROLLUP和CUBE語句。CUBE ROLLUP 是用於統計資料的。
實驗使用SCOTT使用者的EMP表測試
1.僅使用GROUP BY分組，GROUP BY後的單列可以用括號，也可以不用。以下兩種寫法作用一樣：
SCOTT@bys1>SELECT deptno, avg(sal) FROM emp GROUP BY deptno;SCOTT@bys1>SELECT deptno, avg(sal) FROM emp GROUP BY (deptno);
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
正常分組，GROUP BY後的多個列之前要用逗號隔開，列名可以寫到 括號裡，也可以不使用括號。
下面三種寫法作用一樣：
SCOTT@bys1>SELECT nvl(to_char(deptno),'heji') ,job, avg(sal) FROM emp GROUP BY (job,deptno);
SCOTT@bys1>SELECT deptno,job, avg(sal) FROM emp GROUP BY (job,deptno);
SCOTT@bys1>SELECT deptno,job, avg(sal) FROM emp GROUP BY job,deptno;
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
10 CLERK 1300
30 SALESMAN 1400
20 ANALYST 3000
30 MANAGER 2850
10 MANAGER 2450
30 CLERK 950
20 CLERK 950
##################################################################################
2.在GROUP BY語句中使用ROLLUP：
使用ROLLUP操作符時，在生成原有統計結果基礎上，生成橫向小計結果。為每個分組返回一條小計記錄，併為全部分組返回總計。
下面兩個語句只用到了一個分組列，所以返回的是一個總計。
SCOTT@bys1>SELECT deptno, avg(sal) FROM emp GROUP BY rollup(deptno);
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175
30 1566.66667
2073.21429
使用NVL，如果deptno列為NULL，則用'heji'。可以使結果更美觀。
SCOTT@bys1>SELECT nvl(to_char(deptno),'heji') as deptno, avg(sal) FROM emp GROUP BY rollup(deptno);
DEPTNO AVG(SAL)
---------------------------------------- ----------
10 2916.66667
20 2175
30 1566.66667
heji 2073.21429
下面語句用到了兩個列。
可以看到下面查詢產生了如下結果行：-----它按照grouping list列從右到左進行更高層的聚合
1.對(deptno,job)進行GROUP BY，即按部門分組，相同部門裡再按相同崗位進行分組。聚合統計同一部門相同崗位的平均工資。
2.對分組後的(deptno）進行GROUP BY，即相同部門的分組聚合統計。這裡就是統計同一部門所有人的平均工資
3.對所有員工的平均工資聚合統計
這裡如果是GROUP BY rollup(a,b,c);對(a,b,c)三列分組的話，就是先對(a,b,c)進行GROUP BY，再對(a,b)進行GROUP BY，再對(a)進行GROUP BY，再對全表GROUP BY。
即ROLLUP(1,2,N)時，GROUP BY的所有可能的GROUP BY數是N+1個，比如ROLLUP(a,b,c);時，總共有4個彙總。
DEPTNO JOB AVG(SAL)
---------------------------------------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 2916.66667
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
20 2175
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
30 1566.66667
heji 2073.21429
########################################################################################################
3.在GROUP BY中使用CUBE:
使用CUBE操作符時，在生成原有統計結果基礎上，生成縱向小計結果。返回所有列組合的小計資訊，同時在最後顯示總計資訊
下面兩個語句只用到了一個分組列，所以返回的是一個總計。--ROLLUP總計在下面，CUBE的統計結果在上面。
SCOTT@bys1>SELECT nvl(to_char(deptno),'zongji'), avg(sal) FROM emp GROUP BY cube(deptno);
NVL(TO_CHAR(DEPTNO),'ZONGJI') AVG(SAL)
---------------------------------------- ----------
zongji 2073.21429
10 2916.66667
20 2175
30 1566.66667
下面語句用到了兩個列。
可以看到下面查詢產生了如下結果行：
1.是對(deptno,job)進行GROUP BY，即按部門、同一部門相同崗位GROUP BY。統計了同一部門相同崗位的平均工資及同一部門所有崗位的平均工資。
2.又對每個(job)進行GROUP BY，即不管部門，對錶中所有行按JOB列進行GROUP BY。統計了各個崗位的平均工資
3.最後統計了所有員工的平均工資。即統計的第2步分組後的各種崗位的平均工資--也可能是全部員工的工資平均。
如果是GROUP BY CUBE(a,b,c);首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，再對全表進行GROUP BY。
即CUBE(1,2,N)時，GROUP BY的所有可能的GROUP BY數是2的N次方，比如CUBE(a,b,c);時，總共有8個。
DEPTNO JOB AVG(SAL)
---------------------------------------- --------- ----------
zongji 2073.21429
zongji CLERK 1037.5
zongji ANALYST 3000
zongji MANAGER 2758.33333
zongji SALESMAN 1400
zongji PRESIDENT 5000
10 2916.66667
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 2175
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
30 1566.66667
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
實驗總結：ROLLUP非常高效，對一個查詢增加的開銷非常少；CUBE相對更耗費資源。
在GROUP BY子句有列（a,b)兩列時，ROLLUP統計（a,b)，（a)；而CUBE統計了（a,b)，（a)，（b)。
在此實驗中就是：
ROLLUP統計了按(deptno,job)分組彙總，按(deptno)分組彙總，最後對全表進行GROUP BY操作。
CUBE統計了按(deptno,job)分組彙總，按(deptno)分組彙總，按(job)分組彙總，最後對全表進行GROUP BY操作。
#############################################################################################
4.grouping函式，解決在返會的結果中如何能準確區分出那些是小計，哪些是彙總資料。GROUPING只能在使用ROLLUP或CUBE的查詢中使用。
對輸入列返回0或1，如果該行資料使用了資料的列中的資訊，即此列資料參與ROLLUP/CUBE函式分組彙總活動，則輸出0；沒有用到則輸出1
或者說，對於該行得出的統計資料，需要從輸入列中選擇資料的話，輸出0；不需要選擇資料的就輸出1
如下：GROUP BY rollup(deptno,job)時，可以看到在不同聚合統計列deptno,job欄位的使用情況。0使用該欄位，1未使用。
SCOTT@bys1>SELECT nvl(to_char(deptno),'zongji') as deptno,job,avg(sal),grouping(deptno),grouping(job) FROM emp GROUP BY rollup(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------------------------------------- --------- ---------- ---------------- -------------
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
10 2916.66667 0 1
20 CLERK 950 0 0
20 ANALYST 3000 0 0
20 MANAGER 2975 0 0
20 2175 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 1400 0 0
30 1566.66667 0 1
zongji 2073.21429 1 1
如下：GROUP BY cube(deptno,job)時，可以看到在不同聚合統計列deptno,job欄位的使用情況。0使用該欄位，1未使用。
SCOTT@bys1>SELECT nvl(to_char(deptno),'zongji') as deptno,job,avg(sal),grouping(deptno),grouping(job) FROM emp GROUP BY cube(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------------------------------------- --------- ---------- ---------------- -------------
zongji 2073.21429 1 1
zongji CLERK 1037.5 1 0
zongji ANALYST 3000 1 0
zongji MANAGER 2758.33333 1 0
zongji SALESMAN 1400 1 0
zongji PRESIDENT 5000 1 0
10 2916.66667 0 1
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
20 2175 0 1
20 CLERK 950 0 0
20 ANALYST 3000 0 0
20 MANAGER 2975 0 0
30 1566.66667 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 1400 0 0
5.grouping SETS函式
只返回統計資訊，就是上一個查詢中提出兩個 GROUPING 的兩個列全為0或全為1的都過濾掉。SCOTT@bys1>SELECT nvl(to_char(deptno),'zongji') as deptno,job,avg(sal),grouping(deptno),grouping(job) FROM emp GROUP BY grouping sets(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------------------------------------- --------- ---------- ---------------- -------------
zongji CLERK 1037.5 1 0
zongji SALESMAN 1400 1 0
zongji PRESIDENT 5000 1 0
zongji MANAGER 2758.33333 1 0
zongji ANALYST 3000 1 0
30 1566.66667 0 1
20 2175 0 1
10 2916.66667 0 1
不過當查詢只有一個聚合列時，是將全表統計的給過濾了：
SCOTT@bys1>SELECT deptno, avg(sal),grouping(deptno) FROM emp GROUP BY cube(deptno);
DEPTNO AVG(SAL) GROUPING(DEPTNO)
---------- ---------- ----------------
2073.21429 1
10 2916.66667 0
20 2175 0
30 1566.66667 0
SCOTT@bys1>SELECT deptno, avg(sal),grouping(deptno) FROM emp GROUP BY
grouping sets(deptno);
DEPTNO AVG(SAL) GROUPING(DEPTNO)
---------- ---------- ----------------
30 1566.66667 0
20 2175 0
10 2916.66667 0
6.GROUPING_ID(column_name1,column_name2…)----這個沒看懂，也沒實驗
這個返回一個整數，最小為0，這個整數怎麼確定，將上面的輸入的列，分配以bit，column_name1 的在column_name2的左邊，
這樣就形成了一個二進位制數，將它轉為10進位制就是獲得的數了，
怎麼確定每位的0和1？
每位的值，和 GROUPING(column_name)的值是一樣的，
例如上面的GROUPING(column_name1) GROUPING(column_name2) 為1 和0
則獲得的值為 0b10 ，即2.
相關文章
- Oracle的rollup、cube、grouping sets函式Oracle函式
- 解析數倉OLAP函式：ROLLUP、CUBE、GROUPING SETS函式
- Oracle OCP(13)：GROUPING & GROUPING_ID & GROUP_ID & GROUPING SETSOracle
- 分析函式 - CUBE和GROUPING SETS函式
- 分析函式 - ROLLUP和GROUPING SETS函式
- group by中rollup的以及grouping含義解析
- SQL 2012 Group By Rollup, GroupingSQL
- [Developer] Grouping setsDeveloper
- SQL grouping sets 子句SQL
- 分析函式 - GROUPING SETS函式
- 關於 grouping sets 學習
- oracle group by中cube和rollup字句的使用方法及區別Oracle
- Grouping函式使用函式
- MySQL8.0-分組函式ROLLUP的基本用法（GROUPING）MySql函式
- SQL groupingSQL
- group by分組函式之rollup與cube用法函式
- 資料倉儲開發報表常用函式—ROLLUP和GROUPING函式
- ORACLE ROLLUP和CUBE的使用Oracle
- group by中cube含義解析
- Oracle OCP(12)：ROLLUP & CUBEOracle
- 分析函式 - GROUPING_ID函式
- GROUPING_ID的應用
- MySQL ROLLUP和CUBE問題MySql
- 分析函式rollup||cube學習函式
- rollup cube 超匯聚函式函式
- R語言中的迴圈函式（Grouping Function）R語言函式Function
- oracle rollup,cube子句的應用舉例；Oracle
- Oracle PL/SQL之GROUP BY CUBEOracleSQL
- mssql sqlserver 關鍵字 GROUPING用法簡介及說明SQLServer
- Dual Differential Grouping: A More General Decomposition Method for Large-Scale Optimization
- Spark2 Dataset多維度統計cube與rollupSpark
- [SQL] rollup & cube 分組函式用法及一例SQL函式
- oracle 高階分組group by cube擴充Oracle
- SAP 使用BP建立供應商報錯 - You cannot create a vendor with grouping G001 - 對策
- oracle10g後面的聚合運算函式：cube、rollup等Oracle函式
- PostgreSQL 原始碼解讀（73）- 查詢語句#58（grouping_planner函式...SQL原始碼函式
- SAP S4HANA BP事務程式碼初始介面的ROLE和Grouping配置
- Storm概念學習系列之Stream訊息流 和 Stream Grouping 訊息流組ORM