【ROLLUP】Oracle分組函式之ROLLUP魅力
本文透過演示給出Oracle ROLLUP分組函式的用法，體驗一下Oracle在統計查詢領域中的函式魅力。ROLLUP分組函式可以理解為Group By分組函式封裝後的精簡用法，這裡同時給出ROLLUP的Group By的改寫思路。
1.初始化實驗壞境
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.
2.先看一下普通分組的效果：對group_id進行普通的group by操作---按照小組進行分組
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id;
GROUP_ID SUM(SALARY)
---------- -----------
30 12000
20 8000
40 16000
10 4000
3.對group_id進行普通的roolup操作---按照小組進行分組，同時求總計
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by rollup(group_id);
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
使用Group By語句翻譯一下上面的SQL語句如下（union all一個統計所有資料的行）：
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id
2 union all
3 select null, sum(salary) from group_test
4 order by 1;
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
4.再看一個rollup兩列的情況
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by rollup(group_id, job);
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
10 Coding 1000
10 Director 1000
10 Architect 1000
10 Programmer 1000
10 4000
20 Coding 2000
20 Director 2000
20 Architect 2000
20 Programmer 2000
20 8000
30 Coding 3000
30 Director 3000
30 Architect 3000
30 Programmer 3000
30 12000
40 Coding 4000
40 Director 4000
40 Architect 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
上面的SQL語句該如何使用Group By進行翻譯呢？
答案如下：
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by group_id, job
2 union all
3 select group_id,null,sum(salary) from group_test group by group_id
4 union all
5 select null,null,sum(salary) from group_test
6 order by 1,2;
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
10 Architect 1000
10 Coding 1000
10 Director 1000
10 Programmer 1000
10 4000
20 Architect 2000
20 Coding 2000
20 Director 2000
20 Programmer 2000
20 8000
30 Architect 3000
30 Coding 3000
30 Director 3000
30 Programmer 3000
30 12000
40 Architect 4000
40 Coding 4000
40 Director 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
5.補充一步，體驗一下GROUPING函式的效果
直接看效果就OK啦：
SECOOLER@ora11g> 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.
看出來什麼效果了麼？
有的同學還是沒有看出來，小小的解釋一下：
如果顯示“1”表示GROUPING函式對應的列（例如JOB欄位）是由於ROLLUP函式所產生的空值對應的資訊，即對此列進行彙總計算後的結果。
如果顯示“0”表示此行對應的這列參未與ROLLUP函式分組彙總活動。
如果還是沒有理解清楚，請參見Oracle官方文件中的描述內容：“Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.”
6.小結
ROLLUP在資料統計和報表生成過程中帶來極大的便利，而且效率比起來Group By + Union組合方法效率高得多。這也體現了Oracle在SQL統計分析上人性化、自動化、高效率的特點。
ROLLUP與GROUP BY的關係可以參考Oracle官方文件中的例子，連結如下：《ROLLUP Extension to GROUP BY》
Good luck.
secooler
12.04.21
-- The End --
1.初始化實驗壞境
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.
2.先看一下普通分組的效果：對group_id進行普通的group by操作---按照小組進行分組
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id;
GROUP_ID SUM(SALARY)
---------- -----------
30 12000
20 8000
40 16000
10 4000
3.對group_id進行普通的roolup操作---按照小組進行分組，同時求總計
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by rollup(group_id);
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
使用Group By語句翻譯一下上面的SQL語句如下（union all一個統計所有資料的行）：
SECOOLER@ora11g> select group_id,sum(salary) from group_test group by group_id
2 union all
3 select null, sum(salary) from group_test
4 order by 1;
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
4.再看一個rollup兩列的情況
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by rollup(group_id, job);
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
10 Coding 1000
10 Director 1000
10 Architect 1000
10 Programmer 1000
10 4000
20 Coding 2000
20 Director 2000
20 Architect 2000
20 Programmer 2000
20 8000
30 Coding 3000
30 Director 3000
30 Architect 3000
30 Programmer 3000
30 12000
40 Coding 4000
40 Director 4000
40 Architect 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
上面的SQL語句該如何使用Group By進行翻譯呢？
答案如下：
SECOOLER@ora11g> select group_id,job,sum(salary) from group_test group by group_id, job
2 union all
3 select group_id,null,sum(salary) from group_test group by group_id
4 union all
5 select null,null,sum(salary) from group_test
6 order by 1,2;
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
10 Architect 1000
10 Coding 1000
10 Director 1000
10 Programmer 1000
10 4000
20 Architect 2000
20 Coding 2000
20 Director 2000
20 Programmer 2000
20 8000
30 Architect 3000
30 Coding 3000
30 Director 3000
30 Programmer 3000
30 12000
40 Architect 4000
40 Coding 4000
40 Director 4000
40 Programmer 4000
40 16000
40000
21 rows selected.
5.補充一步，體驗一下GROUPING函式的效果
直接看效果就OK啦：
SECOOLER@ora11g> 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.
看出來什麼效果了麼？
有的同學還是沒有看出來，小小的解釋一下：
如果顯示“1”表示GROUPING函式對應的列（例如JOB欄位）是由於ROLLUP函式所產生的空值對應的資訊，即對此列進行彙總計算後的結果。
如果顯示“0”表示此行對應的這列參未與ROLLUP函式分組彙總活動。
如果還是沒有理解清楚，請參見Oracle官方文件中的描述內容：“Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.”
6.小結
ROLLUP在資料統計和報表生成過程中帶來極大的便利，而且效率比起來Group By + Union組合方法效率高得多。這也體現了Oracle在SQL統計分析上人性化、自動化、高效率的特點。
ROLLUP與GROUP BY的關係可以參考Oracle官方文件中的例子，連結如下：《ROLLUP Extension to GROUP BY》
Good luck.
secooler
12.04.21
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1595140/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- group by分組函式之rollup與cube用法函式
- rollup函式函式
- MySQL資料庫中的分組函式ROLLUPMySql資料庫函式
- 【CUBE】Oracle分組函式之CUBE魅力Oracle函式
- [SQL] rollup & cube 分組函式用法及一例SQL函式
- MySQL8.0-分組函式ROLLUP的基本用法（GROUPING）MySql函式
- Oracle的rollup、cube、grouping sets函式Oracle函式
- 分析函式rollup||cube學習函式
- rollup cube 超匯聚函式函式
- 分析函式 - ROLLUP和GROUPING SETS函式
- 10分鐘快速精通rollup.js——前置學習之rollup.js外掛篇JS
- Oracle OCP(12)：ROLLUP & CUBEOracle
- oracle10g後面的聚合運算函式：cube、rollup等Oracle函式
- 解析數倉OLAP函式：ROLLUP、CUBE、GROUPING SETS函式
- [Developer] RollupDeveloper
- ORACLE ROLLUP和CUBE的使用Oracle
- 5分鐘打通rollup.js副本JS
- 以太坊Rollup方案之 arbitrum（1）
- 以太坊Rollup方案之 arbitrum（2）
- vue、rollup、sass、requirejs組成的vueManagerVueUIJS
- HbuilderX，找不到rollup/rollup-win32-x64-msvcUIWin32
- 10分鐘快速入門rollup.jsJS
- 10分鐘快速進階rollup.jsJS
- 使用Rollup打包JavaScriptJavaScript
- 使用 rollup 打包 JSJS
- MySQL 中 WITH ROLLUP 用法MySql
- oracle rollup,cube子句的應用舉例；Oracle
- 資料倉儲開發報表常用函式—ROLLUP和GROUPING函式
- rollup入門介紹
- mysql與oracle的分組函式MySqlOracle函式
- 6、Oracle中的分組函式Oracle函式
- 【實驗】分析函式之魅力展示函式
- rollup 開發環境搭建開發環境
- 其他打包工具 Rollup && Parcel
- MySQL ROLLUP和CUBE問題MySql
- 關於Oracle自定義分組函式Oracle函式
- 10分鐘快速精通rollup.js——前置學習之基礎知識篇JS
- Sql Server Rollup 語句的用法SQLServer