你的位置:首页 > 数据库

[数据库]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         290257 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               2902513 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    560018 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