oracle分析函数(一)——rollup、cube

发表于   |   分类于   |  

一、rollup介绍

rollup 通常和group by语句一起使用，是根据维度在分组的结果集中进行聚合操作（通常为汇总，这取决于SELECT后的聚合函数）。最常用的场景是：为每个分组返回一个小计，同时为所有分组返回总计。

在oracle中，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

返回结果集：为以上三种分组统计结果集的并集且未去掉重复数据。

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括号内也顶多加到一重括号，加多重了应该没有任何意义（这个推断我没有做验证的哦）。

其他：

  1. rollup的功能可以使用多个group by和union来实现；
  2. mysql中也有with rollup语法，但比较简单。只实现了上面的第二种功能。而且oracle中rollup可以和order by一起使用（mysql中不可以）

实例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select ename,deptno,sum(sal) from emp group by rollup(deptno,ename);

ENAME	       DEPTNO	SUM(SAL)
---------- ---------- ----------
KING		   10	    5000
CLARK		   10	    2450
MILLER		   10	    1300
		       10	    8750
FORD		   20	    3000
ADAMS		   20	    1100
JONES		   20	    2975
SCOTT		   20	    3000
SMITH		   20	     800
		       20	   10875
WARD		   30	    1250

ENAME	       DEPTNO	SUM(SAL)
---------- ---------- ----------
ALLEN		   30	    1600
BLAKE		   30	    2850
JAMES		   30	     950
MARTIN		   30	    1250
TURNER		   30	    1500
		       30	    9400
			            29025
1
2
3
4
5
select decode(grouping(f_line)+grouping(f_workarea),1,'小计',2,'总计',f_workarea) f_workarea,
decode(grouping(f_line),1,count(*)||'条',f_line) f_line,
sum(f_pagesnumber) sum_pagesnumbers  
from t_testcount 
group by rollup (f_workarea,f_line);

二、rollup的几个辅助函数

  • grouping()函数：

    必须接受一列且只能接受一列做为其参数。参数列值为空返回1，参数列值非空返回0。（如果参数的列在rollup中，则返回1；否则返回0）

  • grouping_id()函数：

    必须接受一列或多列做为其参数。返回值为按参数排列顺序，依次对各个参数使用grouping()函数，并将结果值依次串成一串二进制数然后再转化为十进制所得到的值。

例如：grouping(A) = 0 ; grouping(B) = 1;
则：grouping_id(A,B) = (01)2 = 1;
grouping_id(B,A) = (10)2 =2;

  • group_id()函数

    调用时不需要且不能传入任何参数。返回值为某个特定的分组出现的重复次数(第一大点中的第3种情况中往往会产生重复的分组)。重复次数从0开始，例如某个分组第一次出现则返回值为0，第二次出现时返回值为1，……，第n次出现返回值为n-1。

注：使用以上三个函数往往是为了过滤掉一部分统计数据，而达到美化统计结果的作用。

三、rollup和cube的区别

rollup是cube的一种特殊情况，和rollup一样，cube也是根据维度在分组的结果集中进行聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube需要2的N次方次分组操作，而rollup只需要N次分组操作。

带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。

  1. 假设有n个维度，rollup会有n个聚合：
    rollup(a,b) 统计列包含：(a,b)、(a)、()
    rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、()
    ……以此类推ing……

  2. 假设有n个纬度，cube会有2的n次方个聚合：
    cube(a,b) 统计列包含：(a,b)、(a)、(b)、()
    cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
    ……以此类推ing……

四、grouping sets(…) 介绍

oracle中，允许在group by后面使用 grouping sets(…) 语句。通过该语句可以实现rollup、cube同样的功能。

  1. Group by grouping sets(A ,B) 产生的分组种数：2种；
    • 第一种：group by A
    • 第二种：group by B

返回结果集：为以上两种分组统计结果集的并集且未去掉重复数据。

  1. Group by grouping sets (（A ,B）,A) 产生的分组种数：2种；
    • 第一种：group by A,B,A 等价于group by A,B
    • 第二种：group by A,NULL 等价于group by A

返回结果集：为以上二种分组统计结果集的并集且未去掉重复数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SQL> select ename,deptno,sum(sal) from emp group by grouping sets((ename,deptno),deptno);

ENAME	       DEPTNO	SUM(SAL)
---------- ---------- ----------
KING		   10	    5000
CLARK		   10	    2450
MILLER		   10	    1300
		   10	    8750
FORD		   20	    3000
ADAMS		   20	    1100
JONES		   20	    2975
SCOTT		   20	    3000
SMITH		   20	     800
		   20	   10875
WARD		   30	    1250

ENAME	       DEPTNO	SUM(SAL)
---------- ---------- ----------
ALLEN		   30	    1600
BLAKE		   30	    2850
JAMES		   30	     950
MARTIN		   30	    1250
TURNER		   30	    1500
		   30	    9400

17 rows selected.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL> select ename,deptno,sum(sal) from emp group by grouping sets((ename,deptno),deptno,null);

ENAME	       DEPTNO	SUM(SAL)
---------- ---------- ----------
KING		   10	    5000
CLARK		   10	    2450
MILLER		   10	    1300
		   10	    8750
FORD		   20	    3000
ADAMS		   20	    1100
JONES		   20	    2975
SCOTT		   20	    3000
SMITH		   20	     800
		   20	   10875
WARD		   30	    1250

ENAME	       DEPTNO	SUM(SAL)
---------- ---------- ----------
ALLEN		   30	    1600
BLAKE		   30	    2850
JAMES		   30	     950
MARTIN		   30	    1250
TURNER		   30	    1500
		   30	    9400
			   29025

18 rows selected.