擴展group by語句

學(xué)習(xí)自《劍破冰山 Oracle開發(fā)藝術(shù)》第五章報表開發(fā)之?dāng)U展GROUP BY

對于簡單group by語句很難對復(fù)雜維度進行分析，難以達(dá)到實際生產(chǎn)的復(fù)雜報表需求，group by的擴展特性就需要了，union語句也可以達(dá)到需求但是sql復(fù)雜且效率低

1 rollup多維匯總

rollup，分組先進行常規(guī)分組，然后在此基礎(chǔ)上，通過將列從右向左移動，然后進行更高一級的小計，最后合計，注意rollup分組和列的順序相關(guān)

指定n列，有n+1種分組方式

部分rollup可以剔除某些不需要的小計和合計

例子

[oracle@localhost ~]$ sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 23 10:31:24 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

10:31:24 SCOTT@edw> set autotrace on
10:31:30 SCOTT@edw> 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           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
RESEARCH                      10875
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
ACCOUNTING                     8750
                              29025

13 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3067950682

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |    14 |   392 |     7  (29)| 00:00:01 |
|   1 |  SORT GROUP BY ROLLUP         |         |    14 |   392 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN                  |         |    14 |   392 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |    14 |   210 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMP     |    14 |   210 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        913  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         13  rows processed

10:31:34 SCOTT@edw>

可以看出僅僅dept和emp表均僅掃描一次，而如果是union來寫就會多次重復(fù)掃描，效率低

通過執(zhí)行計劃看到有個隱藏操作SORT GROUP BY ROLLUP ,顯示結(jié)果有序，一般還是要顯示排序的，默認(rèn)的排序不一定符合業(yè)務(wù)需求

rollup分組具有方向性

如果使用hint:expand_gset_to_union,則優(yōu)化器會將rollup轉(zhuǎn)換為對應(yīng)的union all操作，其他的grouping sets、cube也可以

部分rollup分組，將不需要小計的列從rollup拿出到group by中即可，當(dāng)然合計也沒有了

例子

10:31:34 SCOTT@edw> set autotrace off
10:43:49 SCOTT@edw> SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(b.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);

HIRE DNAME          JOB          SUM_SAL
---- -------------- --------- ----------
1980 RESEARCH       CLERK            800
1980 RESEARCH                        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
1982 ACCOUNTING     CLERK           1300
1982 ACCOUNTING                     1300
1987 RESEARCH       CLERK           1100
1987 RESEARCH       ANALYST         3000
1987 RESEARCH                       4100

17 rows selected.

Elapsed: 00:00:00.01
10:43:53 SCOTT@edw>

2 cube交叉報表

cube分組可以實現(xiàn)更精細(xì)復(fù)雜的統(tǒng)計，對不同維度的所以可能進行分析，生成交叉報表，cube分組，是從n列中先進行合計，即一個列不取，然后小計，即取1列到n-1列，最后n列全取，即標(biāo)準(zhǔn)分組

因為包含所有可能的組合，所以結(jié)果與列的順序無關(guān)，列順序僅僅影響默認(rèn)的隱藏排序而已，如果用了顯示排序則無所謂了

cube分組增加一列，可能結(jié)果是指數(shù)級的增長，分組種類2的n次方

語法類似，例子

11:02:40 SCOTT@edw> set autotrace on
11:02:48 SCOTT@edw>  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
-------------- --------- ----------
                              29025
               CLERK           4150
               ANALYST         6000
               MANAGER         8275
               SALESMAN        5600
               PRESIDENT       5000
SALES                          9400
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH                      10875
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING                     8750
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000

18 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2382666110

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |    14 |   392 |     7  (29)| 00:00:01 |
|   1 |  SORT GROUP BY                  |         |    14 |   392 |     7  (29)| 00:00:01 |
|   2 |   GENERATE CUBE                 |         |    14 |   392 |     7  (29)| 00:00:01 |
|   3 |    SORT GROUP BY                |         |    14 |   392 |     7  (29)| 00:00:01 |
|   4 |     MERGE JOIN                  |         |    14 |   392 |     6  (17)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  7 |      SORT JOIN                  |         |    14 |   210 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL         | EMP     |    14 |   210 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   7 - access("A"."DEPTNO"="B"."DEPTNO")
       filter("A"."DEPTNO"="B"."DEPTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1175  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         18  rows processed

11:02:52 SCOTT@edw>

可以看執(zhí)行計劃，結(jié)果也是有序的

部分cube分組，例子

11:06:24 SCOTT@edw>  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                      10875
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING                     8750
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000

12 rows selected.

Elapsed: 00:00:00.00
11:06:26 SCOTT@edw>

3 grouping sets實現(xiàn)小計

rollup和cube會產(chǎn)生各種標(biāo)準(zhǔn)分組、小計、合計，grouping sets則只關(guān)注指定維度的小計，n列的結(jié)果也是n種

如grouping sets(a,b,c)就是group by a、group by b和group by c的結(jié)果union all

例子

11:06:26 SCOTT@edw>  set autotrace on
11:12:33 SCOTT@edw> SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job,SUM(b.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 DNAME          JOB          SUM_SAL
---- -------------- --------- ----------
                    CLERK           4150
                    SALESMAN        5600
                    PRESIDENT       5000
                    MANAGER         8275
                    ANALYST         6000
     ACCOUNTING                     8750
     RESEARCH                      10875
     SALES                          9400
1987                                4100
1980                                 800
1982                                1300
1981                               22825

12 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2825031421

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |    14 |   448 |    17  (24)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION     |                           |       |       |            |          |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660D_29B9BB |       |       |            |          |
|   3 |    MERGE JOIN                  |                           |    14 |   504 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT                      |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_DEPT                   |     4 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |                           |    14 |   322 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL         | EMP                       |    14 |   322 |     3   (0)| 00:00:01 |
|   8 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660E_29B9BB |       |       |            |          |
|   9 |    HASH GROUP BY               |                           |     5 |    60 |     3  (34)| 00:00:01 |
|  10 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D660D_29B9BB |    14 |   168 |     2   (0)| 00:00:01 |
|  11 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660E_29B9BB |       |       |            |          |
|  12 |    HASH GROUP BY               |                           |     4 |    56 |     3  (34)| 00:00:01 |
|  13 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D660D_29B9BB |    14 |   196 |     2   (0)| 00:00:01 |
|  14 |   LOAD AS SELECT               | SYS_TEMP_0FD9D660E_29B9BB |       |       |            |          |
|  15 |    HASH GROUP BY               |                           |     1 |     8 |     3  (34)| 00:00:01 |
|  16 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D660D_29B9BB |    14 |   112 |     2   (0)| 00:00:01 |
|  17 |   VIEW                         |                           |     5 |   160 |     2   (0)| 00:00:01 |
|  18 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D660E_29B9BB |     5 |    60 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   6 - access("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")
       filter("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")


Statistics
----------------------------------------------------------
         23  recursive calls
         33  db block gets
         39  consistent gets
          4  physical reads
       2172  redo size
        962  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

11:12:36 SCOTT@edw>

執(zhí)行計劃可以看出，沒有默認(rèn)排序了，無序，和列的順序也無關(guān)

同理部分grouping sets分組，例子

11:12:36 SCOTT@edw> set autotrace off
11:17:03 SCOTT@edw> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.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 JOB          SUM_SAL
-------------- ---- --------- ----------
SALES               MANAGER         2850
SALES               CLERK            950
ACCOUNTING          MANAGER         2450
ACCOUNTING          PRESIDENT       5000
ACCOUNTING          CLERK           1300
RESEARCH            MANAGER         2975
SALES               SALESMAN        5600
RESEARCH            ANALYST         6000
RESEARCH            CLERK           1900
RESEARCH       1981                 5975
SALES          1981                 9400
RESEARCH       1987                 4100
ACCOUNTING     1981                 7450
ACCOUNTING     1982                 1300
RESEARCH       1980                  800

15 rows selected.

Elapsed: 00:00:00.01
11:17:05 SCOTT@edw>

注意此時的含義有較大的變化

cube、rollup作為grouping sets的參數(shù)

grouping sets只提供單列分組，沒有合計功能，如果需要提供合計，則可以將rollup或cube作為參數(shù)，例子

11:23:59 SCOTT@edw>  SELECT a.dname,b.job,SUM(b.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           4150
               SALESMAN        5600
               PRESIDENT       5000
               MANAGER         8275
               ANALYST         6000
ACCOUNTING                     8750
RESEARCH                      10875
SALES                          9400
                              29025
                              29025

10 rows selected.

Elapsed: 00:00:00.02
11:24:02 SCOTT@edw>

問題是產(chǎn)生了兩個合計行，因為rollup或cube作為grouping sets參數(shù)，相當(dāng)于每個rollup或cube操作的union all，等價于這就很好理解功能了

對于重復(fù)合計，使用distinct剔除即可，另外后面還有特殊的函數(shù)可以使用，group_id可以用來剔除重復(fù)分組(和distinct功能是不一樣的)

rollup和cube作為參數(shù)也可以混用，而且也可以使用其它擴展功能，如部分分組、復(fù)合列分組、連接分組等

rollup和cube不能接受grouping sets作為參數(shù)，rollup和cube互相作為參數(shù)也不行

4 組合列分組、連接分組、重置列分組

組合列分組、連接分組在復(fù)雜報表中用處很大。組合列分組用于剔除不必要的小計保留合計，連接分組按每個分組的笛卡爾積進行操作，分組更多更細(xì)。對于常規(guī)分組滿足不了的需求可以考慮

組合列即將多個列當(dāng)做整體對待，下列對比表可以清晰展示不同之處

連接分組更強大，允許group by后出現(xiàn)多個rollup、cube和grouping sets操作，這樣分組級別更多，報表更精細(xì)，實現(xiàn)很復(fù)雜的需求實際上不管是同類型的連接分組還是不通類型的連接分組之間，最后的分組級別種類都是每個擴展分組級別種類的乘積，分組級別是笛卡爾積，比如rollup(a,b),rollup(c)，最終3*2=6中分組級別

重復(fù)列分組也就是group by中允許重復(fù)列，比如group by rollup(a,(a,b))、group by a,rollup(a,b)

組合列分組

例子

14:48:13 SCOTT@edw> SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY rollup(a.dname,(to_char(b.hiredate,'yyyy'),b.job));

DNAME          HIRE JOB          SUM_SAL
-------------- ---- --------- ----------
SALES          1981 CLERK            950
SALES          1981 MANAGER         2850
SALES          1981 SALESMAN        5600
SALES                               9400
RESEARCH       1980 CLERK            800
RESEARCH       1981 ANALYST         3000
RESEARCH       1981 MANAGER         2975
RESEARCH       1987 CLERK           1100
RESEARCH       1987 ANALYST         3000
RESEARCH                           10875
ACCOUNTING     1981 MANAGER         2450
ACCOUNTING     1981 PRESIDENT       5000
ACCOUNTING     1982 CLERK           1300
ACCOUNTING                          8750
                                   29025

15 rows selected.

Elapsed: 00:00:00.00
14:48:16 SCOTT@edw>

組合列分組可以實現(xiàn)部分rollup和部分cube分組類似效果并且加上合計

但是這個也比較麻煩，對于需要cube、rollup合計并剔除部分小計的需求用grouping_id或grouping函數(shù)

cube和rollup均可以轉(zhuǎn)換為對應(yīng)的grouping sets

當(dāng)然反向也可以，不過意義不大

連接分組

例子

14:48:16 SCOTT@edw>  SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY rollup(a.dname,b.job),ROLLUP(to_char(b.hiredate,'yyyy'));

DNAME          HIRE JOB          SUM_SAL
-------------- ---- --------- ----------
SALES               CLERK            950
SALES               MANAGER         2850
SALES               SALESMAN        5600
SALES                               9400
RESEARCH            CLERK           1900
RESEARCH            ANALYST         6000
RESEARCH            MANAGER         2975
RESEARCH                           10875
ACCOUNTING          CLERK           1300
ACCOUNTING          MANAGER         2450
ACCOUNTING          PRESIDENT       5000
ACCOUNTING                          8750
                                   29025
RESEARCH       1980 CLERK            800
RESEARCH       1980                  800
               1980                  800
SALES          1981 CLERK            950
SALES          1981 MANAGER         2850
SALES          1981 SALESMAN        5600
SALES          1981                 9400
RESEARCH       1981 ANALYST         3000
RESEARCH       1981 MANAGER         2975
RESEARCH       1981                 5975
ACCOUNTING     1981 MANAGER         2450
ACCOUNTING     1981 PRESIDENT       5000
ACCOUNTING     1981                 7450
               1981                22825
ACCOUNTING     1982 CLERK           1300
ACCOUNTING     1982                 1300
               1982                 1300
RESEARCH       1987 CLERK           1100
RESEARCH       1987 ANALYST         3000
RESEARCH       1987                 4100
               1987                 4100

34 rows selected.

Elapsed: 00:00:00.01
14:57:57 SCOTT@edw>

相當(dāng)于兩個rollup的笛卡爾積

理解了之后，利用連接分組，cube可以用rollup轉(zhuǎn)換，如cube(a,b,c)等于rollup(a),rollup(b),rollup(c)，但是對于rollup和grouping sets轉(zhuǎn)換為cube一般沒啥用

連接分組一般是同類型的，不通類型的連接分組一般不常用

重復(fù)列分組

例子

14:57:57 SCOTT@edw>   SELECT a.dname,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY a.dname,ROLLUP(a.dname,b.job);

DNAME          JOB          SUM_SAL
-------------- --------- ----------
SALES          CLERK            950
SALES          MANAGER         2850
SALES          SALESMAN        5600
RESEARCH       CLERK           1900
RESEARCH       ANALYST         6000
RESEARCH       MANAGER         2975
ACCOUNTING     CLERK           1300
ACCOUNTING     MANAGER         2450
ACCOUNTING     PRESIDENT       5000
SALES                          9400
RESEARCH                      10875
ACCOUNTING                     8750
SALES                          9400
RESEARCH                      10875
ACCOUNTING                     8750

15 rows selected.

Elapsed: 00:00:00.00
15:07:14 SCOTT@edw>

沒啥意義的例子，只不過說明語法允許

5 三個擴展分組函數(shù):grouping、grouping_id、group_id

三個擴展分組函數(shù):grouping、grouping_id、group_id在生成有意義的報表、結(jié)果進行過濾、排序中有很重要的作用，常用于復(fù)雜的報表查詢

注意grouping和grouping_id函數(shù)的參數(shù)不能是組合列

grouping函數(shù)用于制作有意義的報表

grouping_id函數(shù)對結(jié)果過濾以及排序

group_id函數(shù)剔除重復(fù)行

grouping函數(shù)

在擴展group by子句來說，null表示小計或者合計，但是如果數(shù)據(jù)中本來就有null值呢？grouping函數(shù)專門處理擴展group by分組中null問題：

它只接受一個參數(shù)，且參數(shù)來自rollup、cube、grouping sets中的列。當(dāng)然也可以在group by而不在上述3個子句的列，不過結(jié)果肯定是0，沒有意義

grouping函數(shù)對于小計或合計的列返回1，否則返回0。用于區(qū)別是否原始數(shù)據(jù)中含null，常與decode一起使用。當(dāng)然也可以確定分組級別從而過濾一些行，不過會很煩，一般用grouping_id替代

例子

15:34:01 SCOTT@edw>  SELECT decode(GROUPING(a.dname),1,'全部部門',a.dname) dname,decode(grouping(b.mgr),1,'全部老板',b.mgr) mgr,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.mgr);

DNAME          MGR                                         SUM_SAL
-------------- ---------------------------------------- ----------
SALES          7698                                           6550
SALES          7839                                           2850
SALES          全部老板                                       9400
RESEARCH       7566                                           6000
RESEARCH       7788                                           1100
RESEARCH       7839                                           2975
RESEARCH       7902                                            800
RESEARCH       全部老板                                      10875
ACCOUNTING                                                    5000
ACCOUNTING     7782                                           1300
ACCOUNTING     7839                                           2450
ACCOUNTING     全部老板                                       8750
全部部門       全部老板                                      29025

13 rows selected.

Elapsed: 00:00:00.01
15:34:12 SCOTT@edw>

grouping_id函數(shù)

用于過濾分組級別和排序結(jié)果

可以接受多個參數(shù)，來自rollup、cube、grouping sets中的列，按列從左往右順序計算，是分組列則0，是小計或合計列為1，然后組合成為一個二進制數(shù)字叫做位向量，位向量轉(zhuǎn)化為10進制即最后的結(jié)果，代表分組級別，如cube(a,b),那么grouping_id(a,b)代表的如下

grouping_id的好處是可以對多列進行計算得到分組級別

例子

15:46:26 SCOTT@edw>  SELECT a.dname,b.mgr,b.job,SUM(b.sal) sum_sal FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY ROLLUP(a.dname,b.mgr,b.job) HAVING grouping_id(a.dname,b.mgr,b.job) IN (0,7);

DNAME                 MGR JOB          SUM_SAL
-------------- ---------- --------- ----------
SALES                7698 CLERK            950
SALES                7698 SALESMAN        5600
SALES                7839 MANAGER         2850
RESEARCH             7566 ANALYST         6000
RESEARCH             7788 CLERK           1100
RESEARCH             7839 MANAGER         2975
RESEARCH             7902 CLERK            800
ACCOUNTING                PRESIDENT       5000
ACCOUNTING           7782 CLERK           1300
ACCOUNTING           7839 MANAGER         2450
                                         29025

11 rows selected.

Elapsed: 00:00:00.00
15:46:29 SCOTT@edw>

group_id函數(shù)

group_id無參數(shù)，因為擴展group by子句允許多種復(fù)雜分組操作，有時候為了實現(xiàn)復(fù)雜報表，可能出現(xiàn)重復(fù)統(tǒng)計，而group_id函數(shù)可以區(qū)分重復(fù)分組結(jié)果，第一次出現(xiàn)為0，以后每次出現(xiàn)增1，group_id在select中出現(xiàn)沒啥意義，通常用于having子句剔除重復(fù)統(tǒng)計

例子

15:46:29 SCOTT@edw>  SELECT a.dname,b.job,SUM(b.sal) sum_sal,group_id() gi FROM dept a,emp b WHERE a.deptno=b.deptno GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job)) HAVING group_id()=0;

DNAME          JOB          SUM_SAL         GI
-------------- --------- ---------- ----------
               CLERK           4150          0
               SALESMAN        5600          0
               PRESIDENT       5000          0
               MANAGER         8275          0
               ANALYST         6000          0
ACCOUNTING                     8750          0
RESEARCH                      10875          0
SALES                          9400          0
                              29025          0

9 rows selected.

Elapsed: 00:00:00.01
15:55:55 SCOTT@edw>

posted @ 2020-03-23 15:59 九命貓幺閱讀(437) 評論(0) 收藏舉報

刷新頁面返回頂部

九命貓幺的筆記

多吃蔬菜多吃肉，多曬太陽多睡覺！