擴展group by語句_ZenDei技術網路在線

學習自《劍破冰山 Oracle開發藝術》第五章報表開發之擴展GROUP BY對於簡單group by語句很難對複雜維度進行分析，難以達到實際生產的複雜報表需求，group by的擴展特性就需要了，union語句也可以達到需求但是sql複雜且效率低1 rollup多維彙總rollup，分組先進行常規... ...

學習自《劍破冰山 Oracle開發藝術》第五章報表開發之擴展GROUP BY

對於簡單group by語句很難對複雜維度進行分析，難以達到實際生產的複雜報表需求，group by的擴展特性就需要了，union語句也可以達到需求但是sql複雜且效率低

1 rollup多維彙總

rollup，分組先進行常規分組，然後在此基礎上，通過將列從右向左移動，然後進行更高一級的小計，最後合計，註意rollup分組和列的順序相關

指定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來寫就會多次重覆掃描，效率低

通過執行計劃看到有個隱藏操作SORT GROUP BY ROLLUP ,顯示結果有序，一般還是要顯示排序的，預設的排序不一定符合業務需求

rollup分組具有方向性

如果使用hint:expand_gset_to_union,則優化器會將rollup轉換為對應的union all操作，其他的grouping sets、cube也可以

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

例子

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分組可以實現更精細複雜的統計，對不同維度的所以可能進行分析，生成交叉報表，cube分組，是從n列中先進行合計，即一個列不取，然後小計，即取1列到n-1列，最後n列全取，即標準分組

因為包含所有可能的組合，所以結果與列的順序無關，列順序僅僅影響預設的隱藏排序而已，如果用了顯示排序則無所謂了

cube分組增加一列，可能結果是指數級的增長，分組種類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>

可以看執行計劃，結果也是有序的

部分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實現小計

rollup和cube會產生各種標準分組、小計、合計，grouping sets則只關註指定維度的小計，n列的結果也是n種

如grouping sets(a,b,c)就是group by a、group by b和group by c的結果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>

執行計劃可以看出，沒有預設排序了，無序，和列的順序也無關

同理部分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的參數

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

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>

問題是產生了兩個合計行，因為rollup或cube作為grouping sets參數，相當於每個rollup或cube操作的union all，等價於這就很好理解功能了

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

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

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

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

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

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

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

重覆列分組也就是group by中允許重覆列，比如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>

組合列分組可以實現部分rollup和部分cube分組類似效果並且加上合計

但是這個也比較麻煩，對於需要cube、rollup合計並剔除部分小計的需求用grouping_id或grouping函數

cube和rollup均可以轉換為對應的grouping sets

當然反向也可以，不過意義不大

連接分組

例子

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>

相當於兩個rollup的笛卡爾積

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

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

重覆列分組

例子

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 三個擴展分組函數:grouping、grouping_id、group_id

三個擴展分組函數:grouping、grouping_id、group_id在生成有意義的報表、結果進行過濾、排序中有很重要的作用，常用於複雜的報表查詢

註意grouping和grouping_id函數的參數不能是組合列

grouping函數用於製作有意義的報表

grouping_id函數對結果過濾以及排序

group_id函數剔除重覆行

grouping函數

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

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

grouping函數對於小計或合計的列返回1，否則返回0。用於區別是否原始數據中含null，常與decode一起使用。當然也可以確定分組級別從而過濾一些行，不過會很煩，一般用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函數

用於過濾分組級別和排序結果

可以接受多個參數，來自rollup、cube、grouping sets中的列，按列從左往右順序計算，是分組列則0，是小計或合計列為1，然後組合成為一個二進位數字叫做位向量，位向量轉化為10進位即最後的結果，代表分組級別，如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函數

group_id無參數，因為擴展group by子句允許多種複雜分組操作，有時候為了實現複雜報表，可能出現重覆統計，而group_id函數可以區分重覆分組結果，第一次出現為0，以後每次出現增1，group_id在select中出現沒啥意義，通常用於having子句剔除重覆統計

例子

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>