首页 > 代码库 > GROUP函数
GROUP函数
GROUP_ID
首先我们看看官方的解释：
大意是GROUP_ID用于区分相同分组标准的分组统计结果。
解释起来比较抽象，下面我们来看看具体的案例。
例1：单一分组
SQL> select group_id(),deptno,sum(sal) from emp group by rollup(deptno);
GROUP_ID() DEPTNO SUM(SAL)
---------- ---------- ----------
0 10 8750
0 20 10875
0 30 9400
0 29025
rollup(deptno)只是一个唯一的分组，所以产生的group_id()为0，代表这是同一个分组的结果。
下面我们来看看重复分组的情况
例2：重复分组
SQL> select group_id(),deptno,sum(sal) from emp group by rollup(deptno,deptno);
GROUP_ID() DEPTNO SUM(SAL)
---------- ---------- ----------
0 10 8750
0 20 10875
0 30 9400
1 10 8750
1 20 10875
1 30 9400
0 29025
7 rows selected.
group_id()为1代表这些是重复的分组。
注意：可通过having group_id() <1来剔除重复的分组。
老实说，我也看不出GROUP_ID在实际工作中的应用场景，姑且先记着。
GROUPING
其语法为：GROUPING(expr)
下面我们来看看官方的解释：
即GROUPING函数用于区分分组后的普通行和聚合行。如果是聚合行，则返回1，反之，则是0。
下面我们来看看具体的案例：
SQL> select grouping(deptno),grouping(job),deptno,job,sum(sal) from emp group by rollup(deptno,job);
GROUPING(DEPTNO) GROUPING(JOB) DEPTNO JOB SUM(SAL)
---------------- ------------- ---------- --------- ----------
0 0 10 CLERK 1300
0 0 10 MANAGER 2450
0 0 10 PRESIDENT 5000
0 1 10 8750
0 0 20 CLERK 1900
0 0 20 ANALYST 6000
0 0 20 MANAGER 2975
0 1 20 10875
0 0 30 CLERK 950
0 0 30 MANAGER 2850
0 0 30 SALESMAN 5600
0 1 30 9400
1 1 29025
13 rows selected.
首先我们看GROUPING(DEPTNO)这一列的结果，不难看出，凡是基于DEPTNO的汇总，GROUPING的结果均为0，因为最后一行是总的汇总，所以GROUPING的值为1.
基于这个逻辑，可以看出GROUPING(JOB)的值也是吻合的。
GROUPING_ID
GROUPING_ID是GROUPING的增强版，与GROUPING只能带一个表达式不同，它能带多个表达式。
语法如下：
GROUPING_ID(expr1, expr2, expr3,….)
下面我们来看看官方的解释：
GROUPING_ID在功能上相当于将多个GROUPING函数的结果串接成二进制数，返回的是这个二进制数对应的十进制数。
下面我们来看看具体的案例：
SQL> select grouping(deptno)g_d,grouping(job)g_j,grouping_id(deptno)gi_d,grouping_id(job)gi_j,grouping_id(deptno,job)gi_dj,grouping_id(job,deptno)gi_jd,deptno,job,sum(sal) from emp group by cube(deptno,job);
G_D G_J GI_D GI_J GI_DJ GI_JD DEPTNO JOB SUM(SAL)
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------
1 1 1 1 3 3 29025
1 0 1 0 2 1 CLERK 4150
1 0 1 0 2 1 ANALYST 6000
1 0 1 0 2 1 MANAGER 8275
1 0 1 0 2 1 SALESMAN 5600
1 0 1 0 2 1 PRESIDENT 5000
0 1 0 1 1 2 10 8750
0 0 0 0 0 0 10 CLERK 1300
0 0 0 0 0 0 10 MANAGER 2450
0 0 0 0 0 0 10 PRESIDENT 5000
0 1 0 1 1 2 20 10875
0 0 0 0 0 0 20 CLERK 1900
0 0 0 0 0 0 20 ANALYST 6000
0 0 0 0 0 0 20 MANAGER 2975
0 1 0 1 1 2 30 9400
0 0 0 0 0 0 30 CLERK 950
0 0 0 0 0 0 30 MANAGER 2850
0 0 0 0 0 0 30 SALESMAN 5600
18 rows selected.
大家看到这个案例估计都有点晕。。。
之所以这样提供，是为了呈现一个直观的结果进行对比。
解读这个结果，需要注意以下两点：
1> 若本行是某expr的汇总，那么该expr对应的二进制数位置为0否则置为1。
2> GROUPING_ID(expr1, expr2, expr3,….)的值其实是对应GROUPING(expr1),GROUPING(expr2),GROUPING(expr3)...值的串接。
首先看第一列，第三列，虽然一个是grouping(deptno)，一个是grouping_id(deptno)，因为只有一个表达式，所以两者的结果是一样的。第二列，第四列同样如此。
第五列的结果是第一列和第二列的数值的串接，然后返回的十进制数，以第二行为例，GI_DJ=2其实是二进制10转化为十进制后的数，其中1为G_D的值，0为G_J的值。
而GI_JD=1则是二进制01转化为十进制后的数，其中0为G_J的值，1为G_D的值。注意，串接的顺序为GROUPING_ID中表达式的顺序。
说了这么多，下面我们来看一个利用GROUPING_ID实现行列转换的案例。
with t as
( select grouping_id(deptno,job)gi_dj,deptno,job,count(*)cnt
from emp group by cube(deptno,job)),
t1 as
( select decode(gi_dj,0,deptno,1,deptno,99) deptno,decode(gi_dj,1,cnt,3,cnt)sub_total,
decode(job,‘CLERK‘,cnt) c1,decode(job,‘ANALYST‘,cnt)c2,decode(job,‘MANAGER‘,cnt)c3,
decode(job,‘SALESMAN‘,cnt)c4,decode(job,‘PRESIDENT‘,cnt)c5
from t)
select deptno,max(sub_total) sub_total,max(c1)clerk,max(c2)analyst,
max(c3)manager,max(c4)salesman,max(c5)president
from t1 group by deptno order by deptno;
最后生成的结果如下：
DEPTNO SUB_TOTAL CLERK ANALYST MANAGER SALESMAN PRESIDENT
---------- ---------- ---------- --------- ---------- ---------- ----------
10 3 1 1 1
20 5 2 2 1
30 6 1 1 4
99 14 4 2 3 4 1
其中，99代表合计，sub_total代表小计。这种统计类的需求在实际生产中还是应用蛮广的。
当然，该结果也可利用PIVOT函数实现，具体语句如下：
with t as(select grouping_id(deptno,job)gi_dj,deptno,job,count(*)cnt from emp group by cube(deptno,job)), t1 as (select decode(gi_dj,0,deptno,1,deptno,99)deptno,decode(gi_dj,0,job,2,job,9)job,cnt from t) select * from (select * from t1)pivot(sum(cnt)for job in (‘9‘,‘CLERK‘,‘ANALYST‘,‘MANAGER‘,‘SALESMAN‘,‘PRESIDENT‘)) order by deptno;
参考资料：
1> http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions070.htm#SQLRF00646
2> http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions071.htm#SQLRF00647
3> http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions072.htm#SQLRF00648
GROUP函数