欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

程序员文章站 2022-04-21 16:38:02
前几天,看到一个群友用WITH ROLLUP运算符。由于自个儿没用过,看到概念及结果都云里雾里的,所以突然来了兴趣对生成结果测了一番。 一、概念: WITH CUBE:生成的结果集显示了所选列中值的所有组合的聚合。 WITH ROLLUP:生成的结果集显示了所选列中值的某一层次结构的聚合。 GROU ......

    前几天,看到一个群友用with rollup运算符。由于自个儿没用过,看到概念及结果都云里雾里的,所以突然来了兴趣对生成结果测了一番。

    一、概念:

    with cube:生成的结果集显示了所选列中值的所有组合的聚合。

    with rollup:生成的结果集显示了所选列中值的某一层次结构的聚合。

    grouping:当行由 with cube或with rollup运算符添加时,该函数将导致附加列的输出值为 1;当行不由 cube 或 rollup 运算符添加时,该函数将导致附加列的输出值为 0。仅在与包含 cube 或 rollup 运算符的 group by 子句相关联的选择列表中才允许分组。

    二、测试:

    1、建立临时表

create table #t0
(
    [grade] [varchar](50) null,     --年级
    [class] [varchar](50) null,     --班级
    [name] [varchar](50) null,      --姓名
    [course] [varchar](50) null,    --学科
    [result] [numeric](8,2) null    --成绩
)

create table #t1
(
    [id] [int] identity(1,1) not null,    --序号
    [grade] [varchar](50) null,           --年级
    [class] [varchar](50) null,           --班级
    [name] [varchar](50) null,            --姓名
    [course] [varchar](50) null,          --学科
    [result] [numeric](8,2) null          --成绩
)

create table #t2
(
    [id] [int] identity(1,1) not null,    --序号
    [grade] [varchar](50) null,           --年级
    [class] [varchar](50) null,           --班级
    [name] [varchar](50) null,            --姓名
    [course] [varchar](50) null,          --学科
    [result] [numeric](8,2) null          --成绩
)

     2、插入测试数据

insert into #t0 (grade,class,name,course,result)
select '2019','class1','9a01','c#',100
union
select '2019','class1','9a02','c#',100
union
select '2019','class2','9b01','c#',100
union
select '2019','class2','9b02','c#',100
union
select '2018','class1','8a01','java',100
union
select '2018','class1','8a02','java',100
union
select '2018','class2','8b01','java',100
union
select '2018','class2','8b02','java',100

    查询t0表结果:

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

    3、group by

    抛砖引玉,看看常用的group by排序:默认以select字段顺序(grade->class->name->course)进行排序,以下两种查询结果是一样的。

select grade,class,name,course,sum(result) result
from #t0
group by grade,class,name,course

select grade,class,name,course,sum(result) result
from #t0
group by grade,class,name,course
order by grade,class,name,course

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

    4、with cube

    原理1:以group by字段依次赋以null值进行分组聚合。

    原理2:第1个字段(即grade字段)生成结果:除原始数据外,以第1个字段固定赋以null值,然后其它字段依次赋以null值进行分组聚合,结果由右往左进行排序

    下面开始测第1个字段的结果是怎么来的:

insert into #t1 (grade,class,name,course,result)
select grade,class,name,course,sum(result) result 
from #t0 
group by grade,class,name,course

insert into #t1 (grade,class,name,course,result)
select 'zz' grade,class,name,course,sum(result) result 
from #t0 
group by class,name,course

insert into #t1 (grade,class,name,course,result)
select 'zz' grade,'zz' class,name,course,sum(result) result 
from #t0 
group by name,course

insert into #t1 (grade,class,name,course,result)
select 'zz' grade,'zz' class,'zz' name,course,sum(result) result 
from #t0 
group by course

insert into #t1 (grade,class,name,course,result)
select 'zz' grade,'zz' class,'zz' name,'zz' course,sum(result) result 
from #t0

--第1个字段结果排序由右往左
insert into #t2 (grade,class,name,course,result)
select grade,class,name,course,result from #t1 where id between 1 and 27 order by course,name,class,grade

update #t2 set grade=null where grade='zz'
update #t2 set class=null where class='zz'
update #t2 set name=null where name='zz'
update #t2 set course=null where course='zz'

    with cube的结果:

select grade,class,name,course,sum(result) result
from #t0
group by grade,class,name,course
with cube

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

    自已测试的结果:

select * from #t2

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

    结果与上面一致。

    其它字段优先跟哪个字段组合、最终怎样排序?呃,测过,没搞清楚……

    5、with rollup

    原理1:除原始数据外,以group by最后1个字段(即course字段)固定赋以null值,然后其它字段依次赋以null值进行分组聚合,结果由左往右进行排序

    这个跟with cube的第1个字段非常相象:一个是第1个字段,一个是最后1个字段;一个结果是由右往左排序,一个结果是由左往右排序。

    下面开始测结果是怎么来的:

truncate table #t1
truncate table #t2

insert into #t1 (grade,class,name,course,result)
select grade,class,name,course,sum(result) result 
from #t0 
group by grade,class,name,course

insert into #t1 (grade,class,name,course,result)
select grade,class,name,'zz' course,sum(result) result 
from #t0 
where not exists (select 1 from #t1 where grade=#t0.grade and class=#t0.grade and name=#t0.name and course='zz')
group by grade,class,name

insert into #t1 (grade,class,name,course,result)
select grade,class,'zz' name,'zz' course,sum(result) result 
from #t0 
where not exists (select 1 from #t1 where grade=#t0.grade and class=#t0.class and name='zz' and course='zz')
group by grade,class

insert into #t1 (grade,class,name,course,result)
select grade,'zz' class,'zz' name,'zz' course,sum(result) result 
from #t0 
where not exists (select 1 from #t1 where grade=#t0.grade and class='zz' and name='zz' and course='zz')
group by grade

insert into #t1 (grade,class,name,course,result)
select 'zz' grade,'zz' class,'zz' name,'zz' course,sum(result) result 
from #t0 

--结果排序由左往右
insert into #t2 (grade,class,name,course,result)
select grade,class,name,course,result from #t1 order by grade,class,name,course

update #t2 set grade=null where grade='zz'
update #t2 set class=null where class='zz'
update #t2 set name=null where name='zz'
update #t2 set course=null where course='zz'

    with rollup的结果:

select grade,class,name,course,sum(result) result
from #t0
group by grade,class,name,course
with rollup

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

    自己测试的结果:

select * from #t2

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

    结果与上面一致。

    6、grouping

    这个就比较容易理解了,with cube与with rollup用法一样,先看结果:

select grade,class,name,course,sum(result) result,grouping(course) [grouping]
from #t0
group by grade,class,name,course
with rollup

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

    上面grouping的是course字段,有null值就是with rollup额外添加的,grouping结果值为1。

    有了grouping,那做小计、总计就方便了。

select 
    grade,
    case when grouping(grade)=1 and grouping(class)=1 then '总计' when grouping(grade)=0 and grouping(class)=1 then '小计' else class end class,
    name,course,sum(result) result
from #t0
group by grade,class,name,course
with rollup

GROUP BY中的WITH CUBE、WITH ROLLUP原理测试及GROUPING应用

     好了,原理测试及应用就到这里结束了。