• 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函数,我们做数据统计的时候就可以轻松很多
  • 相关阅读:
    Java中的IO操作和缓冲区
    Java是否还能再辉煌十年？
    Java的字符串操作
    WordCount(Java实现)
    自我介绍+软工5问
    数据库系统第六章【关系数据理论】（B站视频）
    ini 配置文件读取程序（C语言）
    epoll介绍 实例
    Blizzardhash算法oneway hash
    pychartdir模块安装
  • 原文地址：https://www.cnblogs.com/jasonsfu/p/1153086.html
Copyright © 2020-2023  润新知