ORACLE 分析函数详解: RollUP,Cube

1. RollUp
select sumyear,summonth,svcdesc,
sum(decode(sumio,'I',1,0) * decode(sumef,'E',1,0) * sumtype2 * decode(sumreefind,'N',1,0) * SumStandard) IE20,
sum(decode(sumio,'I',1,0) * decode(sumef,'E',1,0) * sumtype4 * decode(sumreefind,'N',1,0) * SumStandard) IE40,
sum(decode(sumio,'I',1,0) * decode(sumef,'E',1,0) * sumtype5 * decode(sumreefind,'N',1,0) * SumStandard) IE45,
sum(decode(sumio,'I',1,0) * decode(sumef,'F',1,0) * sumtype2 * decode(sumreefind,'N',1,0) * SumStandard) IF20,
sum(decode(sumio,'I',1,0) * decode(sumef,'F',1,0) * sumtype4 * decode(sumreefind,'N',1,0) * SumStandard) IF40,
sum(decode(sumio,'I',1,0) * decode(sumef,'F',1,0) * sumtype2 * decode(sumreefind,'Y',1,0) * SumStandard) IRF20,
sum(decode(sumio,'I',1,0) * decode(sumef,'F',1,0) * sumtype4 * decode(sumreefind,'Y',1,0) * SumStandard) IRF40,
sum(decode(sumio,'I',1,0) * decode(sumef,'F',1,0) * sumtype2 * decode(sumreefind,'N',1,0) * SumStandard) IRF45,
sum(decode(sumio,'I',1,0) * SumStandard) IALL,

sum(decode(sumio,'O',1,0) * decode(sumef,'E',1,0) * sumtype2 * decode(sumreefind,'N',1,0) * SumStandard) OE20,
sum(decode(sumio,'O',1,0) * decode(sumef,'E',1,0) * sumtype4 * decode(sumreefind,'N',1,0) * SumStandard) OE40,
sum(decode(sumio,'O',1,0) * decode(sumef,'E',1,0) * sumtype5 * decode(sumreefind,'N',1,0) * SumStandard) OE45,

sum(decode(sumio,'O',1,0) * decode(sumef,'F',1,0) * sumtype2 * decode(sumreefind,'N',1,0) * SumStandard) OF20,
sum(decode(sumio,'O',1,0) * decode(sumef,'F',1,0) * sumtype4 * decode(sumreefind,'N',1,0) * SumStandard) OF40,
sum(decode(sumio,'O',1,0) * decode(sumef,'F',1,0) * sumtype2 * decode(sumreefind,'Y',1,0) * SumStandard) ORF20,
sum(decode(sumio,'O',1,0) * decode(sumef,'F',1,0) * sumtype4 * decode(sumreefind,'Y',1,0) * SumStandard) ORF40,
sum(decode(sumio,'O',1,0) * decode(sumef,'F',1,0) * sumtype2 * decode(sumreefind,'N',1,0) * SumStandard) ORF45,
sum(decode(sumio,'O',1,0) * SumStandard) OALL,
sum(sumstandard) IOALL

from
(select sumyear,summonth,svcdesc,sumio,sumef,
 decode(substr(sumtype,1,1),'2',1,0) sumtype2, decode(substr(sumtype,1,1),'4',1,0) sumtype4,  decode(substr(sumtype,1,1),'5',1,0) sumtype5,
 sumreefind,sumcount,sumstandard
from xyqsummary ,xyqsvcline
where sumsailoff between to_date('20070101','yyyymmdd') and to_date('20080331235959','yyyymmddhh24miss')
and sumopr='YML' and sumstatus<>'X'
and sumsvc=svccode)

group by rollup(sumyear,summonth,svcdesc)
order by 1,2,3


SUMYEAR

SUMMONTH

SVCDESC

IE20

IE40

IE45

IF20

IF40

IRF20

IRF40

IRF45

IALL

OE20

OE40

OE45

OF20

OF40

ORF20

ORF40

ORF45

OALL

IOALL
2007 11 美达高雄线 0 110 0 0 0 0 0 0 170 0 0 0 3 12 0 10 3 25 195
2007 11 外运高雄线 0 0 0 9 88 0 0 9 403 0 0 0 52 552 0 48 52 742 1145
2007 11 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 1 16 0 0 1 17 17
2007 11 0 110 0 9 88 0 0 9 573 0 0 0 56 580 0 58 56 784 1357
2007 12 美达高雄线 100 320 0 0 0 0 0 0 460 0 0 0 0 0 0 0 0 0 460
2007 12 外运高雄线 80 370 0 49 136 0 0 49 765 0 0 0 88 608 0 138 88 888 1653
2007 12 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 1 2 0 0 1 3 3
2007 12 180 690 0 49 136 0 0 49 1225 0 0 0 89 610 0 138 89 891 2116
2007 180 800 0 58 224 0 0 58 1798 0 0 0 145 1190 0 196 145 1675 3473
2008 1 美达高雄线 80 100 22.5 0 0 0 0 0 212.5 0 0 0 0 0 0 0 0 0 212.5
2008 1 外运高雄线 80 280 11.25 17 150 0 0 17 748.25 0 0 0 54 664 0 162 54 997 1745.25
2008 1 阳明中东线 0 0 0 0 0 0 0 0 0 0 0 0 65 122 0 4 65 191 191
2008 1 鹭丰船务香港线 0 0 0 1 2 0 0 1 3 0 0 0 0 0 0 0 0 0 3
2008 1 160 380 33.75 18 152 0 0 18 963.75 0 0 0 119 786 0 166 119 1188 2151.75
2008 2 美达高雄线 0 478 0 4 150 0 0 4 632 0 0 0 0 0 0 0 0 0 632
2008 2 外运高雄线 0 180 0 1 82 0 0 1 423 0 0 0 33 276 0 30 33 375 798
2008 2 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 4 4
2008 2 阳明中东线 0 80 0 0 0 0 0 0 80 0 0 0 154 218 0 14 154 386 466
2008 2 0 738 0 5 232 0 0 5 1135 0 0 0 187 498 0 44 187 765 1900
2008 160 1118 33.75 23 384 0 0 23 2098.75 0 0 0 306 1284 0 210 306 1953 4051.75
340 1918 33.75 81 608 0 0 81 3896.75 0 0 0 451 2474 0 406 451 3628 7524.75


2. Cube
同样是上面的统计语句, 只把 group by 换成cube(sumyear,summonth,svcdesc)
SUMYEAR SUMMONTH SVCDESC IE20 IE40 IE45 IF20 IF40 IRF20 IRF40 IRF45 IALL OE20 OE40 OE45 OF20 OF40 ORF20 ORF40 ORF45 OALL IOALL
2007 11 美达高雄线 0 110 0 0 0 0 0 0 170 0 0 0 3 12 0 10 3 25 195
2007 11 外运高雄线 0 0 0 9 88 0 0 9 403 0 0 0 52 552 0 48 52 742 1145
2007 11 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 1 16 0 0 1 17 17
2007 11 0 110 0 9 88 0 0 9 573 0 0 0 56 580 0 58 56 784 1357
2007 12 美达高雄线 100 320 0 0 0 0 0 0 460 0 0 0 0 0 0 0 0 0 460
2007 12 外运高雄线 80 370 0 49 136 0 0 49 765 0 0 0 88 608 0 138 88 888 1653
2007 12 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 1 2 0 0 1 3 3
2007 12 180 690 0 49 136 0 0 49 1225 0 0 0 89 610 0 138 89 891 2116
2007 　 美达高雄线 100 430 0 0 0 0 0 0 630 0 0 0 3 12 0 10 3 25 655
2007 　 外运高雄线 80 370 0 58 224 0 0 58 1168 0 0 0 140 1160 0 186 140 1630 2798
2007 　 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 2 18 0 0 2 20 20
2007 180 800 0 58 224 0 0 58 1798 0 0 0 145 1190 0 196 145 1675 3473
2008 1 美达高雄线 80 100 22.5 0 0 0 0 0 212.5 0 0 0 0 0 0 0 0 0 212.5
2008 1 外运高雄线 80 280 11.25 17 150 0 0 17 748.25 0 0 0 54 664 0 162 54 997 1745.25
2008 1 阳明中东线 0 0 0 0 0 0 0 0 0 0 0 0 65 122 0 4 65 191 191
2008 1 鹭丰船务香港线 0 0 0 1 2 0 0 1 3 0 0 0 0 0 0 0 0 0 3
2008 1 160 380 33.75 18 152 0 0 18 963.75 0 0 0 119 786 0 166 119 1188 2151.75
2008 2 美达高雄线 0 478 0 4 150 0 0 4 632 0 0 0 0 0 0 0 0 0 632
2008 2 外运高雄线 0 180 0 1 82 0 0 1 423 0 0 0 33 276 0 30 33 375 798
2008 2 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 4 4
2008 2 阳明中东线 0 80 0 0 0 0 0 0 80 0 0 0 154 218 0 14 154 386 466
2008 2 0 738 0 5 232 0 0 5 1135 0 0 0 187 498 0 44 187 765 1900
2008 　 美达高雄线 80 578 22.5 4 150 0 0 4 844.5 0 0 0 0 0 0 0 0 0 844.5
2008 　 外运高雄线 80 460 11.25 18 232 0 0 18 1171.25 0 0 0 87 940 0 192 87 1372 2543.25
2008 　 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 4 4
2008 　 阳明中东线 0 80 0 0 0 0 0 0 80 0 0 0 219 340 0 18 219 577 657
2008 　 鹭丰船务香港线 0 0 0 1 2 0 0 1 3 0 0 0 0 0 0 0 0 0 3
2008 160 1118 33.75 23 384 0 0 23 2098.75 0 0 0 306 1284 0 210 306 1953 4051.75
　 1 美达高雄线 80 100 22.5 0 0 0 0 0 212.5 0 0 0 0 0 0 0 0 0 212.5
　 1 外运高雄线 80 280 11.25 17 150 0 0 17 748.25 0 0 0 54 664 0 162 54 997 1745.25
　 1 阳明中东线 0 0 0 0 0 0 0 0 0 0 0 0 65 122 0 4 65 191 191
　 1 鹭丰船务香港线 0 0 0 1 2 0 0 1 3 0 0 0 0 0 0 0 0 0 3
　 1 　 160 380 33.75 18 152 0 0 18 963.75 0 0 0 119 786 0 166 119 1188 2151.75
　 2 美达高雄线 0 478 0 4 150 0 0 4 632 0 0 0 0 0 0 0 0 0 632
　 2 外运高雄线 0 180 0 1 82 0 0 1 423 0 0 0 33 276 0 30 33 375 798
　 2 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 4 4
　 2 阳明中东线 0 80 0 0 0 0 0 0 80 0 0 0 154 218 0 14 154 386 466
　 2 　 0 738 0 5 232 0 0 5 1135 0 0 0 187 498 0 44 187 765 1900
　 11 美达高雄线 0 110 0 0 0 0 0 0 170 0 0 0 3 12 0 10 3 25 195
　 11 外运高雄线 0 0 0 9 88 0 0 9 403 0 0 0 52 552 0 48 52 742 1145
　 11 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 1 16 0 0 1 17 17
　 11 　 0 110 0 9 88 0 0 9 573 0 0 0 56 580 0 58 56 784 1357
　 12 美达高雄线 100 320 0 0 0 0 0 0 460 0 0 0 0 0 0 0 0 0 460
　 12 外运高雄线 80 370 0 49 136 0 0 49 765 0 0 0 88 608 0 138 88 888 1653
　 12 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 1 2 0 0 1 3 3
　 12 　 180 690 0 49 136 0 0 49 1225 0 0 0 89 610 0 138 89 891 2116
　 　 美达高雄线 180 1008 22.5 4 150 0 0 4 1474.5 0 0 0 3 12 0 10 3 25 1499.5
　 　 外运高雄线 160 830 11.25 76 456 0 0 76 2339.25 0 0 0 227 2100 0 378 227 3002 5341.25
　 　 万海高雄线 0 0 0 0 0 0 0 0 0 0 0 0 2 22 0 0 2 24 24
　 　 阳明中东线 0 80 0 0 0 0 0 0 80 0 0 0 219 340 0 18 219 577 657
　 　 鹭丰船务香港线 0 0 0 1 2 0 0 1 3 0 0 0 0 0 0 0 0 0 3
340 1918 33.75 81 608 0 0 81 3896.75 0 0 0 451 2474 0 406 451 3628 7524.75

可以看到,在cube函数的输出结果比使用rollup多出了几行统计数据.这就是cube函数做的汇总统计结果
另外,如果把查询语句换成以下内容:
select decode(grouping(sumyear),1,'ALL Year',sumyear),
decode(grouping(summonth),1,'ALL Month',summonth),
decode(grouping(svcdesc),1,'ALL Line',svcdesc),......
其结果就更清楚,所有的空值现在都根据grouping函数做出了很好的区分,这样利用rollup,cube和grouping函数,我们做数据统计的时候就可以轻松很多
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jasonsfu/p/1153086.html