【CUBE】Oracle分組函式之CUBE魅力
Oracle的CUBE與ROLLUP功能很相似，也是在資料統計分析領域的一把好手。
關於ROLLUP的查詢統計功能請參考文章《【ROLLUP】Oracle分組函式之ROLLUP魅力》（http://space.itpub.net/519536/viewspace-610995）。
1.先看一下ROLLUP的資料統計效果
1）建立測試表group_test
SECOOLER@ora11g> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
Table created.
2）初始化資料
insert into group_test values (10,'Coding', 'Bruce',1000);
insert into group_test values (10,'Programmer','Clair',1000);
insert into group_test values (10,'Architect', 'Gideon',1000);
insert into group_test values (10,'Director', 'Hill',1000);
insert into group_test values (20,'Coding', 'Jason',2000);
insert into group_test values (20,'Programmer','Joey',2000);
insert into group_test values (20,'Architect', 'Martin',2000);
insert into group_test values (20,'Director', 'Michael',2000);
insert into group_test values (30,'Coding', 'Rebecca',3000);
insert into group_test values (30,'Programmer','Rex',3000);
insert into group_test values (30,'Architect', 'Richard',3000);
insert into group_test values (30,'Director', 'Sabrina',3000);
insert into group_test values (40,'Coding', 'Samuel',4000);
insert into group_test values (40,'Programmer','Susy',4000);
insert into group_test values (40,'Architect', 'Tina',4000);
insert into group_test values (40,'Director', 'Wendy',4000);
commit;
3）初始化之後的資料情況如下：
SECOOLER@ora11g> set pages 100
SECOOLER@ora11g> select * from group_test;
GROUP_ID JOB NAME SALARY
---------- ---------- ---------- ----------
10 Coding Bruce 1000
10 Programmer Clair 1000
10 Architect Gideon 1000
10 Director Hill 1000
20 Coding Jason 2000
20 Programmer Joey 2000
20 Architect Martin 2000
20 Director Michael 2000
30 Coding Rebecca 3000
30 Programmer Rex 3000
30 Architect Richard 3000
30 Director Sabrina 3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy 4000
16 rows selected.
4）ROLLUP的資料統計效果
sec@ora10g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);
GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
10 Coding 0 0 1000
10 Director 0 0 1000
10 Architect 0 0 1000
10 Programmer 0 0 1000
10 0 1 4000
20 Coding 0 0 2000
20 Director 0 0 2000
20 Architect 0 0 2000
20 Programmer 0 0 2000
20 0 1 8000
30 Coding 0 0 3000
30 Director 0 0 3000
30 Architect 0 0 3000
30 Programmer 0 0 3000
30 0 1 12000
40 Coding 0 0 4000
40 Director 0 0 4000
40 Architect 0 0 4000
40 Programmer 0 0 4000
40 0 1 16000
1 1 40000
21 rows selected.
2.進一步體驗CUBE的魅力
sec@ora10g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by cube(group_id, job) order by 1;
GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
10 Architect 0 0 1000
10 Coding 0 0 1000
10 Director 0 0 1000
10 Programmer 0 0 1000
10 0 1 4000
20 Architect 0 0 2000
20 Coding 0 0 2000
20 Director 0 0 2000
20 Programmer 0 0 2000
20 0 1 8000
30 Architect 0 0 3000
30 Coding 0 0 3000
30 Director 0 0 3000
30 Programmer 0 0 3000
30 0 1 12000
40 Architect 0 0 4000
40 Coding 0 0 4000
40 Director 0 0 4000
40 Programmer 0 0 4000
40 0 1 16000
Architect 1 0 10000
Coding 1 0 10000
Director 1 0 10000
Programmer 1 0 10000
1 1 40000
25 rows selected.
解釋如上結果中GROUPING函式返回值“0”和“1”的含義。
如果顯示“1”表示CUBE函式對應的列（例如JOB欄位）是由於CUBE函式所產生的空值對應的資訊，即對此列進行彙總計算後的結果。
如果顯示“0”表示此行對應的這列參未與ROLLUP函式分組彙總活動。
如果還是沒有理解清楚，請參見Oracle官方文件中的描述內容：“Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is, if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value, including a storedNULL, returns a 0.”
3.仔細觀察一下，CUBE與ROLLUP之間的細微差別
rollup(a,b) 統計列包含：(a,b)、(a)、()
rollup(a,b,c) 統計列包含：(a,b,c)、(a,b)、(a)、()
……以此類推ing……
cube(a,b) 統計列包含：(a,b)、(a)、(b)、()
cube(a,b,c) 統計列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
……以此類推ing……
So，上面例子中CUBE的結果比ROLLUP多了下面關於第一列GROUP_ID的統計資訊：
Architect 1 0 10000
Coding 1 0 10000
Director 1 0 10000
4.小結
CUBE在ROLLUP的基礎上進一步從各種維度上給出細化的統計彙總結果。
CUBE與GROUP BY的關係可以參考Oracle官方文件中的例子，連結如下：，連結如下：《CUBE Extension to GROUP BY》
Good luck.
secooler
12.04.23
-- The End --
關於ROLLUP的查詢統計功能請參考文章《【ROLLUP】Oracle分組函式之ROLLUP魅力》（http://space.itpub.net/519536/viewspace-610995）。
1.先看一下ROLLUP的資料統計效果
1）建立測試表group_test
SECOOLER@ora11g> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
Table created.
2）初始化資料
insert into group_test values (10,'Coding', 'Bruce',1000);
insert into group_test values (10,'Programmer','Clair',1000);
insert into group_test values (10,'Architect', 'Gideon',1000);
insert into group_test values (10,'Director', 'Hill',1000);
insert into group_test values (20,'Coding', 'Jason',2000);
insert into group_test values (20,'Programmer','Joey',2000);
insert into group_test values (20,'Architect', 'Martin',2000);
insert into group_test values (20,'Director', 'Michael',2000);
insert into group_test values (30,'Coding', 'Rebecca',3000);
insert into group_test values (30,'Programmer','Rex',3000);
insert into group_test values (30,'Architect', 'Richard',3000);
insert into group_test values (30,'Director', 'Sabrina',3000);
insert into group_test values (40,'Coding', 'Samuel',4000);
insert into group_test values (40,'Programmer','Susy',4000);
insert into group_test values (40,'Architect', 'Tina',4000);
insert into group_test values (40,'Director', 'Wendy',4000);
commit;
3）初始化之後的資料情況如下：
SECOOLER@ora11g> set pages 100
SECOOLER@ora11g> select * from group_test;
GROUP_ID JOB NAME SALARY
---------- ---------- ---------- ----------
10 Coding Bruce 1000
10 Programmer Clair 1000
10 Architect Gideon 1000
10 Director Hill 1000
20 Coding Jason 2000
20 Programmer Joey 2000
20 Architect Martin 2000
20 Director Michael 2000
30 Coding Rebecca 3000
30 Programmer Rex 3000
30 Architect Richard 3000
30 Director Sabrina 3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy 4000
16 rows selected.
4）ROLLUP的資料統計效果
sec@ora10g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by rollup(group_id, job);
GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
10 Coding 0 0 1000
10 Director 0 0 1000
10 Architect 0 0 1000
10 Programmer 0 0 1000
10 0 1 4000
20 Coding 0 0 2000
20 Director 0 0 2000
20 Architect 0 0 2000
20 Programmer 0 0 2000
20 0 1 8000
30 Coding 0 0 3000
30 Director 0 0 3000
30 Architect 0 0 3000
30 Programmer 0 0 3000
30 0 1 12000
40 Coding 0 0 4000
40 Director 0 0 4000
40 Architect 0 0 4000
40 Programmer 0 0 4000
40 0 1 16000
1 1 40000
21 rows selected.
2.進一步體驗CUBE的魅力
sec@ora10g> select group_id,job,grouping(GROUP_ID),grouping(JOB),sum(salary) from group_test group by cube(group_id, job) order by 1;
GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
10 Architect 0 0 1000
10 Coding 0 0 1000
10 Director 0 0 1000
10 Programmer 0 0 1000
10 0 1 4000
20 Architect 0 0 2000
20 Coding 0 0 2000
20 Director 0 0 2000
20 Programmer 0 0 2000
20 0 1 8000
30 Architect 0 0 3000
30 Coding 0 0 3000
30 Director 0 0 3000
30 Programmer 0 0 3000
30 0 1 12000
40 Architect 0 0 4000
40 Coding 0 0 4000
40 Director 0 0 4000
40 Programmer 0 0 4000
40 0 1 16000
Architect 1 0 10000
Coding 1 0 10000
Director 1 0 10000
Programmer 1 0 10000
1 1 40000
25 rows selected.
解釋如上結果中GROUPING函式返回值“0”和“1”的含義。
如果顯示“1”表示CUBE函式對應的列（例如JOB欄位）是由於CUBE函式所產生的空值對應的資訊，即對此列進行彙總計算後的結果。
如果顯示“0”表示此行對應的這列參未與ROLLUP函式分組彙總活動。
如果還是沒有理解清楚，請參見Oracle官方文件中的描述內容：“Using a single column as its argument,GROUPINGreturns 1 when it encounters aNULLvalue created by aROLLUPorCUBEoperation. That is, if theNULLindicates the row is a subtotal,GROUPINGreturns a 1. Any other type of value, including a storedNULL, returns a 0.”
3.仔細觀察一下，CUBE與ROLLUP之間的細微差別
rollup(a,b) 統計列包含：(a,b)、(a)、()
rollup(a,b,c) 統計列包含：(a,b,c)、(a,b)、(a)、()
……以此類推ing……
cube(a,b) 統計列包含：(a,b)、(a)、(b)、()
cube(a,b,c) 統計列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
……以此類推ing……
So，上面例子中CUBE的結果比ROLLUP多了下面關於第一列GROUP_ID的統計資訊：
Architect 1 0 10000
Coding 1 0 10000
Director 1 0 10000
4.小結
CUBE在ROLLUP的基礎上進一步從各種維度上給出細化的統計彙總結果。
CUBE與GROUP BY的關係可以參考Oracle官方文件中的例子，連結如下：，連結如下：《CUBE Extension to GROUP BY》
Good luck.
secooler
12.04.23
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1617739/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- group by分組函式之rollup與cube用法函式
- [SQL] rollup & cube 分組函式用法及一例SQL函式
- 【ROLLUP】Oracle分組函式之ROLLUP魅力Oracle函式
- oracle 高階分組group by cube擴充Oracle
- Oracle的rollup、cube、grouping sets函式Oracle函式
- 分析函式rollup||cube學習函式
- rollup cube 超匯聚函式函式
- Oracle PL/SQL之GROUP BY CUBEOracleSQL
- 分析函式 - CUBE和GROUPING SETS函式
- Oracle OCP(12)：ROLLUP & CUBEOracle
- oracle10g後面的聚合運算函式：cube、rollup等Oracle函式
- [Developer] CubeDeveloper
- 解析數倉OLAP函式：ROLLUP、CUBE、GROUPING SETS函式
- ORACLE ROLLUP和CUBE的使用Oracle
- Cube 技術解讀 | Cube 小程式技術詳解
- Cube 技術解讀 | Cube 渲染設計的前世今生
- Cube 技術解讀 | Cube 卡片技術棧詳解
- Cube 緯度分析
- oracle rollup,cube子句的應用舉例；Oracle
- 【kylin】Failed to build cubeAIUI
- SAP BW:CUBE size 分析
- mysql與oracle的分組函式MySqlOracle函式
- MySQL ROLLUP和CUBE問題MySql
- Remote Cube的三兩事REM
- group by中cube含義解析
- 【實驗】分析函式之魅力展示函式
- 關於Oracle自定義分組函式Oracle函式
- Cube-UI 最近做的更新UI
- vue-music 音樂 App 之 cube-ui 重構VueAPPUI
- 資料庫之DQL排序&分組&函式資料庫排序函式
- MySQL之集合函式與分組查詢MySql函式
- cube ui RecycleList 不自動追加資料UI
- 輔助Cube建模的程式設計程式設計
- ORACLE單行函式與多行函式之七：多行函式之分組函式示例Oracle函式
- Oracle 分組彙總統計函式的使用Oracle函式
- oracle group by中cube和rollup字句的使用方法及區別Oracle
- Apache Kylin 入門 5 – 構建 CubeApache
- Apache Kylin 入門 6 - 優化 CubeApache優化