成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

擴展GROUP BY之CUBE與GROUPING SETS

IT那活兒 / 1817人閱讀
擴展GROUP BY之CUBE與GROUPING SETS
點擊上方“IT那活兒”公眾號,關(guān)注后了解更多內(nèi)容,不管IT什么活兒,干就完了?。?!

擴展GROUP BY概述

Oracle擴展GROUP BY允許使用SQL語句對數(shù)據(jù)匯總結(jié)果進行多維展現(xiàn),從而生成復雜的報表,為決策者提供有效的數(shù)據(jù)支持。主要表現(xiàn)在

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

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


CUBE

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

2.1 CUBE分組

CUBE使用的語法和ROLLUP類似,只不過將ROLLUP換成CUBE而已,如下所示:
SELECT … GROUP BY CUBE(grouping_column_reference_list)
在前面使用ROLLUP(dname,job)來統(tǒng)計標準分組,對job的小計以及合計,下面用CUBE替換ROLLUP,看一下結(jié)果的區(qū)別:
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);
顯示結(jié)果如下:
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行。

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


CUBE對兩列操作,對應4個分組級別,最終對各種可能性分組進行統(tǒng)計,獲得多維度更加精細的數(shù)據(jù)統(tǒng)計結(jié)果。

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);
顯示結(jié)果如下:
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行。
結(jié)果少了合計以及GROUP BY job。部分CUBE很有用,有時候我們統(tǒng)計的列很多,沒有必要將所有的情況都統(tǒng)計,而是關(guān)注于某幾個維度統(tǒng)計,這樣部分CUBE的作用就發(fā)揮出來了。
2.3 CUBE總結(jié)

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


GROUPING SETS實現(xiàn)小計

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

3.1 GROUPING SETS分組

GROUPING SETS的語法很簡單,和ROLLUP、CUBE類似:
SELECT … GROUP BY GROUPING SETS(grouping_column_reference_list)
現(xiàn)在把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);
結(jié)果如下:
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行。

執(zhí)行計劃
----------------------------------------------------------------------------------------------
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")
從結(jié)果上很容易看出這個統(tǒng)計的規(guī)律,也就是分別按單列分組之后UNION ALL的結(jié)果。比如前5行就是對于每個JOB值,計算橫跨所有入職時間(年)和部門的小計。
注意GROUPING SETS的結(jié)果和列的順序沒有關(guān)系,而且結(jié)果的順序也是無序的,從計劃看出,上面的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);
顯示結(jié)果為:
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中,則語句的含義發(fā)生了變化,現(xiàn)在就是統(tǒng)計對于每個部門每個入職時間(年),對所有職位進行小計以及對于每個部門每個職位,對入職時間(年)進行小計。

3.3 CUBE、ROLLUP作為GROUPING SETS的參數(shù)

GROUPING SETS操作能夠接受ROLLUP和CUBE作為它的參數(shù), GROUPING SETS操作只對單列分組,而不提供合計的功能,如果需要GROUPING SETS提供合計的功能,那么可以使用ROLLUP或CUBE作為GROUPING SETS的參數(shù),比如改寫前面的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));
結(jié)果如下:
DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
CLERK 3050
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 3000
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
24925
24925
這條語句產(chǎn)生了2個合計行,因為ROLLUP或CUBE作為GROUPING SETS的參數(shù),則相當于對每個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所實現(xiàn)的功能了。上面的SQL具有重復合計,可以使用DISTINCT剔除重復行,另外在擴展分組中有特殊的函數(shù)可以使用,后面會說到GROUP_ID函數(shù)專門用來剔除重復分組(注意DISTINCT和GROUP_ID實現(xiàn)的功能還是不一樣的,見GROUP_ID部分講解)。當然,在GROUPING SETS中,ROLLUP和CUBE也可以混合使用,而且也能使用其他擴展功能,如部分分組、復合列分組、連接分組等。
ROLLUP和CUBE就不能接受GROUPING SETS作為參數(shù)了,ROLLUP和CUBE之間互相作為參數(shù)也是不可以的。

GROUPING SETS總結(jié)

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


本文作者:丁 俊(上海新炬中北團隊)

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

文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址:http://systransis.cn/yun/129100.html

相關(guān)文章

  • GROUP BY你都不會!ROLLUP,CUBE,GROUPPING詳解

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

    only_do 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

|高級講師

TA的文章

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