系列文章目錄
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（一）:關(guān)于SQL不得不說的那些事
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二）：簡單單表查詢
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（三）：SQL排序的那些事
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（四）：從執(zhí)行計(jì)劃討論UNION ALL與空字符串&UNION與OR的使用注意事項(xiàng)
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（五）：從執(zhí)行計(jì)劃看IN、EXISTS 和 INNER JOIN效率，我們要分場景不要死記網(wǎng)上結(jié)論
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（六）：從執(zhí)行計(jì)劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關(guān)聯(lián)條件不要亂放
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（七）：從有重復(fù)數(shù)據(jù)前提下如何比較出兩個(gè)表中的差異數(shù)據(jù)及對應(yīng)條數(shù)聊起
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時(shí)比約束更靈活的限制數(shù)據(jù)插入以及怎么一個(gè)insert語句同時(shí)插入多張表
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（九）：一個(gè)update誤把其他列數(shù)據(jù)更新成空了？Merge改寫update！給你五種刪除重復(fù)數(shù)據(jù)的寫法！
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十）：從拆分字符串、替換字符串以及統(tǒng)計(jì)字符串出現(xiàn)次數(shù)說起
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十一）：拿幾個(gè)案例講講translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函數(shù)
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十二）：三問(如何對字符串字母去重后按字母順序排列字符串？如何識別哪些字符串中包含數(shù)字？如何將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表?)
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十三）：討論一下常用聚集函數(shù)&通過執(zhí)行計(jì)劃看sum()over()對員工工資進(jìn)行累加
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十四）：計(jì)算消費(fèi)后的余額&計(jì)算銀行流水累計(jì)和&計(jì)算各部門工資排名前三位的員工
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十五）：查找最值所在行數(shù)據(jù)信息及快速計(jì)算總和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十六）：數(shù)據(jù)倉庫中時(shí)間類型操作（初級）日、月、年、時(shí)、分、秒之差及時(shí)間間隔計(jì)算
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十七）：數(shù)據(jù)倉庫中時(shí)間類型操作（初級）確定兩個(gè)日期之間的工作天數(shù)、計(jì)算—年中周內(nèi)各日期出現(xiàn)次數(shù)、確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十八）：數(shù)據(jù)倉庫中時(shí)間類型操作（進(jìn)階）INTERVAL、EXTRACT以及如何確定一年是否為閏年及周的計(jì)算
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（十九）：數(shù)據(jù)倉庫中時(shí)間類型操作（進(jìn)階）如何一個(gè)SQL打印當(dāng)月或一年的日歷？如何確定某月內(nèi)第一個(gè)和最后—個(gè)周內(nèi)某天的日期？
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十）：數(shù)據(jù)倉庫中時(shí)間類型操作（進(jìn)階）獲取季度開始結(jié)束時(shí)間以及如何統(tǒng)計(jì)非連續(xù)性時(shí)間的數(shù)據(jù)
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十一）：數(shù)據(jù)倉庫中時(shí)間類型操作（進(jìn)階）識別重疊的日期范圍，按指定10分鐘時(shí)間間隔匯總數(shù)據(jù)
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十二）：數(shù)倉報(bào)表場景? 從分析函數(shù)效率一定快嗎聊一聊結(jié)果集分頁和隔行抽樣實(shí)現(xiàn)方式
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十三）：數(shù)倉報(bào)表場景? 如何對數(shù)據(jù)排列組合去重以及通過如何找到包含最大值和最小值的記錄這個(gè)問題再次用執(zhí)行計(jì)劃給你證明分析函數(shù)性能不一定高
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十四）：數(shù)倉報(bào)表場景?通過案例執(zhí)行計(jì)劃詳解”行轉(zhuǎn)列”,”列轉(zhuǎn)行”是如何實(shí)現(xiàn)的
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十五）：數(shù)倉報(bào)表場景?結(jié)果集中的重復(fù)數(shù)據(jù)只顯示一次以及計(jì)算部門薪資差異高效的寫法以及如何對數(shù)據(jù)進(jìn)行快速分組
【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十六）：數(shù)倉報(bào)表場景?聊聊ROLLUP、UNION ALL是如何分別做分組合計(jì)的以及如何識別哪些行是做匯總的結(jié)果行
前言
本篇文章講解的主要內(nèi)容是：ROLLUP、UNION ALL是如何分別做分組合計(jì)的以及如何通過CUBE 、GROUPING、GROUPING_ID 識別哪些行是做匯總的結(jié)果行
【SQL開發(fā)實(shí)戰(zhàn)技巧】這一系列博主當(dāng)作復(fù)習(xí)舊知識來進(jìn)行寫作，畢竟SQL開發(fā)在數(shù)據(jù)分析場景非常重要且基礎(chǔ)，面試也會經(jīng)常問SQL開發(fā)和調(diào)優(yōu)經(jīng)驗(yàn)，相信當(dāng)我寫完這一系列文章，也能再有所收獲，未來面對SQL面試也能游刃有余~。
一、ROLLUP代替UNION ALL做小計(jì)
生成報(bào)表數(shù)據(jù)時(shí)通常還要加一個(gè)總合計(jì),比如我現(xiàn)在有個(gè)需求：想要統(tǒng)計(jì)每個(gè)部門各個(gè)員工的工資以及每個(gè)部門每個(gè)工作崗位的工資總計(jì)以及每個(gè)部門的工資總計(jì)數(shù)以及全公司的工資總計(jì)。
如果是你你會怎么做？
是不是考慮用union all了？
比如下面實(shí)現(xiàn)方案：
select a.deptno,a.ename,a.job,a.sal--每個(gè)部門各個(gè)員工的工資
from emp a where deptno is not null
union all
select a.deptno,null ename,a.job,sum(a.sal)--每個(gè)部門每個(gè)工作崗位的工資總計(jì)
from emp a where deptno is not null
group by a.deptno,a.job
union all
select a.deptno,null ename,null job,sum(a.sal)--每個(gè)部門的工資總計(jì)
from emp a where deptno is not null
group by a.deptno
union all
select null deptno,null ename,null job,sum(a.sal)--全公司的工資總計(jì)
from emp a where deptno is not null;
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
20 SMITH CLERK 800
30 ALLEN SALESMAN 1600
30 WARD SALESMAN 1250
20 JONES MANAGER 2975
30 MARTIN SALESMAN 1250
30 BLAKE MANAGER 2850
10 CLARK MANAGER 2450
20 SCOTT ANALYST 3000
10 KING PRESIDENT 5000
30 TURNER SALESMAN 1500
20 ADAMS CLERK 1100
30 JAMES CLERK 950
20 FORD ANALYST 3000
10 MILLER CLERK 1300
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
10 8750
20 10875
30 9400
29025
27 rows selected
那么問題來了，上面的寫法你不感覺挺麻煩的嗎，起碼你要寫的代碼量挺多，思考一個(gè)問題：必須要用union all才能做嗎？
答案是否定的,我們用ROLLUP就可以達(dá)到這個(gè)目的。
SQL> set pagesize 200;
SQL>
SQL> select deptno,ename,job,sum(sal)as sal
2 from emp
3 group by rollup(deptno,job,ename)
4 order by deptno,job,ename;
DEPTNO ENAME JOB SAL
------ ---------- --------- ----------
10 MILLER CLERK 1300
10 CLERK 1300
10 CLARK MANAGER 2450
10 MANAGER 2450
10 KING PRESIDENT 5000
10 PRESIDENT 5000
10 8750
20 FORD ANALYST 3000
20 SCOTT ANALYST 3000
20 ANALYST 6000
20 ADAMS CLERK 1100
20 SMITH CLERK 800
20 CLERK 1900
20 JONES MANAGER 2975
20 MANAGER 2975
20 10875
30 JAMES CLERK 950
30 CLERK 950
30 BLAKE MANAGER 2850
30 MANAGER 2850
30 ALLEN SALESMAN 1600
30 MARTIN SALESMAN 1250
30 TURNER SALESMAN 1500
30 WARD SALESMAN 1250
30 SALESMAN 5600
30 9400
test
29025
30 rows selected
上述語句中,ROLLUP是GROUP BY子句的一種擴(kuò)展,可以為每個(gè)分組返回小計(jì)記錄,以及為所有的分組返回總計(jì)記錄。
可能這種方式有很多人已用過,如果按部門編號和工作兩列匯總,加上總合計(jì)有沒有辦法處理呢？
我們可以把部門與工作這兩列放入括號中,這樣部門與工作會被當(dāng)作一個(gè)整體：
SQL> SELECT deptno AS 部門編碼,job 工作,
2 SUM(sal) AS 工資小計(jì)
3 FROM emp
4 group by rollup((deptno,job));
部門編碼 工作 工資小計(jì)
---- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
29025
11 rows selected
二、既然有了自動合計(jì)，那怎么判斷出來哪些行是做的自動合計(jì)？
前面介紹了用ROLLUP來生成級次匯總,那么如何判斷哪些行是做了小計(jì)的呢？
有些人會說可以用NVL,如NVL(DEPTNO,總計(jì)’)、NVL(JOB,‘小計(jì)’)下面來看是否可行。
首先來生成一下測試數(shù)據(jù)：
create table empp as select * from emp where deptno is not null;
update empp set job =null where empno=7788;
update empp set deptno =null where empno in(7654,7902);
commit;
SQL> select * from empp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
好了測試數(shù)據(jù)生成好了，可以看到(7788,7654,7902)這幾個(gè)員工的job,deptno分別被設(shè)置成了空值。
我們接下來驗(yàn)證一下用NVL來判斷小計(jì)是否合理哈！
SQL> select nvl(to_Char(aa.deptno),'總計(jì)') as 部門編碼,
2 nvl(job,'小計(jì)') as 工作,
3 deptno,
4 job,
5 mgr as 主管,
6 max(case when empno in(7788,7654,7902) then empno end) as max_empno,
7 sum(sal) as sal,
8 grouping(deptno) deptno_grouping,
9 grouping(job)job_grouping,
10 grouping(mgr)mgr_grouping
11 from empp aa
12 group by rollup (deptno,job,mgr);
部門編碼 工作 DEPTNO JOB 主管 MAX_EMPNO SAL DEPTNO_GROUPING JOB_GROUPING MGR_GROUPING
---------------------------------------- -------------------------------- ------ --------- ----- ---------- ---------- --------------- ------------ ------------
總計(jì) ANALYST ANALYST 7566 7902 3000 0 0 0
總計(jì) ANALYST ANALYST 7902 3000 0 0 1
總計(jì) SALESMAN SALESMAN 7698 7654 1250 0 0 0
總計(jì) SALESMAN SALESMAN 7654 1250 0 0 1
總計(jì) 小計(jì) 7902 4250 0 1 1
10 CLERK 10 CLERK 7782 1300 0 0 0
10 CLERK 10 CLERK 1300 0 0 1
10 MANAGER 10 MANAGER 7839 2450 0 0 0
10 MANAGER 10 MANAGER 2450 0 0 1
10 PRESIDENT 10 PRESIDENT 5000 0 0 0
10 PRESIDENT 10 PRESIDENT 5000 0 0 1
10 小計(jì) 10 8750 0 1 1
20 小計(jì) 20 7566 7788 3000 0 0 0
20 小計(jì) 20 7788 3000 0 0 1
20 CLERK 20 CLERK 7788 1100 0 0 0
20 CLERK 20 CLERK 7902 800 0 0 0
20 CLERK 20 CLERK 1900 0 0 1
20 MANAGER 20 MANAGER 7839 2975 0 0 0
20 MANAGER 20 MANAGER 2975 0 0 1
20 小計(jì) 20 7788 7875 0 1 1
30 CLERK 30 CLERK 7698 950 0 0 0
30 CLERK 30 CLERK 950 0 0 1
30 MANAGER 30 MANAGER 7839 2850 0 0 0
30 MANAGER 30 MANAGER 2850 0 0 1
30 SALESMAN 30 SALESMAN 7698 4350 0 0 0
30 SALESMAN 30 SALESMAN 4350 0 0 1
30 小計(jì) 30 8150 0 1 1
總計(jì) 小計(jì) 7902 29025 1 1 1
28 rows selected
看到上面結(jié)果，那么當(dāng)有空值(empno為 7788,7654,7902)時(shí),對應(yīng)的detpno或job本身就是空值,所以小計(jì)結(jié)果是錯誤的。
這時(shí)我們就要用GROUPJNG函數(shù),該函數(shù)的參數(shù)只能是列名,而且只能是group by后顯示的列名。
當(dāng)該列被匯總時(shí),GROUPING的返回值為1,如DEPTNO_GROUPING最后一行。當(dāng)該列沒有被匯總而是顯示明細(xì)時(shí),GROUPING的返回值為0,
就像DEPTNO_GROUPTNG前的所有行。
于是查詢語句可以更改如下：
SQL> select case grouping(deptno)
2 when 1 then
3 '總計(jì)'
4 else
5 to_Char(aa.deptno)
6 end as 部門編碼,
7 case
8 when grouping(deptno) = 0 and grouping(job) = 1 then
9 '根據(jù)部門匯總'
10 else
11 job
12 end as 工作,
13 case
14 when grouping(mgr) = 1 then
15 '根據(jù)工作匯總'
16 else
17 to_char(mgr)
18 end as 主管,
19 max(case
20 when empno in (7788, 7654, 7902) then
21 empno
22 end) as max_empno,
23 sum(sal) as sal,
24 grouping(deptno) deptno_grouping,
25 grouping(job) job_grouping,
26 grouping(mgr) mgr_grouping
27 from emp aa
28 where mgr is not null
29 group by rollup(deptno, job, mgr)
30 order by deptno, job, mgr;
部門編碼 工作 主管 MAX_EMPNO SAL DEPTNO_GROUPING JOB_GROUPING MGR_GROUPING
---------------------------------------- -------------------------------- ---------------------------------------- ---------- ---------- --------------- ------------ ------------
10 CLERK 7782 1300 0 0 0
10 CLERK 根據(jù)工作匯總 1300 0 0 1
10 MANAGER 7839 2450 0 0 0
10 MANAGER 根據(jù)工作匯總 2450 0 0 1
10 根據(jù)部門匯總 根據(jù)工作匯總 3750 0 1 1
20 ANALYST 7566 7902 6000 0 0 0
20 ANALYST 根據(jù)工作匯總 7902 6000 0 0 1
20 CLERK 7788 1100 0 0 0
20 CLERK 7902 800 0 0 0
20 CLERK 根據(jù)工作匯總 1900 0 0 1
20 MANAGER 7839 2975 0 0 0
20 MANAGER 根據(jù)工作匯總 2975 0 0 1
20 根據(jù)部門匯總 根據(jù)工作匯總 7902 10875 0 1 1
30 CLERK 7698 950 0 0 0
30 CLERK 根據(jù)工作匯總 950 0 0 1
30 MANAGER 7839 2850 0 0 0
30 MANAGER 根據(jù)工作匯總 2850 0 0 1
30 SALESMAN 7698 7654 5600 0 0 0
30 SALESMAN 根據(jù)工作匯總 7654 5600 0 0 1
30 根據(jù)部門匯總 根據(jù)工作匯總 7654 9400 0 1 1
總計(jì) 根據(jù)工作匯總 7902 24025 1 1 1
21 rows selected
SQL>
三、計(jì)算所有表達(dá)式組合的小計(jì)
現(xiàn)在有個(gè)需求：按DEPTNO,JOB的各種組合匯總,并返回總的合計(jì)。
可能很多人都用過,那就是CUBE語句。
CUBE也是GROUPBY子句的一種擴(kuò)展,可以返回每一個(gè)列組合的小計(jì)記錄,同時(shí)在末尾加上總計(jì)記錄。
下面介紹一下grouping_id函數(shù),見下列語句中的注釋及與GROUPING的對比。文章來源：http://www.zghlxwxcb.cn/news/detail-411231.html
SQL> select case grouping(deptno) || grouping(job)
2 when '00' then
3 '按照部門與工作分組'
4 when '10' then
5 '按照工作分組，部門聚合'
6 when '01' then
7 '按照部門分組，工作聚合'
8 when '11' then
9 '做了匯總'
10 end as grouping,
11 /*把GROUPING(deptno)IIGROUPING(job)的結(jié)果當(dāng)作二進(jìn)制,再轉(zhuǎn)為十進(jìn)制就是grouping_id(deptno,job)的值*/
12 case grouping_id(deptno, job)
13 when 0 then
14 '按照部門與工作分組'
15 when 2 then
16 '按照工作分組，部門聚合'
17 when 1 then
18 '按照部門分組，工作聚合'
19 when 3 then
20 '做了匯總'
21 end as grouping_id,
22 deptno,
23 job,
24 sum(sal) as smsal
25 from emp a
26 group by cube(deptno, job)
27 order by grouping(job), grouping(deptno);
GROUPING GROUPING_ID DEPTNO JOB SMSAL
---------------------- ---------------------- ------ --------- ----------
按照部門與工作分組 按照部門與工作分組 10 MANAGER 2450
按照部門與工作分組 按照部門與工作分組 30 MANAGER 2850
按照部門與工作分組 按照部門與工作分組 30 CLERK 950
按照部門與工作分組 按照部門與工作分組 20 MANAGER 2975
按照部門與工作分組 按照部門與工作分組 20 ANALYST 6000
按照部門與工作分組 按照部門與工作分組 20 CLERK 1900
按照部門與工作分組 按照部門與工作分組 10 PRESIDENT 5000
按照部門與工作分組 按照部門與工作分組 30 SALESMAN 5600
按照部門與工作分組 按照部門與工作分組 10 CLERK 1300
按照工作分組，部門聚合 按照工作分組，部門聚合 SALESMAN 5600
按照工作分組，部門聚合 按照工作分組，部門聚合 CLERK 4150
按照工作分組，部門聚合 按照工作分組，部門聚合 ANALYST 6000
按照工作分組，部門聚合 按照工作分組，部門聚合 MANAGER 8275
按照工作分組，部門聚合 按照工作分組，部門聚合 PRESIDENT 5000
按照部門分組，工作聚合 按照部門分組，工作聚合 10 8750
按照部門分組，工作聚合 按照部門分組，工作聚合 30 9400
按照部門分組，工作聚合 按照部門分組，工作聚合 20 10875
做了匯總 做了匯總 29025
18 rows selected
總結(jié)
上面內(nèi)容是對rollup做的詳細(xì)測試~文章來源地址http://www.zghlxwxcb.cn/news/detail-411231.html
到了這里，關(guān)于【SQL開發(fā)實(shí)戰(zhàn)技巧】系列（二十六）：數(shù)倉報(bào)表場景?聊聊ROLLUP、UNION ALL是如何分別做分組合計(jì)的以及如何識別哪些行是做匯總的結(jié)果行的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
