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

資訊專欄INFORMATION COLUMN

擴(kuò)展GROUP BY之CUBE與GROUPING SETS

IT那活兒 / 1808人閱讀
擴(kuò)展GROUP BY之CUBE與GROUPING SETS
點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！
一

擴(kuò)展GROUP BY概述

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

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

通過(guò)SQL語(yǔ)句對(duì)上述功能的組合使用，就可以實(shí)現(xiàn)制作復(fù)雜的多維分析報(bào)表的功能。針對(duì)不同維度的報(bào)表統(tǒng)計(jì)，使用擴(kuò)展GROUP BY的強(qiáng)大功能很容易實(shí)現(xiàn)，而且SQL編寫(xiě)更簡(jiǎn)單，性能也比同等的UNION ALL更好，在后面的內(nèi)容中，我們會(huì)見(jiàn)識(shí)到強(qiáng)大的擴(kuò)展GROUP BY功能。

二

CUBE

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

2.1 CUBE分組

CUBE使用的語(yǔ)法和ROLLUP類似，只不過(guò)將ROLLUP換成CUBE而已，如下所示：
SELECT … GROUP BY CUBE(grouping_column_reference_list)
在前面使用ROLLUP(dname,job)來(lái)統(tǒng)計(jì)標(biāo)準(zhǔn)分組，對(duì)job的小計(jì)以及合計(jì)，下面用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í)行計(jì)劃
----------------------------------------------------------
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")
對(duì)比ROLLUP(dname,job)，CUBE(dname,job)多了5行數(shù)據(jù)。這5行數(shù)據(jù)就是GROUP BY (NULL,job)。因?yàn)镽OLLUP是按列的順序從右到左遞減分組統(tǒng)計(jì)的，而CUBE是各種可能性的分組，對(duì)于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)分組。
從上面的計(jì)劃看出，不像ROLLUP，有SORT GROUP BY ROLLUP，CUBE操作是先采用GENERATE CUBE，然后對(duì)結(jié)果SORT GROUP BY，所以上面的CUBE計(jì)算結(jié)果也是有默認(rèn)排序的，同樣這種默認(rèn)排序也是受計(jì)劃影響的，應(yīng)該用顯式排序，在后面內(nèi)容中會(huì)講解如何對(duì)擴(kuò)展分組排序。
下面用表格分析一下CUBE(dname,job)對(duì)應(yīng)分組級(jí)別：
分組級(jí)別
描述
dname,job
標(biāo)準(zhǔn)分組
dname
對(duì)于每個(gè)dname,計(jì)算橫跨所有job的小計(jì)
job
對(duì)于每個(gè)job,計(jì)算橫跨所有dname的小計(jì)
合計(jì)
合計(jì)


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

2.2 部分CUBE分組

和ROLLUP一樣，也有部分CUBE操作，可以去掉合計(jì)以及某些不需要的小計(jì)。比如上面的GROUP BY CUBE(dname,job)改為GROUP BY dname,CUBE(job)則剔除了合計(jì)以及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é)果少了合計(jì)以及GROUP BY job。部分CUBE很有用，有時(shí)候我們統(tǒng)計(jì)的列很多，沒(méi)有必要將所有的情況都統(tǒng)計(jì)，而是關(guān)注于某幾個(gè)維度統(tǒng)計(jì)，這樣部分CUBE的作用就發(fā)揮出來(lái)了。
2.3 CUBE總結(jié)

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

三

GROUPING SETS實(shí)現(xiàn)小計(jì)

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

3.1 GROUPING SETS分組

GROUPING SETS的語(yǔ)法很簡(jiǎn)單，和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í)行計(jì)劃
----------------------------------------------------------------------------------------------
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é)果上很容易看出這個(gè)統(tǒng)計(jì)的規(guī)律，也就是分別按單列分組之后UNION ALL的結(jié)果。比如前5行就是對(duì)于每個(gè)JOB值，計(jì)算橫跨所有入職時(shí)間(年)和部門的小計(jì)。
注意：GROUPING SETS的結(jié)果和列的順序沒(méi)有關(guān)系，而且結(jié)果的順序也是無(wú)序的，從計(jì)劃看出，上面的GROUPING SETS的計(jì)劃還是很復(fù)雜的。

3.2  部分GROUPING SETS分組

每種擴(kuò)展GROUP BY都有部分分組特性，GROUPING SETS也不例外，改寫(xiě)上面的語(yǔ)句：
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中，則語(yǔ)句的含義發(fā)生了變化，現(xiàn)在就是統(tǒng)計(jì)對(duì)于每個(gè)部門每個(gè)入職時(shí)間(年)，對(duì)所有職位進(jìn)行小計(jì)以及對(duì)于每個(gè)部門每個(gè)職位，對(duì)入職時(shí)間(年)進(jìn)行小計(jì)。

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

GROUPING SETS操作能夠接受ROLLUP和CUBE作為它的參數(shù)， GROUPING SETS操作只對(duì)單列分組，而不提供合計(jì)的功能，如果需要GROUPING SETS提供合計(jì)的功能，那么可以使用ROLLUP或CUBE作為GROUPING SETS的參數(shù)，比如改寫(xiě)前面的GROUPING SETS(a.dname,b.job)，提供合計(jì)功能：
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
這條語(yǔ)句產(chǎn)生了2個(gè)合計(jì)行，因?yàn)镽OLLUP或CUBE作為GROUPING SETS的參數(shù)，則相當(dāng)于對(duì)每個(gè)ROLLUP或CUBE操作的UNION ALL。所以上面的語(yǔ)句等價(jià)于：
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所實(shí)現(xiàn)的功能了。上面的SQL具有重復(fù)合計(jì)，可以使用DISTINCT剔除重復(fù)行,另外在擴(kuò)展分組中有特殊的函數(shù)可以使用，后面會(huì)說(shuō)到GROUP_ID函數(shù)專門用來(lái)剔除重復(fù)分組（注意DISTINCT和GROUP_ID實(shí)現(xiàn)的功能還是不一樣的，見(jiàn)GROUP_ID部分講解）。當(dāng)然，在GROUPING SETS中，ROLLUP和CUBE也可以混合使用，而且也能使用其他擴(kuò)展功能，如部分分組、復(fù)合列分組、連接分組等。
ROLLUP和CUBE就不能接受GROUPING SETS作為參數(shù)了，ROLLUP和CUBE之間互相作為參數(shù)也是不可以的。

GROUPING SETS總結(jié)

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


本文作者：丁 俊(上海新炬中北團(tuán)隊(duì)）

本文來(lái)源：“IT那活兒”公眾號(hào)

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

轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/129100.html

相關(guān)文章

  • GROUP BY你都不會(huì)！ROLLUP，CUBE，GROUPPING詳解

    摘要：當(dāng)向傳入一列時(shí)，會(huì)得到一個(gè)總計(jì)行。結(jié)果當(dāng)向傳遞兩列時(shí)，將會(huì)按照這兩列進(jìn)行分組，同時(shí)按照第一列的分組結(jié)果返回小計(jì)行。結(jié)果可以看出來(lái)結(jié)果是按照工廠和部門分別分組匯總的。選擇的就表示兩列都不為空。 Group By Group By 誰(shuí)不會(huì)啊？這不是最簡(jiǎn)單的嗎？越是簡(jiǎn)單的東西，我們?cè)綍?huì)忽略掉他，因?yàn)槲覀儾辉敢庠偃ド钊肓私馑? 小時(shí) SQL 極速入門（一）1 小時(shí) SQL 極速入門（二）1 ...

    only_do 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<