group by分組函式之rollup與cube用法
ROLLUP，是GROUP BY子句的一種擴充套件，可以為每個分組返回小計記錄以及為所有分組返回總計記錄。
CUBE，也是GROUP BY子句的一種擴充套件，可以返回每一個列組合的小計記錄，同時在末尾加上總計記錄。
示例：
一、初始化表及資料程式碼如下：
點選(此處)摺疊或開啟
-
SQL> create table group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
-
-
表已建立。
-
-
SQL>
-
SQL> insert into group_test values (10,'Coding', 'Bruce',1000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (10,'Programmer','Clair',1000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (10,'Architect', 'Gideon',1000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (10,'Director', 'Hill',1000);
-
-
已建立 1 行。
-
-
SQL>
-
SQL> insert into group_test values (20,'Coding', 'Jason',2000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (20,'Programmer','Joey',2000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (20,'Architect', 'Martin',2000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (20,'Director', 'Michael',2000);
-
-
已建立 1 行。
-
-
SQL>
-
SQL> insert into group_test values (30,'Coding', 'Rebecca',3000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (30,'Programmer','Rex',3000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (30,'Architect', 'Richard',3000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (30,'Director', 'Sabrina',3000);
-
-
已建立 1 行。
-
-
SQL>
-
SQL> insert into group_test values (40,'Coding', 'Samuel',4000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (40,'Programmer','Susy',4000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (40,'Architect', 'Tina',4000);
-
-
已建立 1 行。
-
-
SQL> insert into group_test values (40,'Director', 'Wendy',4000);
-
-
已建立 1 行。
-
-
SQL>
-
SQL> commit;
-
-
提交完成。
-
-
SQL> 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
GROUP_ID JOB NAME SALARY
---------- ---------- ---------- ----------
30 Director Sabrina 3000
40 Coding Samuel 4000
40 Programmer Susy 4000
40 Architect Tina 4000
40 Director Wendy 4000
-
-
- 已選擇16行。
二、group by一般分組與rollup語句區別
先看一般的group by語句
點選(此處)摺疊或開啟
-
SQL> select group_id,sum(salary) from group_test
-
2 group by group_id;
-
-
GROUP_ID SUM(SALARY)
-
---------- -----------
-
30 12000
-
20 8000
-
40 16000
- 10 4000
點選(此處)摺疊或開啟
-
SQL> edit
-
已寫入 file afiedt.buf
-
-
1 select group_id,sum(salary) from group_test
-
2* group by rollup(group_id)
-
SQL> /
-
-
GROUP_ID SUM(SALARY)
-
---------- -----------
-
10 4000
-
20 8000
-
30 12000
-
40 16000
- 40000
SQL> edit
已寫入 file afiedt.buf
1 select group_id,sum(salary) from group_test
2 group by group_id
3 union all
4 select null,sum(salary) from group_test
5* order by 1
SQL> /
GROUP_ID SUM(SALARY)
---------- -----------
10 4000
20 8000
30 12000
40 16000
40000
下面檢視通過group_id,job二個欄位一般分組與rollup語句的區別
SQL> select group_id,job,sum(salary) from group_test
2 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
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
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行。
用union all方式對上面程式碼改寫如下：
SQL> select group_id,job,sum(salary) from group_test
2 group by group_id,job
3 union all
4 select group_id,null,sum(salary) from group_test
5 group by group_id
6 union all
7 select null,null,sum(salary) from group_test
8 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
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
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行。
SQL> select group_id,job,grouping(group_id),grouping(job),sum(salary)
2 from group_test
3 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
GROUP_ID JOB GROUPING(GROUP_ID) GROUPING(JOB) SUM(SALARY)
---------- ---------- ------------------ ------------- -----------
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行。
分析上面sql執行結果，可知grouping用法，當對某例進行了彙總合計時，grouping（此列）的值為1，否則為0。
三、group by一般分組與cube語句區別
SQL> edit
已寫入 file afiedt.buf
1 select group_id,job,sum(salary) from group_test
2 group by cube(group_id,job)
3* order by 1,2
SQL> /
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
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
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
Architect 10000
Coding 10000
GROUP_ID JOB SUM(SALARY)
---------- ---------- -----------
Director 10000
Programmer 10000
40000
已選擇25行。
你會發現，cube與rollup用法不同，cube會對所有例進行分組彙總合計。
通過以上例子，可知：
如果是Group by ROLLUP(A, B, C)的話，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作。 grouping_id()可以美化效果。除了使用GROUPING函式，還可以使用GROUPING_ID來標識GROUP BY的結果。
也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，…… 這樣任意按自己想要的形式結合統計資料，非常方便。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21251711/viewspace-1073184/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL8.0-分組函式ROLLUP的基本用法（GROUPING）MySql函式
- 解析數倉OLAP函式：ROLLUP、CUBE、GROUPING SETS函式
- 聚合函式及分組與過濾（GROUP BY … HAVING）函式
- MySQL資料庫中的分組函式ROLLUPMySql資料庫函式
- Django筆記十七之group by 分組用法總結Django筆記
- SQL函式Group_concat用法SQL函式
- Oracle OCP(12)：ROLLUP & CUBEOracle
- MySQL - 分組連線欄位函式GROUP_CONCAT的使用MySql函式
- 資料庫之DQL排序&分組&函式資料庫排序函式
- python程式設計之slice與indices函式用法Python程式設計函式
- MySQL 中 WITH ROLLUP 用法MySql
- group by分組查詢
- 正規表示式以及group的用法
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式
- 6、Oracle中的分組函式Oracle函式
- abs函式用法函式
- 粒子群最佳化函式--particleswarm函式的用法與討論函式Swarm
- python 系統函式呼叫sed分組Python函式
- sql中的left切割 與 group by後的兩次分組SQL
- SSD-函式用法函式
- GetModuleFileName函式的用法函式
- Python排序函式用法Python排序函式
- Instr函式的用法函式
- SQL LEN()函式用法SQL函式
- Python range() 函式用法Python函式
- 10分鐘快速精通rollup.js——前置學習之rollup.js外掛篇JS
- Android開發 - 掌握ConstraintLayout（九）分組(Group)AndroidAI
- 【複變函式與積分變換】02. 解析函式函式
- Rust中Turbofish之函式後面雙冒號(::)用法Rust函式
- PHP 自定義函式用法及常用函式集合PHP函式
- string 函式的基本用法函式
- C++ replace() 函式用法C++函式
- 【Oracle的NVL函式用法】Oracle函式
- fcntl函式用法詳解函式
- [譯] 柯里化與函式組合函式
- Mysql中常用函式 分組,連線查詢MySql函式
- Oracle 分組彙總統計函式的使用Oracle函式
- 用listagg函式分組實現列轉行函式
- 非同步操作系列之Generator函式與Async函式非同步函式