Oracle SQL中的汇总Group by, Rollup 与 Grouping等函数联用示例

Oracle 中，分组汇总函数 Group by，可以通过Rollup实现自动小结式的汇总，并且可以指定多个列，进行逐级汇总小计。以下整理了简单示例，以备不时之需。

Group by示例

1、数据示例

select * from (
  select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z911' type3, 100 amt from dual union all
  select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z912' type3, 100 amt from dual union all
  select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z913' type3, 100 amt from dual union all
  select 'A000001' pno, 'Z9' type1, 'Z92' type2, ''     type3, 80  amt from dual union all
  select 'A000001' pno, 'Z8' type1, ''    type2, ''     type3, 60  amt from dual
) a;
           

查询结果：

2、按照 type1、type2、type3汇总，其中，对type3进行 rollup

select a.pno, a.type1, a.type2, a.type3, sum(a.amt)
 from (
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z911' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z912' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z913' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z92' type2, ''     type3, 80  amt from dual union all
select 'A000001' pno, 'Z8' type1, ''    type2, ''     type3, 60  amt from dual
) a
group by a.pno, a.type1, A.type2, rollup( A.type3);
           

查询结果：

3、对type2, type3进行 rollup

select a.pno, a.type1, a.type2, a.type3, sum(a.amt)
 from (
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z911' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z912' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z913' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z92' type2, ''     type3, 80  amt from dual union all
select 'A000001' pno, 'Z8' type1, ''    type2, ''     type3, 60  amt from dual
) a
group by a.pno, a.type1, rollup(A.type2,  A.type3);
           

查询结果：

**4、rollup后使用grouping函数查看汇总行

select a.pno, a.type1, a.type2, a.type3, sum(a.amt), grouping(a.type1) g1, grouping(a.type2) g2, grouping(a.type3) g3
 from (
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z911' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z912' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z913' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z92' type2, ''     type3, 80  amt from dual union all
select 'A000001' pno, 'Z8' type1, ''    type2, ''     type3, 60  amt from dual
) a
group by a.pno, a.type1, rollup(A.type2,  A.type3);
           

查询结果：

**5、对比grouping_id()函数和grouping()函数的差别

select a.pno, a.type1, a.type2, a.type3, sum(a.amt), grouping(a.type1) g1, grouping(a.type2) g2, grouping(a.type3) g3
 from (
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z911' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z912' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z91' type2, 'Z913' type3, 100 amt from dual union all
select 'A000001' pno, 'Z9' type1, 'Z92' type2, ''     type3, 80  amt from dual union all
select 'A000001' pno, 'Z8' type1, ''    type2, ''     type3, 60  amt from dual
) a
group by a.pno, a.type1, rollup(A.type2,  A.type3);
           

查询结果：

Group by的配套函数介绍

Grouping()

grouping()函数的参数只有一个，而且必须为group by中出现的某一列。表示结果集的一行是否对该列做了grouping，若是，则返回1，否则为0。

Grouping_id()

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

本例中，grouping_id为3的，即对应二进制：11，表示两列都发生了grouping。

Group_id()

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

对于上述示例，并没有发生重复行的情况，若使用group_id，得到的结果如下：

延伸用法

Cube

cube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2的n 次方的grouping。

例如，group by cube(A,B,C) ，等效于：首先对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，©，最后对全表进行GROUP BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql。

Grouping sets

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

对比：rollup，N+1个分组方案；cube，2^N个分组方案；grouping sets，自定义罗列出分组方案。

示例待补充。