ORACLE中group by rollup/cube用法

Oracle数据库中的rollup配合group by命令使用，可以提供信息汇总功能（与"小计"相似）

示例如下：


SQL> select job,deptno,sal from emp;


JOB            DEPTNO   SAL

---------      ---------   ---------

CLERK          20          800

SALESMAN   30          1600

SALESMAN   30          1250

MANAGER     20          2975

SALESMAN   30          1250

MANAGER     30          2850

MANAGER     10          2450

ANALYST       20          3000

PRESIDENT   10          5000

SALESMAN    30          1500

CLERK          20          1100

CLERK          30          950

ANALYST       20          3000

CLERK          10          1300


已选择14行。


SQL> select job,deptno,sum(sal) total_sal from emp group by rollup(job,deptno);


JOB              DEPTNO     TOTAL_SAL

---------         ---------        ---------

ANALYST        20              6000

ANALYST                          6000

CLERK           10              1300

CLERK            20             1900

CLERK            30               950

CLERK                             4150

MANAGER       10             2450

MANAGER       20             2975

MANAGER       30             2850

MANAGER                        8275

PRESIDENT     10             5000

PRESIDENT                      5000

SALESMAN      30             5600

SALESMAN                       5600

                                      29025


已选择15行。

SQL>

--------------------------------------------------------------------------------

可以看出,

用了rollup的group by子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，同时也会对聚合列生成一个合计列。
例如在scott用户下面有一个emp表：
select deptno,job,sum(sal) from emp group by deptno,job;
会对每一个不同的dept,job生成一行独立的结果。

而
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
的结果中除了上述的结果结果之外，还会对每一个deptno进行一个小结，并单独生成一行，除此之外还会对所有的sal求和并生成一列。

这里的group by后面我们仅仅接了2列，实际上我们可以使用更多列的，这样的话oracle就会以从右向左的方式来进行逐个小结。

这里需要注意的是是使用了group by和rollup后，其后面的列要用括号括起来，否则将会出现ORA-00933: SQL 命令未正确结束的错误。

rollup的这个用法在我们做一些复杂的中国式报表时还是能大有用武之地的。

group by后使用rollup子句总结
今天接触到了group by后带rollup子句的用法（真是丑死人了，接触ORACLE一两年了今天才知道还有这样一个用法），group by后带rollup子句这个子句所产生的功能实在是太高级了，见了就高兴。但学习完后感觉也没什么的，很简单的。

下面按我的理解简单总结一下：

一、如何理解group by后带rollup子句所产生的效果
group by后带rollup子句的功能可以理解为：先按一定的规则产生多种分组，然后按各种分组统计数据（至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。因此要搞懂group by后带rollup子句的用法主要是搞懂它是如何按一定的规则产生多种分组的。另group by后带rollup子句所返回的结果集，可以理解为各个分组所产生的结果集的并集且没有去掉重复数据。下面举例说明：
1、对比没有带rollup的goup by
例：Group by A ,B
产生的分组种数：1种；
即group by A,B
返回结果集：也就是这一种分组的结果集。
2、带rollup但group by与rollup之间没有任何内容
例1：Group by rollup(A ,B)
产生的分组种数：3种；
第一种：group by A,B
第二种：group by A
第三种：group by NULL
（说明：本没有group by NULL 的写法，在这里指是为了方便说明，而采用之。含义是：没有分组，也就是所有数据做一个统计。例如聚合函数是SUM的话，那就是对所有满足条件的数据进行求和。此写法的含义下同)
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
例2：Group by rollup(A ,B,C)
产生的分组种数：4种；
第一种：group by A,B,C
第二种：group by A,B
第三种：group by A
第四种：group by NULL
返回结果集：为以上四种分组统计结果集的并集且未去掉重复数据。
3、带rollup但group by与rollup之间还包含有列信息
例1：Group by A , rollup(A ,B)
产生的分组种数：3种；
第一种：group by A,A,B    等价于group by A,B
第二种：group by A,A      等价于group by A
第三种：group by A,NULL  等价于group by A
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
例2：Group by C , rollup(A ,B)
产生的分组种数：3种；
第一种：group by C,A,B    
第二种：group by C,A      
第三种：group by C,NULL  等价于group by C
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
4、带rollup且rollup子句括号内又使用括号对列进行组合
例1：Group by rollup((A ,B))
产生的分组种数：2种；
第一种：group by A,B
第二种：group by NULL
返回结果集：为以上两种分组统计结果集的并集且未去掉重复数据。
例2：Group by rollup(A ,(B,C))
产生的分组种数：3种；
第一种：group by A,B,C
第二种：group by A
第三种：group by NULL
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
  注：对这种情况，可以理解为几个列被括号括在一起时，就只能被看成一个整体，分组时不需要再细化。因此也可推断rollup括号内也顶多加到一重括号，加多重了应该没有任何意义（这个推断我没有做验证的哦）。
二、与rollup组合使用的其它几个辅助函数
1、grouping()函数
  必须接受一列且只能接受一列做为其参数。参数列值为空返回1，参数列值非空返回0。
2、grouping_id()函数
  必须接受一列或多列做为其参数。
返回值为按参数排列顺序，依次对各个参数使用grouping()函数，并将结果值依次串成一串二进制数然后再转化为十进制所得到的值。
例如：grouping(A) = 0 ; grouping(B) = 1;
     则：grouping_id(A,B) = (01)2 = 1;
                    grouping_id(B,A) = (10)2 =2;

3、group_id()函数
  调用时不需要且不能传入任何参数。
返回值为某个特定的分组出现的重复次数(第一大点中的第3种情况中往往会产生重复的分组)。重复次数从0开始，例如某个分组第一次出现则返回值为0，第二次出现时返回值为1，……，第n次出现返回值为n-1。
       注：使用以上三个函数往往是为了过滤掉一部分统计数据，而达到美化统计结果的作用。
三、group by后带rollup子句与group by后带cube子句区别
group by后带rollup子句与group by后带cube子句的唯一区别就是：
带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。
例：Group by cube(A ,B,C)
产生的分组种数：8种；
第一种：group by A,B,C
第二种：group by A,B
第三种：group by A,C
第四种：group by B,C
第五种：group by C
第六种：group by B
第七种：group by A
第八种：group by NULL
返回结果集：为以上八种分组统计结果集的并集且未去掉重复数据。
四、group by后带grouping sets子句
   group by后带grouping sets子句效果就是只返回小记记录，即只返回按单个列分组后的统计数据，不返回多个列组合分组的统计数据。
例1：Group by grouping sets(A )
产生的分组种数：1种；
第一种：group by A
返回结果集：即为以上一种分组的统计结果集。
例2：Group by grouping sets(A ,B)
产生的分组种数：2种；
第一种：group by A
第二种：group by B
返回结果集：为以上两种分组统计结果集的并集且未去掉重复数据。
例3：Group by grouping sets (A ,B,C)
产生的分组种数：3种；
第一种：group by A
第二种：group by B
第三种：group by C
返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。
select t.city_name,
       decode(t.directline_type,
              null,
              '总数',
              directline_type,
              directline_type),
       sum(t.amount)
from tmp_dl_info t
group by rollup(t.city_name, t.directline_type)