擴展GROUP BY之ROLLUP

IT那活兒發布于2023-01-11 13:19 / 1332人閱讀

擴展GROUP BY之ROLLUP

點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！

一

擴展GROUP BY概述

在本章的開頭已經簡單描述了擴展GROUP BY的應用場景，Oracle擴展GROUP BY允許使用SQL語句對數據匯總結果進行多維展現，從而生成復雜的報表，為決策者提供有效的數據支持。最重要的是，Oracle9i之后，擴展GROUP BY的功能已經趨于完善，能夠滿足大部分多維數據分析統計的工作。

主要表現在：

ROLLUP、CUBE、GROUPING SETS擴展GROUP BY子句提供了不同多維分組統計功能。
3個擴展分組函數：GROUPING、GROUPING_ID、GROUP_ID提供擴展GROUP BY的輔助功能：提供區別結果行屬于哪個分組級別，區分NULL值，建立有意義的報表，對匯總結果排序，過濾結果行等功能。
對擴展GROUP BY允許按重復列分組、組合列分組、部分分組、連接分組等復雜功能，另外GROUPING SETS可以接受CUBE、ROLLUP操作作為參數，這些功能使擴展GROUP BY更加強大。

通過SQL語句對上述功能的組合使用，就可以實現制作復雜的多維分析報表的功能。針對不同維度的報表統計，使用擴展GROUP BY的強大功能很容易實現，而且SQL編寫更簡單，性能也比同等的UNION ALL更好，在后面的內容中，我們會見識到強大的擴展GROUP BY功能。

還需要強調一點，擴展GROUP BY還是需要符合GROUP BY的相關語法語義規則的。比如SELECT中引用的列必須是分組列。

本章的表DEPT和EMP都來自于SCOTT用戶下，雖然表比較簡單，但是足以能說明擴展GROUP BY的功能。下面開始進入主要內容，探討強大的擴展GROUP BY功能。

二

ROLLUP

在數據倉庫系統中，一般多維分析報表，光有標準分組還不行，小計和合計往往是報表的核心內容，這種需求使用ROLLUP很常見，如果使用同等的UNION ALL實現，ROLLUP中的列越多，則需要寫的SQL語句就越復雜，所以與UNION ALL相比，ROLLUP寫法簡單，而且性能一般更好。本章主要探討普通ROLLUP和部分ROLLUP提供的強大功能。

2.1 UNION ALL實現ROLLUP功能

假設有這樣的需求：

1）統計每個部門每個職位的薪水和
2) 統計每個部門所有職位的薪水小計
3）統計所有部門所有職位的薪水合計
4）需要顯示部門名、職位名和累加后的薪水值

如果不知道Oracle中有擴展GROUP BY功能，那么很容易想起使用UNION ALL解決，的確在Oracle8i之前，沒有擴展GROUP BY功能，就要使用UNION ALL解決了，寫3條SQL分別完成3個分組功能，然后用UNION ALL連接：

--需求1實現
SELECT  a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname,b.job
UNION ALL

--需求2實現
SELECT  a.dname,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname
UNION ALL

--需求3實現
SELECT  NULL,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno;

顯示結果如下：

DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
SALES MANAGER 2850
SALES CLERK                     950
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING CLERK                    1300
SALES SALESMAN 5600
RESEARCH MANAGER 2975
RESEARCH ANALYST 3000
RESEARCH CLERK                     800
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
24925

已選擇13行。

執行計劃：

----------------------------------------------------------
Plan hash value: 3113041979

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    17 | 423 |    13  (70)| 00:00:01 |
| 1 |  UNION-ALL | |       | |            | |
| 2 |   HASH GROUP BY | |    12 | 336 |     5  (20)| 00:00:01 |
| 3 |    NESTED LOOPS | |    12 | 336 |     4   (0)| 00:00:01 |
| 4 |     TABLE ACCESS FULL | EMP |    12 | 180 |     3   (0)| 00:00:01 |
| 5 |     TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 6 |      INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
| 7 |   HASH GROUP BY | |     4 | 80 |     5  (20)| 00:00:01 |
| 8 |    NESTED LOOPS | |    12 | 240 |     4   (0)| 00:00:01 |
| 9 |     TABLE ACCESS FULL | EMP |    12 | 84 |     3   (0)| 00:00:01 |
| 10 |     TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
| 12 |   SORT AGGREGATE | |     1 | 7 |            | |
|* 13 |    TABLE ACCESS FULL | EMP |    12 | 84 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("A"."DEPTNO"="B"."DEPTNO")
11 - access("A"."DEPTNO"="B"."DEPTNO")
13 - filter("B"."DEPTNO" IS NOT NULL)

從執行計劃可以看出，為了實現這樣的需求，需要多次訪問EMP、DEPT表以及DEPT表的索引，這里的測試數據很少而且表結構簡單，實際應用中表結構可能很復雜，經常是多表關聯，數據量可能達到百萬級，千萬級，甚至上億，那么使用UNION ALL，明顯性能低下，如果使用WITH子句將常規分組的結果固定下來，然后在此基礎上再聚合，效率可能比單純UNION ALL好，但是還是不夠完美，現在的需求是對2列進行多維分析，如果是很多列呢?必然增加語句復雜度，類似地，CUBE,GROUPING SETS用UNION ALL改寫也有此類問題，何況擴展GROUP BY還提供了很多復雜功能，用UNION ALL改寫就更加麻煩了。

試想對于上面的需求，如果Oracle能提供一個這樣的分組功能就好了：

直接把分組的列按順序寫在一起，提供一個簡單的語法結構。
此語法結構先進行全分組：標準分組。
然后這個語法結構從右到左遞減列，做對應維度的分組，實現小計和合計。

如果有實現這3個要求的語法結構，那么上述需求就能很容易地實現，的確，Oracle提供了這樣的功能，那就是ROLLUP分組。

2.2 ROLLUP分組

Oracle使用ROLLUP對GROUP BY進行擴展：它允許計算標準分組以及相應維度的小計、合計功能。ROLLUP的語法結構很簡單：

SELECT … GROUP BY ROLLUP(grouping_column_reference_list)

ROLLUP后面指定的列以逗號分割，ROLLUP的計算結果和其后面指定列的順序有關，因為ROLLUP分組過程具有方向性：先計算標準分組，然后列從右到左遞減計算更高一級的小計，一直到列全部被選完，最后計算合計。這樣如果ROLLUP中指定n列，則整個計算過程中的分組方式有n+1種。下面就使用ROLLUP實現1.2.1的需求，如下：

SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY ROLLUP(a.dname,b.job);

顯示結果如下：

DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
SALES CLERK                     950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK                     800
RESEARCH ANALYST 3000
RESEARCH MANAGER 2975
RESEARCH 6775
ACCOUNTING CLERK                    1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
       24925

已選擇13行。

執行計劃：

----------------------------------------------------------
Plan hash value: 503922295

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    12 | 336 |     5  (20)| 00:00:01 |
| 1 |  SORT GROUP BY ROLLUP | |    12 | 336 |     5  (20)| 00:00:01 |
| 2 |   NESTED LOOPS | |    12 | 336 |     4   (0)| 00:00:01 |
| 3 |    TABLE ACCESS FULL | EMP |    12 | 180 |     3   (0)| 00:00:01 |
| 4 |    TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 5 |     INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("A"."DEPTNO"="B"."DEPTNO")

從上面的結果可以看出，使用ROLLUP只需要訪問EMP表一次，通過DEPT表的主鍵獲得ROWID回表查詢相關行，比前面的UNION ALL性能要好，而且從執行計劃上看出有一個特別的操作：SORT GROUP BY ROLLUP，這說明：這里的ROLLUP的計算結果是有順序的，所以展示的結果很直觀，但是一般還是要顯式排序的，因為默認排序很多時候不符合業務需求，另外，默認排序是受執行計劃影響的，可能有的ROLLUP就無序了，在重復列分組中就會說到沒有排序的情況，對于顯式排序，后面說到的GROUPING_ID函數會專門解決擴展分組排序問題。

ROLLUP分組具有方向性，從上面的結果看出，ROLLUP(a.dname,b.job)分組的過程是這樣的：

標準分組：GROUP BY(a.dname,b.job)，對每個部門每個職位進行分組(我分析的時候經常對GROUP BY加上括號，其實沒有必要，只是為了更直觀而已)。
從右到左遞減：GROUP BY(a.dname,NULL),其實這個NULL沒有必要使用，這里只是為了方便分析。這個過程是對上個級別分組的小計,也就是對每個dname值，計算橫跨所有job的小計。
最后合計：相當于GROUP BY (NULL,NULL)。

上面的ROLLUP只用了兩個列，如果有n個列，那么結果就是n+1種GROUP BY的組合，從右到左遞減的過程中，下一個分組就是對上一個分組的小計，最后合計，比如ROLLUP(a,b,c)，那么有：

分組級別	描述
a,b,c	標準分組
a,b	對于每個a,b列值，計算橫跨c列的小計
a	對于每個a列值，計算橫跨b,c列的小計
合計匯總	合計

另外提一下，其實ROLLUP操作，如果使用HINT: expand_gset_to_union，那么則優化器會將ROLLUP轉為對應的UNION ALL操作，其它的GROUPING SETS、CUBE也可以，有興趣的可以試一下。

ROLLUP語法簡單，而且具體處理過程也很簡單，除了第1個是標準分組，然后就是列從右到左遞減的分組，最后合計。下面實現需求：

計算每個入職時間(年)、部門、職位的標準分組的薪水和。
計算每個入職時間(年)、部門的所有職位的薪水小計。
計算每個入職時間(年)的所有部門所有職位的薪水小計。
最后合計薪水，顯示入職時間(年)、部門名、職位名。

從上面學到的ROLLUP知識，可以很容易地編寫SQL實現上述需求，將hire_date格式化到年，部門名dname,職位名job按順序放到ROLLUP中即可。

SELECT to_char(b.hiredate,yyyy) hire_year,a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(to_char(b.hiredate,yyyy),a.dname,b.job);

結果如下：

HIRE_YEA DNAME JOB SUM_SAL
-------- ---------------------------- ------------------ ----------
1980     RESEARCH CLERK                     800
1980     RESEARCH 800
1980                                                            800
1981     SALES CLERK                     950
1981     SALES MANAGER 2850
1981     SALES SALESMAN 5600
1981     SALES 9400
1981     RESEARCH ANALYST 3000
1981     RESEARCH MANAGER 2975
1981     RESEARCH 5975
1981     ACCOUNTING MANAGER 2450
1981     ACCOUNTING PRESIDENT 5000
1981     ACCOUNTING 7450
1981                                                          22825
1982     ACCOUNTING CLERK                    1300
1982     ACCOUNTING 1300
1982                                                           1300
                                         24925

從結果看出，編寫的SQL是實現了上述需求的。下面選些結果中的典型記錄對此結果進行分析，其它行分析類似：

相關行	描述
第7行	對第4、5、6行的所有JOB進行小計
第14行	對第7行、第13行的所有DNAME、JOB進行小計，當然也相當于對4-6、8-12行所有DNAME、JOB的小計。
最后一行	對所有入職日期(精確到年)、DNAME、JOB進行合計

因為ROLLUP分組過程具有方向性，所以通過改變ROLLUP中列的順序就可以達到改變報表結果和含義的目的。比如將前面的ROLLUP(dname,job)改為ROLLUP(job,dname)則含義就發生了變化，現在需要查詢的就是標準分組、計算每個job的所有部門的小計、最后合計，這里就兩個列，也就是小計的含義發生了變化，請看：

SELECT  b.job,a.dname, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY ROLLUP(b.job,a.dname);

顯示結果如下：

JOB DNAME SUM_SAL
------------------ ---------------------------- ----------
CLERK              SALES 950
CLERK              RESEARCH 800
CLERK              ACCOUNTING 1300
CLERK                                                 3050
ANALYST RESEARCH 3000
ANALYST 3000
MANAGER SALES 2850
MANAGER RESEARCH 2975
MANAGER ACCOUNTING 2450
MANAGER 8275
SALESMAN SALES 5600
SALESMAN 5600
PRESIDENT ACCOUNTING 5000
PRESIDENT 5000
24925

已選擇15行。

從結果看出，與ROLLUP(dname,job)的確發生了變化，小計是對每個職位的所有部門進行小計，一定要牢記ROLLUP列的順序對結果的影響，才能靈活地使用ROLLUP實現多維分析統計。

2.3 部分ROLLUP分組

2.2中的ROLLUP是普通的ROLLUP，有標準分組、多種小計、合計。如果現在的報表不需要某些小計、合計，那么怎么辦呢?有辦法，Oracle提供了部分ROLLUP分組功能，也就說將部分列從ROLLUP中移出來，放在GROUP BY中，這樣合計肯定沒有了，某些小計也沒有了。如ROLLUP(to_char(b.hiredate,yyyy),a.dname,b.job)，現在不需要每個入職時間(年)的所有部門所有職位的薪水小計，合計也不需要，那么改寫為：

SELECT to_char(b.hiredate,yyyy) hire_year,a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,yyyy),a.dname,ROLLUP(b.job);

將hiredate和dname從ROLLUP中移出來，就可以將每個入職時間(年)的所有部門所有職位的薪水小計以及合計剔除。最終只查詢標準分組和每個入職時間(年)、部門的所有職位的小計。部分ROLLUP提供了報表的額外選擇，當然，很多情況下合計還是需要的，能不能既剔除部分小計，又能保留合計呢?有很多方法，有時候可以很容易用組合列分組、連接分組等解決，有時候使用擴展分組函數剔除比較簡單，根據具體情況使用合適方法，部分CUBE類似。

ROLLUP總結

在數據倉庫系統中，經常需要對相關表進行多維匯總統計，如果分組有規律，比如先進行常規分組，在常規分組基礎上通過將列從右到左移動，然后進行更高一級的小計，最后合計，這樣一般就需要使用ROLLUP，ROLLUP結果和列的順序有關，順序不同結果則不同，這為報表實現提供了很大的靈活性。

ROLLUP中指定n列，則根據ROLLUP的分組過程可以知道，分組方式有n+1種。另外部分ROLLUP可以剔除某些不需要的小計和合計，只要根據需求將相關列從ROLLUP中移出，放到GROUP BY中即可。

本文作者：丁俊(上海新炬中北團隊）

本文來源：“IT那活兒”公眾號

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129114.html

GROUP BY你都不會！ROLLUP，CUBE，GROUPPING詳解

摘要：當向傳入一列時，會得到一個總計行。結果當向傳遞兩列時，將會按照這兩列進行分組，同時按照第一列的分組結果返回小計行。結果可以看出來結果是按照工廠和部門分別分組匯總的。選擇的就表示兩列都不為空。 Group By Group By 誰不會啊？這不是最簡單的嗎？越是簡單的東西，我們越會忽略掉他，因為我們不愿意再去深入了解它。1 小時 SQL 極速入門（一）1 小時 SQL 極速入門（二）1 ...

only_do 2019-06-25 19:07 評論0 收藏0

發表評論

登陸后可評論

0條評論

IT那活兒

男|高級講師

我要關注我要私信

TA的文章

消息中間件故障分析一例

閱讀 1346·2023-01-11 13:20
RAC雙節點crash回復一例

閱讀 1684·2023-01-11 13:20
ORA-600處理一例

閱讀 1132·2023-01-11 13:20
雙節點RAC實例2 HANG 故障分析一例

閱讀 1858·2023-01-11 13:20
RAC集群節點1重啟分析一例

閱讀 4100·2023-01-11 13:20
CRS啟動報錯CRS-1656處理分享

閱讀 2704·2023-01-11 13:20
oracle 12CR2打補丁報錯處理一例

閱讀 1385·2023-01-11 13:20
分布式緩存組件故障分析及監控優化

閱讀 3597·2023-01-11 13:20

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

擴展GROUP BY之ROLLUP

2.1 UNION ALL實現ROLLUP功能

2.3 部分ROLLUP分組

ROLLUP總結

本文作者：丁俊(上海新炬中北團隊）

本文來源：“IT那活兒”公眾號

相關文章

GROUP BY你都不會！ROLLUP，CUBE，GROUPPING詳解

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

消息中間件故障分析一例

RAC雙節點crash回復一例

ORA-600處理一例

雙節點RAC實例2 HANG 故障分析一例

RAC集群節點1重啟分析一例

CRS啟動報錯CRS-1656處理分享

oracle 12CR2打補丁報錯處理一例

分布式緩存組件故障分析及監控優化

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

擴展GROUP BY之ROLLUP

2.1 UNION ALL實現ROLLUP功能

2.3 部分ROLLUP分組

ROLLUP總結

本文作者：丁 俊(上海新炬中北團隊）

本文來源：“IT那活兒”公眾號

相關文章

發表評論

0條評論

男|高級講師

TA的文章

最新活動

本文作者：丁俊(上海新炬中北團隊）