mssql sqlserver 关键字 GROUPING用法简要介绍及表达

2019-11-08 07:28栏目:网站首页

转自: http://www.maomao365.com/?p=6208  

GROUP_ID

摘要:
GROUPING 用于区分列是不是由 ROLLUP、CUBE 或 GROUPING SETS 聚合而发生的行
若果是原生态的行聚合,则重返0 ,新添的行数据就回去1

率先大家看看官方的解释:


图片 1

grouping 语法简要介绍 :
GROUPING (<列名>)
参数列名:

返回值
tinyint
<hr />
grouping 应用比方:  

大意是GROUP_ID用于区分相似分组标准的分组总括结果。

create table test(info varchar(30))
go
insert into test (info)values('a'),
('b'),('a'),('c'),('d'),('d') 
go

select info,count_big(info),grouping(info)
from test group by info 
WITH ROLLUP

go
drop table test 
----输出----
ifno 无列名 无列名
a    2    0
b    1    0
c    1    0
d    2    0
NULL    6    1

讲明起来比较抽象,上面大家来探望具体的案例。

 

例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)

下边大家来会见官方的表达:

图片 2

即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,….)

下边大家来探问官方的阐述:

图片 3

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> 

2> 

3> 

 

版权声明:本文由威尼斯人app发布于网站首页,转载请注明出处:mssql sqlserver 关键字 GROUPING用法简要介绍及表达