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

資訊專欄INFORMATION COLUMN

擴展GROUP BY之CUBE與GROUPING SETS

IT那活兒 / 1812人閱讀
擴展GROUP BY之CUBE與GROUPING SETS
點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！
一

擴展GROUP BY概述

Oracle擴展GROUP BY允許使用SQL語句對數據匯總結果進行多維展現，從而生成復雜的報表，為決策者提供有效的數據支持。主要表現在：

  • 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功能。

二

CUBE

還有一種需求就是對不同維度的所有可能分組進行統計，從而生成交叉報表。這種需求比ROLLUP更加精細，包含了ROLLUP的統計結果，而且還有其它的組合分組結果(小計)。交叉報表實現的分組級別更多，從而為決策分析提供更細粒度的統計數據。CUBE就可以實現這樣的需求，比如CUBE(n列)，那么分組種類有：
C0n  + C1n + C2n + … + Cnn = 2n
CUBE分組就是先進行合計(一個不取C0n)，然后小計(C1n.到C n-1n)，最后全取(標準分組Cnn)，和ROLLUP不同，CUBE計算結果和列的順序無關，但是列順序不同，默認的結果排序則不同，當然，我們應該使用顯式排序規則，默認排序可能會變化，顯式排序見GROUPING_ID函數講解。對CUBE操作，就不用UNION ALL對比了，有興趣的可以測試下，CUBE增加一列，則分組種類呈級數增長，使用UNION ALL改寫更麻煩，而且效率比較差。

2.1 CUBE分組

CUBE使用的語法和ROLLUP類似，只不過將ROLLUP換成CUBE而已，如下所示：
SELECT … GROUP BY CUBE(grouping_column_reference_list)
在前面使用ROLLUP(dname,job)來統計標準分組，對job的小計以及合計，下面用CUBE替換ROLLUP，看一下結果的區別：
SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY CUBE(a.dname,b.job);
顯示結果如下：
DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
24925
                           CLERK 3050
                            ANALYST 3000
                            MANAGER 8275
                            SALESMAN 5600
                            PRESIDENT 5000
SALES 9400
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH 6775
RESEARCH CLERK 800
RESEARCH ANALYST 3000
RESEARCH MANAGER 2975
ACCOUNTING 8750
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000

已選擇18行。

執行計劃
----------------------------------------------------------
Plan hash value: 2432972551

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

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

7 - access("A"."DEPTNO"="B"."DEPTNO")
對比ROLLUP(dname,job)，CUBE(dname,job)多了5行數據。這5行數據就是GROUP BY (NULL,job)。因為ROLLUP是按列的順序從右到左遞減分組統計的，而CUBE是各種可能性的分組，對于ROLLUP中有n列，CUBE中有同樣的n列，那么CUBE的分組種類比ROLLUP多2n -(n+1)種，這里的n=2,因此多1種分組結果，根據兩種分組的規則推算CUBE(dname,job)比ROLLUP(dname,job)多了GROUP BY (NULL,job)分組。
從上面的計劃看出，不像ROLLUP，有SORT GROUP BY ROLLUP，CUBE操作是先采用GENERATE CUBE，然后對結果SORT GROUP BY，所以上面的CUBE計算結果也是有默認排序的，同樣這種默認排序也是受計劃影響的，應該用顯式排序，在后面內容中會講解如何對擴展分組排序。
下面用表格分析一下CUBE(dname,job)對應分組級別：
分組級別
描述
dname,job
標準分組
dname
對于每個dname,計算橫跨所有job的小計
job
對于每個job,計算橫跨所有dname的小計
合計
合計


CUBE對兩列操作，對應4個分組級別，最終對各種可能性分組進行統計，獲得多維度更加精細的數據統計結果。

2.2 部分CUBE分組

和ROLLUP一樣，也有部分CUBE操作，可以去掉合計以及某些不需要的小計。比如上面的GROUP BY CUBE(dname,job)改為GROUP BY dname,CUBE(job)則剔除了合計以及GROUP BY job。代碼如下：
SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname,CUBE(b.job);
顯示結果如下：
DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
SALES 9400
SALES CLERK                     950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH 6775
RESEARCH CLERK                     800
RESEARCH ANALYST 3000
RESEARCH MANAGER 2975
ACCOUNTING 8750
ACCOUNTING CLERK                    1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000

已選擇12行。
結果少了合計以及GROUP BY job。部分CUBE很有用，有時候我們統計的列很多，沒有必要將所有的情況都統計，而是關注于某幾個維度統計，這樣部分CUBE的作用就發揮出來了。
2.3 CUBE總結

CUBE像ROLLUP一樣，可以實現多維數組分析統計工作，而且CUBE是對所有可能性的組合情況進行統計，從而生成交叉報表，CUBE分組級別更多，結果更精細，從而為決策者提供強大的數據支撐，為實現靈活的報表提供保障。

三

GROUPING SETS實現小計

前面已經說了兩種多維數據統計的方法：ROLLUP和CUBE，它們的輸出結果是由對應分組的行伴隨著小計行產生的，它們會產生標準分組、各種小計以及合計，但是有時候我們只關心對某個單列分組，從而得到其他維度小計信息，這樣就需要使用到GROUPING SETS擴展分組。
比如GROUP BY GROUPING SETS(a,b,c)相當于GROUP BY a、GROUP BY b和GROUP BY c這3個分組的UNION ALL結果，這樣結果中只有指定某些維度的小計，沒有常規分組結果以及合計結果，對只關注某些維度的小計分析很有用，從GROUPING SETS操作的功能看出，n列的GROUPING SETS的分組種類有n個。

3.1 GROUPING SETS分組

GROUPING SETS的語法很簡單，和ROLLUP、CUBE類似：
SELECT … GROUP BY GROUPING SETS(grouping_column_reference_list)
現在把5.2.2中的例子中的ROLLUP改為GROUPING SETS：
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 GROUPING SETS(to_char(b.hiredate,yyyy),a.dname,b.job);
結果如下：
HIRE_YEA DNAME JOB SUM_SAL
-------- ---------------------------- ------------------ ----------
                                     CLERK 3050
                                     SALESMAN 5600
                                     PRESIDENT 5000
                                     MANAGER 8275
                                     ANALYST 3000
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
1980                                                            800
1982                                                           1300
1981                                                          22825

已選擇11行。

執行計劃
----------------------------------------------------------------------------------------------
Plan hash value: 18386332

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    12 | 384 |    15  (20)| 00:00:01 |
| 1 |  TEMP TABLE TRANSFORMATION | |       | |            | |
| 2 |   LOAD AS SELECT | |       | |            | |
| 3 |    NESTED LOOPS | |    12 | 432 |     4   (0)| 00:00:01 |
| 4 |     TABLE ACCESS FULL | EMP |    12 | 276 |     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 |   LOAD AS SELECT | |       | |            | |
| 8 |    HASH GROUP BY | |     1 | 19 |     3  (34)| 00:00:01 |
| 9 |     TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_4AC9B4F |     1 | 19 |     2   (0)| 00:00:01 |
| 10 |   LOAD AS SELECT | |       | |            | |
| 11 |    HASH GROUP BY | |     1 | 22 |     3  (34)| 00:00:01 |
| 12 |     TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_4AC9B4F |     1 | 22 |     2   (0)| 00:00:01 |
| 13 |   LOAD AS SELECT | |       | |            | |
| 14 |    HASH GROUP BY | |     1 | 17 |     3  (34)| 00:00:01 |
| 15 |     TABLE ACCESS FULL | SYS_TEMP_0FD9D6612_4AC9B4F |     1 | 17 |     2   (0)| 00:00:01 |
| 16 |   VIEW | |     1 | 32 |     2   (0)| 00:00:01 |
| 17 |    TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_4AC9B4F |     1 | 32 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

4 - access("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")
從結果上很容易看出這個統計的規律，也就是分別按單列分組之后UNION ALL的結果。比如前5行就是對于每個JOB值，計算橫跨所有入職時間(年)和部門的小計。
注意：GROUPING SETS的結果和列的順序沒有關系，而且結果的順序也是無序的，從計劃看出，上面的GROUPING SETS的計劃還是很復雜的。

3.2  部分GROUPING SETS分組

每種擴展GROUP BY都有部分分組特性，GROUPING SETS也不例外，改寫上面的語句：
SELECT a.dname,to_char(b.hiredate,yyyy) hire_year,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname, GROUPING SETS(to_char(b.hiredate,yyyy),b.job);
顯示結果為：
DNAME HIRE_YEA 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
RESEARCH 1981                              5975
SALES 1981                              9400
ACCOUNTING 1981                              7450
ACCOUNTING 1982                              1300
RESEARCH 1980                               800

已選擇14行。
將部門名從GROUPING SETS中移到GROUP BY中，則語句的含義發生了變化，現在就是統計對于每個部門每個入職時間(年)，對所有職位進行小計以及對于每個部門每個職位，對入職時間(年)進行小計。

3.3 CUBE、ROLLUP作為GROUPING SETS的參數

GROUPING SETS操作能夠接受ROLLUP和CUBE作為它的參數， GROUPING SETS操作只對單列分組，而不提供合計的功能，如果需要GROUPING SETS提供合計的功能，那么可以使用ROLLUP或CUBE作為GROUPING SETS的參數，比如改寫前面的GROUPING SETS(a.dname,b.job)，提供合計功能：
SELECT a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job));
結果如下：
DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
CLERK 3050
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 3000
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
24925
24925
這條語句產生了2個合計行，因為ROLLUP或CUBE作為GROUPING SETS的參數，則相當于對每個ROLLUP或CUBE操作的UNION ALL。所以上面的語句等價于：
SELECT a.dname,NULL job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname)
UNION ALL
SELECT NULL dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY  ROLLUP(b.job);
這樣，就很好理解ROLLUP或CUBE作為GROUPING SETS所實現的功能了。上面的SQL具有重復合計，可以使用DISTINCT剔除重復行,另外在擴展分組中有特殊的函數可以使用，后面會說到GROUP_ID函數專門用來剔除重復分組（注意DISTINCT和GROUP_ID實現的功能還是不一樣的，見GROUP_ID部分講解）。當然，在GROUPING SETS中，ROLLUP和CUBE也可以混合使用，而且也能使用其他擴展功能，如部分分組、復合列分組、連接分組等。
ROLLUP和CUBE就不能接受GROUPING SETS作為參數了，ROLLUP和CUBE之間互相作為參數也是不可以的。

GROUPING SETS總結

GROUPING SETS很簡單，就是分別對單列進行分組，從而統計其他維度的小計，對于GROUPING SETS中無合計，Oracle允許ROLLUP,CUBE作為GROUPING SETS的參數，增強了GROUPING SETS分組功能。
現在3個擴展GROUP BY：ROLLUP、CUBE、GROUPING SETS已經基本講完。實際上里面還有很多值得研究的內容，比如3個擴展GROUP BY之間是否可以轉換、如何對結果中的NULL進行判斷是否是小計或合計列、如何制作可讀性強的報表、如何對結果顯式排序、如何實現更加復雜的需求：比如部分ROLLUP中需要保留合計、如何簡單地剔除某些不必要的行(強大的GROUPING_ID函數使用)等等，在后續的內容中，會逐一介紹。


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

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

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

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

相關文章

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

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

    only_do 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<