group by、rollup、cube的用法以及区别

 在此使用oracle，oracle 中的用法为 group by [rollup|cube]( colomn),sql server中的用法为group by colomn with [rollup|cube]

  首先要弄明白rollup 和cube，就要知道group by的用法，group by 为对列进行分组，只展现分组统计的值，而rollup 为分层次展现，cube为展现列中所有层次，听我娓娓道来。

1、搭建场地

首先建立一个group_by 表,列值分别为购买者名称，购买物品的大类，购买物品的小类，物品以及价格，

用户购买了一些3C以及衣物

create table group_by (
o_name varchar2(20),
o_class varchar2(20),
o_item varchar2(20),
o_object varchar2(20),
o_price int
)

insert into group_by values('springy','3C','phone','huawei',4000);
insert into group_by values('springy','3C','phone','xiaomi',3000);
insert into group_by values('springy','3C','computer','mac',10000);
insert into group_by values('springy','3C','phone','thinkpad',8000);
insert into group_by values('springy','3C','phone','huawei',4000);
insert into group_by values('springy','clothes','shoes','adidas',300);
insert into group_by values('springy','clothes','shoes','lining',400);
insert into group_by values('springy','clothes','pants','jackjones',500);
insert into group_by values('stephenson','clothes','shoes','adivon',200);
insert into group_by values('stephenson','clothes','shoes','nike',300);
insert into group_by values('stephenson','clothes','skirt','nike',300);
insert into group_by values('stephenson','clothes','skirt','adidas',400);
commit;

2、初识group by

此时向我们缓缓走来的是group by，group by 可以对数据进行分组求值。

首先对O_NAME,O_CLASS,O_ITEM进行group by 分组 ，查看购买者购买对应分类物品的价格合计。

select o_name, o_class, o_item, sum(o_price)
from group_by
group by o_name, o_class, o_item
order by o_name, o_class, o_item

我们可以看到购买者没类物品消耗的价格

3、rollup

此时我们对统计的数据不太理想，除了想看每个小类，还想看每个大类，以及他们两人的消费和

表格画出来就是这个意思

　　　　表3-1

这时，我们将其与上面group by的进行对比，发现多了这些

　　　　表3-2

而这些又是怎么出来的呢？

这是就回到了开始所说的，rollup为分层次展现，怎么个分层次法呢？

首先，我们要确定，group by 的为O_NAME,O_CLASS,O_ITEM，此时对这些值进行分层次展现，即为下表

其中第一行为group by出来的，接下来的三行即为分层次汇总统计，而最后的空白的一行为对购买者进行汇总统计。

带入我们的例子中，可以看出来即为表3-1，而第2-4行则为多出来的部分，在此也可以看出来rollup 对group by中的值有顺序要求

select o_name,o_class,o_item，sum(o_price),grouping(o_name ),grouping(o_class),grouping(o_item)
from group_by
group by rollup (o_name,o_class,o_item) 
order by o_name,o_class,o_item 

使用rollup出来的结果

此时，看到的结果和表格也不一样呀，其中还有这么多空格怎么处理？？别急，接下来会讲到

4、初始cube 

说完rollup，开始说cube

开始说cube为展现所有层次，这是什么意思呢？就是有这里面所有的分组汇总统计

 在此，看下我们的结果

select o_name,o_class,o_item，sum(o_price),grouping(o_name ),grouping(o_class),grouping(o_item)
from group_by
group by cube (o_name,o_class,o_item) 
order by o_name,o_class,o_item 

 

5、group by、rollup、cube的用法以及区别

cube的组合

使用数字表达一下，看上去至少会更清晰点

想必这样子看上去更明白些，空值部分即该行为合计，这里面列出了所有分组。

而cube与rollup的区别在于后面对2,3部分的分组

rollup分组

group by分组

想必这样子更直观

 6、处理空值

此时有一个方法为grouping() 可以查看该值是否有rollup|cube产生的，若为其值则为1，否则为0，使用一个case when判断一下就好。

下图为使用cube时，可以看出每个有cube产生合计，即空值处，其grouping的值都为1

select o_name,o_class,o_item，sum(o_price),grouping(o_name ),grouping(o_class),grouping(o_item)
from group_by
group by cube (o_name,o_class,o_item) 
order by o_name,o_class,o_item 

 

7 应用

7.1在统计时，若只有一列，那么cube和rollup没差别

select case
         when grouping(o_name) = '1' then
          '合计'
         else
          o_name
       end as customer,
       sum(o_price) as total,
       grouping(o_name) 
  from group_by
 group by rollup(o_name)
 order by o_name

 

在看到上图时，只对一列进行统计时，并且有合计值时使用cube和rollup都ok。

7.2 若进行两列的统计

select case
         when grouping(o_name) = '1' then
          count(distinct o_name) || '位'
         else
          o_name
       end as customer,
       case
         when grouping(o_class) = 1 and grouping(o_name) = 1 then
          '合计'
         when grouping(o_class) = 1 then
          '小计'
         else
          o_class
       end as class,
       sum(o_price) as total,
       grouping(o_name),
       grouping(o_class)
  from group_by
 group by rollup(o_name, o_class)
 order by o_name, o_class

看到类似上图时，总的合计中不出现第二列的详细情况时，只对两列进行总的合计时，使用rollup

7.3 两列分各种情况全统计

 此时，我除了想看到两位顾客和消费，还想看到两位在各个类上的消费是

select case
         when grouping(o_name) = '1' and grouping(o_class) = 1 then
          count(distinct o_name) || '位消费'
           when grouping(o_class) = 1
         then
          o_name
       end as customer,
       case
         when grouping(o_class) = 1 and grouping(o_name) = 1 then
          '合计'
         when grouping(o_class) = 1 then
          '小计'
         else
          o_class
       end as class,
       sum(o_price) as total,
       grouping(o_name),
       grouping(o_class)
  from group_by
 group by cube(o_name, o_class)
 order by o_name, o_class desc

 

对应的图就是上面这样子。

若果仍不清楚，就挨着试一下rollup和cube。