总结: group by rollup(field1,field2); group by cube(field1,field2); group by grouping sets(field1,field2); 生成测试脚本： [c-sharp:nogutter:collapse:showcolumns] view plaincopyprint? ·········10········20········30
总结:
group by rollup(field1,field2);
group by cube(field1,field2);
group by grouping sets(field1,field2);
生成测试脚本：
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
sql> create table bill 2 (bill_month varchar2(6), 3 area_code integer, 4 net_type char(1), 5 local_fare number(10,2)) sql> create table bill 2 (bill_month varchar2(6), 3 area_code integer, 4 net_type char(1), 5 local_fare number(10,2))插入测试数据：
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5761,'j',5667089.85 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5762,'g',6315075.96 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5762,'j',6328716.15 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5763,'g',8861742.59 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5763,'j',7788036.32 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5764,'g',6028670.45 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5764,'j',6459121.49 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5765,'g',13156065.77); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5765,'j',11901671.70); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5761,'g',7614587.96 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5761,'j',5704343.05 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5762,'g',6556992.60 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5762,'j',6238068.05 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5763,'g',9130055.46 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5763,'j',7990460.25 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5764,'g',6387706.01 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5764,'j',6907481.66 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5765,'g',13562968.81); insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5765,'j',12495492.50); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5761,'g',7987050.65 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5761,'j',5723215.28 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5762,'g',6833096.68 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5762,'j',6391201.44 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5763,'g',9410815.91 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5763,'j',8076677.41 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5764,'g',6456433.23 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5764,'j',6987660.53 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5765,'g',14000101.20); insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5765,'j',12301780.20); insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5761,'g',8085170.84 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5761,'j',6050611.37 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5762,'g',6854584.22 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5762,'j',6521884.50 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5763,'g',9468707.65 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5763,'j',8460049.43 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5764,'g',6587559.23 ); insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5761,'j',5667089.85 );insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5762,'g',6315075.96 );insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5762,'j',6328716.15 );insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5763,'g',8861742.59 );insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5763,'j',7788036.32 );insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5764,'g',6028670.45 );insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5764,'j',6459121.49 );insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5765,'g',13156065.77);insert into bill (bill_month,area_code,net_type,local_fare) values('200405',5765,'j',11901671.70);insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5761,'g',7614587.96 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5761,'j',5704343.05 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5762,'g',6556992.60 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5762,'j',6238068.05 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5763,'g',9130055.46 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5763,'j',7990460.25 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5764,'g',6387706.01 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5764,'j',6907481.66 );insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5765,'g',13562968.81);insert into bill (bill_month,area_code,net_type,local_fare) values('200406',5765,'j',12495492.50);insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5761,'g',7987050.65 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5761,'j',5723215.28 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5762,'g',6833096.68 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5762,'j',6391201.44 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5763,'g',9410815.91 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5763,'j',8076677.41 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5764,'g',6456433.23 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5764,'j',6987660.53 );insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5765,'g',14000101.20);insert into bill (bill_month,area_code,net_type,local_fare) values('200407',5765,'j',12301780.20);insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5761,'g',8085170.84 );insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5761,'j',6050611.37 );insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5762,'g',6854584.22 );insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5762,'j',6521884.50 );insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5763,'g',9468707.65 );insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5763,'j',8460049.43 );insert into bill (bill_month,area_code,net_type,local_fare) values('200408',5764,'g',6587559.23 );
1.rollup
rollup是对group by的扩展，因此，它只能出现在group by子句中，依赖于分组的列，对每个分组会生成汇总数据，如下：
select ….
from ….
group by rollup(c1,c2,c3….c(n-1),c(n));
总共会进行n+1个分组,那么实际上有n+1个group by的union all结果。
第1个分组：全分组。c1,c2,c3….c(n-1),c(n)
第2个分组:c1,c2,c3….c(n-1);//这个分组实际上就是对前面前n-1列分组的小计.
----然后逐渐递减分组列
第n个分组:c1。对上一个分组的小计。
第n+1个分组。不分组全量汇总,相当于合计,也是对group by c1的小计,相当于group by null。
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
select nvl(to_char(area_code), '总计') area_code, sum(local_fare) local_fare from bill group by rollup(area_code) select nvl(to_char(area_code), '总计') area_code, sum(local_fare) local_farefrom billgroup by rollup(area_code)--result
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
area_code local_fare 5764 45814632.6 5761 54225413.04 5762 52039619.6 5763 69186545.02 5765 77418080.18 合计 298684290.44 area_code local_fare 5764 45814632.6 5761 54225413.04 5762 52039619.6 5763 69186545.02 5765 77418080.18 合计 298684290.44
2.cube
cube(交叉列表)也是对group by运算的一种扩展，它比rollup扩展更加精细，组合类型更多，rollup是按组合的列从右到左递减分组计算，而cube则是对所有可能的组合情况进行分组，这样分组的情况更多，覆盖所有的可能分组，并计算所有可能的分组的小计。比如：
cube(c1,c2,c3……c(n))对n个列进行cube分组，那么可能的分组情况有：
不分组：c(n,0)
取一列分组:c(n,1)
-----
取n列分组，全分组：c(n,n)
那么运用数学上的组合公式，得出所有所有可能的组合方式有:c(n,0)+c(n,1)+….+c(n,n)=2^n种。
我们以前面的rollup组合列为例子：rollup(name,month)是计算按区域名和月份分组以及每个区域的所有月份的小计以及总计。但是使用 cube(name,month)则有4种分组，比rollup多一个每个月的所有区域的小计。下面比较一下这两种分组方式：
分组公式 描述
rollup(name,month) 分组情况有：
group by name,month
group by name,null //每个区域所有月份小计
group by null,null //合计
cube(name,month) 分组情况有:
group by null,null //总计
group by null,month //每个月份的所有区域小计
group by name,null //每个区域的所有月份小计
group by name,month
cube使用方式：
和rollup一样，是
select …
from …
group by cube(分组列列表)
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
select (nvl(bill_month, '月份')) bill_month, (to_char(area_code)) area_code, sum(local_fare) local_fare from bill group by cube(area_code, bill_month) order by bill_month, area_code select (nvl(bill_month, '月份')) bill_month, (to_char(area_code)) area_code, sum(local_fare) local_farefrom billgroup by cube(area_code, bill_month)order by bill_month, area_code--result
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
bill_month area_code local_fare 200405 5761 13060433.89 200405 5762 12643792.11 200405 5763 16649778.91 200405 5764 12487791.94 200405 5765 25057737.47 200405 79899534.32 200406 5761 13318931.01 200406 5762 12795060.65 200406 5763 17120515.71 200406 5764 13295187.67 200406 5765 26058461.31 200406 82588156.35 200407 5761 13710265.93 200407 5762 13224298.12 200407 5763 17487493.32 200407 5764 13444093.76 200407 5765 26301881.4 200407 84168032.53 200408 5761 14135782.21 200408 5762 13376468.72 200408 5763 17928757.08 200408 5764 6587559.23 200408 52028567.24 月份 5761 54225413.04 月份 5762 52039619.6 月份 5763 69186545.02 月份 5764 45814632.6 月份 5765 77418080.18 月份 298684290.44 bill_month area_code local_fare200405 5761 13060433.89200405 5762 12643792.11200405 5763 16649778.91200405 5764 12487791.94200405 5765 25057737.47200405 79899534.32200406 5761 13318931.01200406 5762 12795060.65200406 5763 17120515.71200406 5764 13295187.67200406 5765 26058461.31200406 82588156.35200407 5761 13710265.93200407 5762 13224298.12200407 5763 17487493.32200407 5764 13444093.76200407 5765 26301881.4200407 84168032.53200408 5761 14135782.21200408 5762 13376468.72200408 5763 17928757.08200408 5764 6587559.23200408 52028567.24月份 5761 54225413.04月份 5762 52039619.6月份 5763 69186545.02月份 5764 45814632.6月份 5765 77418080.18月份 298684290.44
扩展一下，grouping函数
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
select decode(grouping(area_code), 1, 'all area', to_char(area_code)) area_code, decode(grouping(bill_month), 1, 'all month', bill_month) bill_month, sum(local_fare) local_fare from bill group by cube(area_code, bill_month) order by area_code, bill_month nulls last select decode(grouping(area_code), 1, 'all area', to_char(area_code)) area_code, decode(grouping(bill_month), 1, 'all month', bill_month) bill_month, sum(local_fare) local_farefrom billgroup by cube(area_code, bill_month)order by area_code, bill_month nulls last--result
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
5761 200405 13060433.89 5761 200406 13318931.01 5761 200407 13710265.93 5761 200408 14135782.21 5761 all month 54225413.04 5762 200405 12643792.11 5762 200406 12795060.65 5762 200407 13224298.12 5762 200408 13376468.72 5762 all month 52039619.6 5763 200405 16649778.91 5763 200406 17120515.71 5763 200407 17487493.32 5763 200408 17928757.08 5763 all month 69186545.02 5764 200405 12487791.94 5764 200406 13295187.67 5764 200407 13444093.76 5764 200408 6587559.23 5764 all month 45814632.6 5765 200405 25057737.47 5765 200406 26058461.31 5765 200407 26301881.4 5765 all month 77418080.18 all area 200405 79899534.32 all area 200406 82588156.35 all area 200407 84168032.53 all area 200408 52028567.24 all area all month 298684290.44 5761 200405 13060433.895761 200406 13318931.015761 200407 13710265.935761 200408 14135782.215761 all month 54225413.045762 200405 12643792.115762 200406 12795060.655762 200407 13224298.125762 200408 13376468.725762 all month 52039619.65763 200405 16649778.915763 200406 17120515.715763 200407 17487493.325763 200408 17928757.085763 all month 69186545.025764 200405 12487791.945764 200406 13295187.675764 200407 13444093.765764 200408 6587559.235764 all month 45814632.65765 200405 25057737.475765 200406 26058461.315765 200407 26301881.45765 all month 77418080.18all area 200405 79899534.32all area 200406 82588156.35all area 200407 84168032.53all area 200408 52028567.24all area all month 298684290.44
以上我们已经掌握了rollup,cube分组统计的知识。但是rollup和cube的分组统计包含了常规group by的统计明细以及对相关列的小计和合计值。如果我们需要的只是按每个分组列小计呢？oracle提供了grouping sets操作，对group by的另一个扩展，专门对分组列分别进行小计计算，不包括合计。使用方式和rollup和cube一样，都是放在group by中。如：
grouping sets(c1,c2….cn)则分组方式有n种,等于列的数目。
group by c1,null,null…..null。
group by null,c2,null….null。
….
group by null,null……..cn。
无group by null,null….null，也就是说没有合计行。
注意：grouping sets的统计结果和列的顺序无关。
有时我们只需要月、地区统计结果：
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
select decode(grouping(area_code), 1, 'all area', to_char(area_code)) area_code, decode(grouping(bill_month), 1, 'all month', bill_month) bill_month, sum(local_fare) local_fare from bill group by grouping sets(area_code, bill_month); select decode(grouping(area_code), 1, 'all area', to_char(area_code)) area_code, decode(grouping(bill_month), 1, 'all month', bill_month) bill_month, sum(local_fare) local_farefrom billgroup by grouping sets(area_code, bill_month);--result
[c-sharp:nogutter:collapse:showcolumns] view plaincopyprint?
·········10········20········30········40········50········60········70········80········90········100·······110·······120·······130·······140·······150
area_code bill_month local_fare 5764 all month 45814632.6 5761 all month 54225413.04 5762 all month 52039619.6 5763 all month 69186545.02 5765 all month 77418080.18 all area 200405 79899534.32 all area 200406 82588156.35 all area 200407 84168032.53 all area 200408 52028567.24 area_code bill_month local_fare5764 all month 45814632.65761 all month 54225413.045762 all month 52039619.65763 all month 69186545.025765 all month 77418080.18all area 200405 79899534.32all area 200406 82588156.35all area 200407 84168032.53all area 200408 52028567.24
3.rows
rows是物理行，就是按行的位置，根据位置计算窗口范围
sql query
[c-sharp:collapse] view plaincopyprint?
select bill_month,area_code,net_type,local_fare,sum(local_fare) over(order by local_fare rows between current row and 1 following) sum_fare from bill / bill_m area_code n local_fare sum_fare ------ ---------- - ---------- ---------- 200405 5761 j 5667089.85 11371432.9 200406 5761 j 5704343.05 11733013.5 200405 5764 g 6028670.45 12266738.5 200406 5762 j 6238068.05 12625774.1 200406 5764 g 6387706.01 12846827.5 200405 5764 j 6459121.49 13016114.1 200406 5762 g 6556992.6 13464474.3 200406 5764 j 6907481.66 14522069.6 200406 5761 g 7614587.96 15402624.3 200405 5763 j 7788036.32 15778496.6 200406 5763 j 7990460.25 16852202.8 bill_m area_code n local_fare sum_fare ------ ---------- - ---------- ---------- 200405 5763 g 8861742.59 17991798.1 200406 5763 g 9130055.46 9130055.46 13 rows selected. select bill_month,area_code,net_type,local_fare,sum(local_fare) over(order by local_fare rows between current row and 1 following) sum_farefrom bill/bill_m area_code n local_fare sum_fare------ ---------- - ---------- ----------200405 5761 j 5667089.85 11371432.9200406 5761 j 5704343.05 11733013.5200405 5764 g 6028670.45 12266738.5200406 5762 j 6238068.05 12625774.1200406 5764 g 6387706.01 12846827.5200405 5764 j 6459121.49 13016114.1200406 5762 g 6556992.6 13464474.3200406 5764 j 6907481.66 14522069.6200406 5761 g 7614587.96 15402624.3200405 5763 j 7788036.32 15778496.6200406 5763 j 7990460.25 16852202.8bill_m area_code n local_fare sum_fare------ ---------- - ---------- ----------200405 5763 g 8861742.59 17991798.1200406 5763 g 9130055.46 9130055.4613 rows selected.
4.range
range是逻辑行，是按单元格值和偏移量计算窗口范围.
range是逻辑行的范围 ，要经过 计算 的，一般range后面是数值或时间间隔等，这样根据 当行和range的表达 式能计算当
行对应的窗口范围；
[c-sharp:collapse] view plaincopyprint?
select bill_month,area_code,net_type,local_fare,sum(local_fare) over(order by local_fare range between current row and 122350 following) sum_fare from bill / bill_m area_code n local_fare sum_fare ------ ---------- - ---------- ---------- 200405 5761 j 5667089.85 11371432.9 200406 5761 j 5704343.05 5704343.05 200405 5764 g 6028670.45 6028670.45 200406 5762 j 6238068.05 6238068.05 200406 5764 g 6387706.01 12846827.5 200405 5764 j 6459121.49 13016114.1 200406 5762 g 6556992.6 6556992.6 200406 5764 j 6907481.66 6907481.66 200406 5761 g 7614587.96 7614587.96 200405 5763 j 7788036.32 7788036.32 200406 5763 j 7990460.25 7990460.25 bill_m area_code n local_fare sum_fare ------ ---------- - ---------- ---------- 200405 5763 g 8861742.59 8861742.59 200406 5763 g 9130055.46 9130055.46 13 rows selected. select bill_month,area_code,net_type,local_fare,sum(local_fare) over(order by local_fare range between current row and 122350 following) sum_fare from bill/bill_m area_code n local_fare sum_fare------ ---------- - ---------- ----------200405 5761 j 5667089.85 11371432.9200406 5761 j 5704343.05 5704343.05200405 5764 g 6028670.45 6028670.45200406 5762 j 6238068.05 6238068.05200406 5764 g 6387706.01 12846827.5200405 5764 j 6459121.49 13016114.1200406 5762 g 6556992.6 6556992.6200406 5764 j 6907481.66 6907481.66200406 5761 g 7614587.96 7614587.96200405 5763 j 7788036.32 7788036.32200406 5763 j 7990460.25 7990460.25bill_m area_code n local_fare sum_fare------ ---------- - ---------- ----------200405 5763 g 8861742.59 8861742.59200406 5763 g 9130055.46 9130055.4613 rows selected.
5.ratio_to_report
the ratio_to_report function computes the ratio of a value to the sum of a set of values. if the expressionvalue expression evaluates to null , ratio_to_report also evaluates to null , but it is treated as zero for computing the sum of values for the denominator. its syntax is:
ratio_to_report ( expr ) over ( [query_partition_clause] )
[c-sharp:collapse] view plaincopyprint?
select bill_month,area_code,net_type,local_fare,ratio_to_report(local_fare) over() rate from bill / bill_m area_code n local_fare rate ------ ---------- - ---------- ---------- 200405 5761 j 5667089.85 .062047734 200405 5763 g 8861742.59 .097025293 200405 5763 j 7788036.32 .085269516 200405 5764 g 6028670.45 .066006602 200405 5764 j 6459121.49 .070719517 200406 5761 g 7614587.96 .083370468 200406 5761 j 5704343.05 .062455612 200406 5762 g 6556992.6 .071791086 200406 5762 j 6238068.05 .068299251 200406 5763 g 9130055.46 .099962992 200406 5763 j 7990460.25 .087485812 bill_m area_code n local_fare rate ------ ---------- - ---------- ---------- 200406 5764 g 6387706.01 .069937604 200406 5764 j 6907481.66 .075628515