如何理解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統計分析上人性化、自動化、高效率的特點。
關于如何理解Oracle分組函數的ROLLUP問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
