GROUPING_ID(expr [, expr ]...)
GROUPING_ID returns a number corresponding to the GROUPING bit vector associated with a row. GROUPING_ID is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE, and a GROUPING function. In queries with many GROUP BY expressions, determining the GROUP BY level of a particular row requires many GROUPING functions, which leads to cumbersome SQL. GROUPING_ID is useful in these cases.
GROUPING_ID返回一个数字，为相应的GROUPING。GROUPING_ID是只适用于在一个SELECT语句，它包含扩展GROUP BY，（如ROLLUP或CUBE）和分组功能。
GROUPING_ID is functionally equivalent to taking the results of multiple GROUPING functions and concatenating them into a bit vector (a string of ones and zeros). By using GROUPING_ID you can avoid the need for multiple GROUPING functions and make row filtering conditions easier to express. Row filtering is easier with GROUPING_ID because the desired rows can be identified with a single condition of GROUPING_ID = n. The function is especially useful when storing multiple levels of aggregation in a single table.
GROUPING_ID功能等效采取多个GROUPING函数的结果和连接成一个位向量（1和0的一个字符串）。通过使用GROUPING_ID可以避开需要多个分组功能，使行过滤条件更容易表达。行筛选更容易用GROUPING_ID，因为可以识别所需的行用一个条件GROUPING_ID = Ň。这个函数特别有用是在一个表中存储多个级别的集合。
3.用法
Select
Department_id,job_id,
Grouping(department_id) as dept,grouping(job_id) as job,
Grouping_id(department_id,job_id) as grp_id,
Sum(salary)
From employees
Group by cube(department_id,job_id)
Order by department_id,job_id;
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
10 AD_ASST 0 0 0 4400
10 0 1 1 4400
20 MK_MAN 0 0 0 13000
20 MK_REP 0 0 0 6000
20 0 1 1 19000
30 PU_CLERK 0 0 0 13900
30 PU_MAN 0 0 0 11000
30 0 1 1 24900
40 HR_REP 0 0 0 6500
40 0 1 1 6500
50 SH_CLERK 0 0 0 64300
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
50 ST_CLERK 0 0 0 55700
50 ST_MAN 0 0 0 36400
50 0 1 1 156400
60 IT_PROG 0 0 0 28800
60 0 1 1 28800
70 PR_REP 0 0 0 10000
70 0 1 1 10000
80 SA_MAN 0 0 0 61000
80 SA_REP 0 0 0 243500
80 0 1 1 304500
90 AD_PRES 0 0 0 24000
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
90 AD_VP 0 0 0 34000
90 0 1 1 58000
100 FI_ACCOUNT 0 0 0 39600
100 FI_MGR 0 0 0 12008
100 0 1 1 51608
110 AC_ACCOUNT 0 0 0 8300
110 AC_MGR 0 0 0 12008
110 0 1 1 20308
AC_ACCOUNT 1 0 2 8300
AC_MGR 1 0 2 12008
AD_ASST 1 0 2 4400
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
AD_PRES 1 0 2 24000
AD_VP 1 0 2 34000
FI_ACCOUNT 1 0 2 39600
FI_MGR 1 0 2 12008
HR_REP 1 0 2 6500
IT_PROG 1 0 2 28800
MK_MAN 1 0 2 13000
MK_REP 1 0 2 6000
PR_REP 1 0 2 10000
PU_CLERK 1 0 2 13900
PU_MAN 1 0 2 11000
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
SA_MAN 1 0 2 61000
SA_REP 0 0 0 7000
SA_REP 1 0 2 250500
SH_CLERK 1 0 2 64300
ST_CLERK 1 0 2 55700
ST_MAN 1 0 2 36400
0 1 1 7000
1 1 3 691416
已选择52行。
可以看出grouping_Id的位向量值计算：
Department_id job_iddeptjobgrp_id
Not nullNot null 0 0 0
Not null null 0 1 1
Null Not null 1 0 2
Null null 1 1 3
GROUPING_ID()的用武之地
GROUPING_ID()的一个用武之地在于使用HAVING子句过滤记录。HAVING子句可以将不包含小计或总计的记录除去，这只要通过简单的检查GROUPING_ID()的返回值，看其是否大于零就可以实现。
Select
Department_id,job_id,
Grouping(department_id) as dept,grouping(job_id) as job,
Grouping_id(department_id,job_id) as grp_id,
Sum(salary)
From employees
Group by cube(department_id,job_id)
Having Grouping_id(department_id,job_id) >0
Order by department_id,job_id;
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
10 0 1 1 4400
20 0 1 1 19000
30 0 1 1 24900
40 0 1 1 6500
50 0 1 1 156400
60 0 1 1 28800
70 0 1 1 10000
80 0 1 1 304500
90 0 1 1 58000
100 0 1 1 51608
110 0 1 1 20308
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
AC_ACCOUNT 1 0 2 8300
AC_MGR 1 0 2 12008
AD_ASST 1 0 2 4400
AD_PRES 1 0 2 24000
AD_VP 1 0 2 34000
FI_ACCOUNT 1 0 2 39600
FI_MGR 1 0 2 12008
HR_REP 1 0 2 6500
IT_PROG 1 0 2 28800
MK_MAN 1 0 2 13000
MK_REP 1 0 2 6000
DEPARTMENT_ID JOB_ID DEPT JOB GRP_ID SUM(SALARY)
------------- ---------- ---------- ---------- ---------- -----------
PR_REP 1 0 2 10000
PU_CLERK 1 0 2 13900
PU_MAN 1 0 2 11000
SA_MAN 1 0 2 61000
SA_REP 1 0 2 250500
SH_CLERK 1 0 2 64300
ST_CLERK 1 0 2 55700
ST_MAN 1 0 2 36400
0 1 1 7000
1 1 3 691416
已选择32行。