oracle分析函数之CUBE/ROLLUP

acle分析函数

一、rollup()
rollup（）汇总是指定字段列表的前n-1个字段进行分别组合汇总。
比如三个字段，就是按第一个字段汇总和第一二个字段组合汇总。

1.这是对一列进行分组，然后将得到数据在进行汇总
SQL> select EARNMONTH ,sum(PERSONINCOME) from e group by rollup(EARNMONTH) ;

EARNMONTH SUM(PERSONINCOME)

---

200912 2155.33
201001 2410.98
4566.31（汇总）

2.按每个时间进行分组，在按地点进行分组，对分组后的数据再进行一次小汇总，最后就是大汇总。
SQL> select EARNMONTH ,AREA,sum(PERSONINCOME) from e group by rollup(EARNMONTH,AREA) ;
EARNMONTH AREA SUM(PERSONINCOME)

---

200912 北平 1179.5
200912 金陵 975.83
200912 2155.33小汇总
201001 北平 757.5
201001 金陵 1653.48
201001 2410.98小汇总
4566.31大汇总
总结：就是从按左到右的顺序进行分组汇总，小组有小汇总，大组有大汇总
GROUP BY ROLLUP(A,B,C)
A B C
A B
A
所有记录的汇总
三列参考
SQL> select EARNMONTH ,AREA,sname,sum(PERSONINCOME) from e group by rollup(EARNMONTH,AREA,sname) ;

EARNMONTH AREA SNAME SUM(PERSONINCOME)

---

200912 北平 大凯 200
200912 北平 大魁 330
200912 北平 大亮 132
200912 北平 贱敬 330
200912 北平 小东 187.5
200912 北平 1179.5
200912 金陵 小凡 450.09
200912 金陵 小俐 0
200912 金陵 小妮 233.31
200912 金陵 小玉 183.75
200912 金陵 雪儿 108.68
200912 金陵 975.83
200912 2155.33
201001 北平 大凯 350
201001 北平 大魁 0
201001 北平 大亮 57.75
201001 北平 贱敬 231
201001 北平 小东 118.75
201001 北平 757.5
201001 金陵 小凡 283.39

EARNMONTH AREA SNAME SUM(PERSONINCOME)

---

201001 金陵 小俐 288
201001 金陵 小妮 899.91
201001 金陵 小玉 73.5
201001 金陵 雪儿 108.68
201001 金陵 1653.48
201001 2410.98
4566.31
二、看例子CUBE也是分组但是他在第一列数据进行分组后对第二列进行了一次单独分组汇总
总结：在原有的基础上对第n+1列进行一次汇总
1.
SQL> select EARNMONTH,AREA,sum(PERSONINCOME) from e group by cube(EARNMONTH,AREA) order by EARNMONTH,AREA;

EARNMONTH AREA SUM(PERSONINCOME)

---

200912 北平 1179.5
200912 金陵 975.83
200912 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 2410.98
北平 1937
金陵 2629.31
4566.31
9 rows selected

SQL> select EARNMONTH,AREA,sum(PERSONINCOME) from e group by rollup(EARNMONTH,AREA) order by EARNMONTH,AREA;

EARNMONTH AREA SUM(PERSONINCOME)

---

200912 北平 1179.5
200912 金陵 975.83
200912 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 2410.98
4566.31
7 rows selected

三、grouping 让空值更容易分辨
该函数在其参数列的值为超级分组记录所产生的null时返回1，其他情况返回0.

SQL> SELECT
2 DECODE (GROUPING(earnmonth),1,‘所有月份’,earnmonth) 月份,
3 DECODE (GROUPING(area),1,‘全部地区’,area) 地区,
4 SUM (personincome) 总金额
5 FROM e
6 GROUP BY CUBE(earnmonth, area)
7 ORDER BY earnmonth,area
8 NULLS LAST
9 ;

月份 地区 总金额

---

200912 北平 1179.5
200912 金陵 975.83
200912 全部地区 2155.33
201001 北平 757.5
201001 金陵 1653.48
201001 全部地区 2410.98
所有月份 北平 1937
所有月份 金陵 2629.31
所有月份 全部地区 4566.31

9 rows selected