group by cube和rollup学习笔记

 建表如下： 

 

create table TEST_GROUP(NAME VARCHAR2(20),CLASS VARCHAR2(10),KEMU VARCHAR2(4),CHENGJI VARCHAR2(3));
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('张三', '1班', '数学', '90');
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('张三', '1班', '语文', '68');
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('李四', '1班', '数学', '90');
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('李四', '1班', '语文', '88');
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('王五', '2班', '数学', '70');
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('王五', '2班', '语文', '88');
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('小六', '2班', '数学', '95');
insert into TEST_GROUP (NAME, CLASS, KEMU, CHENGJI)
values ('小六', '2班', '语文', '98');

 

ROLLUP用法：

    GROUP BY ROLLUP(A, B, C)， 首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

select class, name, sum(chengji) from test_group
 group by rollup(class, name) order by class;

 

 

 

CUBE用法：

    GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)， 最后对全表进行GROUP BY操作。

select class, name, sum(chengji) from test_group
 group by cube(class, name) order by class;

 

 

grouping sets用法：

    grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping, 例如使用group by grouping sets(A,B,C)，则对(A),(B),(C)进行group by，如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的，也就是对(A)进行2次group by, grouping sets的参数允许重复

select class, name, sum(chengji) from test_group
 group by grouping sets(class,name);

 

 

grouping()函数排除空值用法:

    grouping():参数只有一个，而且必须为group by中出现的某一列，表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言，grouping()=0，反之为1；

select case
         when grouping(name) = '1' and grouping(class) = '0' then
          class || ' 合计'
         when grouping(name) = '0' and grouping(class) = '0' then
          name
         when grouping(name) = '1' and grouping(class) = '1' then
          '总计'
       end,
       sum(chengji)
  from test_group
 group by rollup(class, name)
 order by class;

 

 

having grouping()用法：

select decode(grouping(name), '1', '    总计:', name), sum(chengji)
  from test_group
 group by cube(class, name)
having grouping(class) = 1;

 

grouping_id()函数用法:

     grouping_id():参数可以是多个，但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，grouping(B)=0，则grouping_id(A,B)的返回值就是二进制的10，转成10进制就是2。

select grouping_id(class, name),
       grouping(class),
       grouping(name),
       name,
       class,
       sum(chengji)
  from test_group
 group by cube(class, name);

 

 

结果集看是有点乱，我们加上having条件看看：

select grouping_id(class, name),
       grouping(class),
       grouping(name),
       name,
       class,
       sum(chengji)
  from test_group
 group by cube(class, name)
having grouping_id(class, name) in(0,1,3);

 

 

 

group_id()函数用法:

    group_id():无参数。见上面的说明3），group by对某些列的集合会进行重复的grouping，而实际上绝大多数情况下对结果集中的这些重复行是不需要的，那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时，每行的group_id()分别是0,1,…,n，这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。

 

GROUP BY 的局限性

    如果不知道GROUP BY不能做什么，你对于它的学习就是不完整的。GROUP BY的局限性如下：

       1. LOB列，不能用做GROUP BY 表达式的一部分

       2. 子查询是不允许的

       3. 如果GROUP BY子句引用任何对象类型的列，则查询不能并行化