【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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL資料庫中的分組函式ROLLUPMySql資料庫函式
- MySQL8.0-分組函式ROLLUP的基本用法（GROUPING）MySql函式
- Oracle OCP(12)：ROLLUP & CUBEOracle
- 解析數倉OLAP函式：ROLLUP、CUBE、GROUPING SETS函式
- 10分鐘快速精通rollup.js——前置學習之rollup.js外掛篇JS
- 以太坊Rollup方案之 arbitrum（1）
- 以太坊Rollup方案之 arbitrum（2）
- 5分鐘打通rollup.js副本JS
- HbuilderX，找不到rollup/rollup-win32-x64-msvcUIWin32
- 10分鐘快速進階rollup.jsJS
- 10分鐘快速入門rollup.jsJS
- 使用 rollup 打包 JSJS
- MySQL 中 WITH ROLLUP 用法MySql
- 使用Rollup打包JavaScriptJavaScript
- rollup配置及使用
- 6、Oracle中的分組函式Oracle函式
- 關於Rollup那些事
- rollup入門介紹
- rollup 開發環境搭建開發環境
- 其他打包工具 Rollup && Parcel
- 10分鐘快速精通rollup.js——前置學習之基礎知識篇JS
- Rollup的本質是什麼？
- Oracle 分組彙總統計函式的使用Oracle函式
- 元件庫rollup打包體積優化元件優化
- 資料庫之DQL排序&分組&函式資料庫排序函式
- 使用rollup打包庫的一種基本配置
- 打包工具 rollup.js 入門教程JS
- vite的專案，使用 rollup 打包的方法Vite
- Rollup作者新作: Svelte Cubed, 擁抱 Three.js !JS
- 記錄從vuecli打包庫遷移到rollup打包Vue
- 實用程式包utils - 基於Rollup打包輸出各模組檔案（二）
- JavaScript中高階函式的魅力JavaScript函式
- 10分鐘快速精通rollup.js——Vue.js原始碼打包原理深度分析Vue.js原始碼
- Rollup處理並打包JS檔案專案例項JS
- Oracle分析函式之開窗函式over()詳解Oracle函式
- 第二章 函式的魅力函式
- Elasticsearch 8.X Rollup 功能詳解及避坑指南Elasticsearch
- 為什麼說rollup比webpack更適合打包庫Web
- python 系統函式呼叫sed分組Python函式