ORACLE GROUPING_ID函數

可以使用GROUPING_ID函數藉助HAVING子句對記錄進行過濾，將不包含小計或者總計的記錄除去。GROUPING_ID()函數可以接受一列或多列，返回GROUPING位向量的十進位值。GROUPING位向量的計算方法是將按照順序對每一列調用GROUPING函數的結果組合起來。

 

關於GROUPING函數的使用方法可以參見我前面寫的一篇文章

http://blog.csdn.net/wh62592855/archive/2009/11/16/4818072.aspx

 

1、GROUPING_ID用法執行個體

SQL> select
  2  division_id,job_id,
  3  grouping(division_id) as div_grp,
  4  grouping(job_id) as job_grp,
  5  grouping_id(division_id,job_id) as grp_id,
  6  sum(salary)
  7  from employees2
  8  group by cube(division_id,job_id)
  9  order by division_id,job_id;

DIV JOB    DIV_GRP    JOB_GRP     GRP_ID SUM(SALARY)
--- --- ---------- ---------- ---------- -----------
BUS MGR          0          0          0      530000
BUS PRE          0          0          0      800000
BUS WOR          0          0          0      280000
BUS              0          1          1     1610000
OPE ENG          0          0          0      245000
OPE MGR          0          0          0      805000
OPE WOR          0          0          0      270000
OPE              0          1          1     1320000
SAL MGR          0          0          0     4446000
SAL WOR          0          0          0      490000
SAL              0          1          1     4936000

DIV JOB    DIV_GRP    JOB_GRP     GRP_ID SUM(SALARY)
--- --- ---------- ---------- ---------- -----------
SUP MGR          0          0          0      465000
SUP TEC          0          0          0      115000
SUP WOR          0          0          0      435000
SUP              0          1          1     1015000
    ENG          1          0          2      245000
    MGR          1          0          2     6246000
    PRE          1          0          2      800000
    TEC          1          0          2      115000
    WOR          1          0          2     1475000
                 1          1          3     8881000

21 rows selected.

 

2、GOURPING位向量計算

如上例所示

division_id          job_id           位向量      GROUPING_ID()傳回值

非空                     非空              00             0

非空                     空                 01             1

空                        非空              10             2

空                        空                 11             3

 

3、GROUPING_ID()的用武之地

GROUPING_ID()的一個用武之地在於使用HAVING子句過濾記錄。HAVING子句可以將不包含小計或總計的記錄除去，這隻要通過簡單的檢查GROUPING_ID()的傳回值，看其是否大於零就可以實現。

SQL> select
  2  division_id,job_id,
  3  grouping_id(division_id,job_id) as grp_id,
  4  sum(salary)
  5  from employees2
  6  group by cube(division_id,job_id)
  7  having grouping_id(division_id,job_id) > 0
  8  order by division_id,job_id;

DIV JOB     GRP_ID SUM(SALARY)
--- --- ---------- -----------
BUS              1     1610000
OPE              1     1320000
SAL              1     4936000
SUP              1     1015000
    ENG          2      245000
    MGR          2     6246000
    PRE          2      800000
    TEC          2      115000
    WOR          2     1475000
                 3     8881000

10 rows selected.

 

4、在GROUP BY子句中多次使用一個列

在GROUP BY子句中可以多次使用某個列，這樣可以實現對資料的重新組織，或是按照不同的資料分組進行統計。例如，下面這個查詢中包含一個GROUP BY子句，其中使用了兩次division_id列，第一次是對division_id進行分組，第二次是在ROLLUP中使用。

SQL> select division_id,job_id,sum(salary)
  2  from employees2
  3  group by division_id,rollup(division_id,job_id);

DIV JOB SUM(SALARY)
--- --- -----------
BUS MGR      530000
BUS PRE      800000
BUS WOR      280000
OPE ENG      245000
OPE MGR      805000
OPE WOR      270000
SAL MGR     4446000
SAL WOR      490000
SUP MGR      465000
SUP TEC      115000
SUP WOR      435000

DIV JOB SUM(SALARY)
--- --- -----------
BUS         1610000
OPE         1320000
SAL         4936000
SUP         1015000
BUS         1610000
OPE         1320000
SAL         4936000
SUP         1015000

19 rows selected.

 

但是需要注意，最後四行記錄和前面四行記錄是重複的。這種重複現象可以通過使用GROUP_ID()來消除。

 

5、使用GROUP_ID函數

GROUP_ID函數可用於消除GROUP BY子句返回的重複記錄。GROUP_ID()不接受任何參數。如果某個特定的分組重複出現n次，那麼GROUP_ID()返回從0到n-1之間的一個整數。下面我們重寫上面那個例子

SQL> select division_id,job_id,group_id(),sum(salary)
  2  from employees2
  3  group by division_id,rollup(division_id,job_id);

DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS MGR          0      530000
BUS PRE          0      800000
BUS WOR          0      280000
OPE ENG          0      245000
OPE MGR          0      805000
OPE WOR          0      270000
SAL MGR          0     4446000
SAL WOR          0      490000
SUP MGR          0      465000
SUP TEC          0      115000
SUP WOR          0      435000

DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS              0     1610000
OPE              0     1320000
SAL              0     4936000
SUP              0     1015000
BUS              1     1610000
OPE              1     1320000
SAL              1     4936000
SUP              1     1015000

19 rows selected.

可以通過HAVING子句來消除重複記錄，只返回GROUP_ID()等於0的記錄。

SQL> select division_id,job_id,group_id(),sum(salary)
  2  from employees2
  3  group by division_id,rollup(division_id,job_id)
  4  having group_id()=0;

DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS MGR          0      530000
BUS PRE          0      800000
BUS WOR          0      280000
OPE ENG          0      245000
OPE MGR          0      805000
OPE WOR          0      270000
SAL MGR          0     4446000
SAL WOR          0      490000
SUP MGR          0      465000
SUP TEC          0      115000
SUP WOR          0      435000

DIV JOB GROUP_ID() SUM(SALARY)
--- --- ---------- -----------
BUS              0     1610000
OPE              0     1320000
SAL              0     4936000
SUP              0     1015000

15 rows selected.