隨筆-109  評(píng)論-187  文章-25  trackbacks-0
           
          oracle的分析函數(shù)over 及開(kāi)窗函數(shù)
          一:分析函數(shù)over
          Oracle從8.1.6開(kāi)始提供分析函數(shù),分析函數(shù)用于計(jì)算基于組的某種聚合值,它和聚合函數(shù)的不同之處是
          對(duì)于每個(gè)組返回多行,而聚合函數(shù)對(duì)于每個(gè)組只返回一行。
          下面通過(guò)幾個(gè)例子來(lái)說(shuō)明其應(yīng)用。                                       
          1:統(tǒng)計(jì)某商店的營(yíng)業(yè)額。        
               date       sale
               1           20
               2           15
               3           14
               4           18
               5           30
              規(guī)則:按天統(tǒng)計(jì):每天都統(tǒng)計(jì)前面幾天的總額
              得到的結(jié)果:
              DATE   SALE       SUM
              ----- -------- ------
              1      20        20           --1天           
              2      15        35           --1天+2天           
              3      14        49           --1天+2天+3天           
              4      18        67            .          
              5      30        97            .
               
          2:統(tǒng)計(jì)各班成績(jī)第一名的同學(xué)信息
              NAME   CLASS S                         
              ----- ----- ----------------------
              fda    1      80                     
              ffd    1      78                     
              dss    1      95                     
              cfe    2      74                     
              gds    2      92                     
              gf     3      99                     
              ddd    3      99                     
              adf    3      45                     
              asdf   3      55                     
              3dd    3      78              
             
              通過(guò):   
              --
              select * from                                                                       
              (                                                                            
              select name,class,s,rank()over(partition by class order by s desc) mm from t2
              )                                                                            
              where mm=1
              --
              得到結(jié)果:
              NAME   CLASS S                       MM                                                                                        
              ----- ----- ---------------------- ----------------------
              dss    1      95                      1                      
              gds    2      92                      1                      
              gf     3      99                      1                      
              ddd    3      99                      1          
             
              注意:
              1.在求第一名成績(jī)的時(shí)候,不能用row_number(),因?yàn)槿绻嘤袃蓚€(gè)并列第一,row_number()只返回一個(gè)結(jié)果          
              2.rank()和dense_rank()的區(qū)別是:
                --rank()是跳躍排序,有兩個(gè)第二名時(shí)接下來(lái)就是第四名
                --dense_rank()l是連續(xù)排序,有兩個(gè)第二名時(shí)仍然跟著第三名
               
               
          3.分類統(tǒng)計(jì) (并顯示信息)
              A   B   C                      
              -- -- ----------------------
              m   a   2                      
              n   a   3                      
              m   a   2                      
              n   b   2                      
              n   b   1                      
              x   b   3                      
              x   b   2                      
              x   b   4                      
              h   b   3
             select a,c,sum(c)over(partition by a) from t2                
             得到結(jié)果:
             A   B   C        SUM(C)OVER(PARTITIONBYA)      
             -- -- ------- ------------------------
             h   b   3        3                        
             m   a   2        4                        
             m   a   2        4                        
             n   a   3        6                        
             n   b   2        6                        
             n   b   1        6                        
             x   b   3        9                        
             x   b   2        9                        
             x   b   4        9                        
            
             如果用sum,group by 則只能得到
             A   SUM(C)                            
             -- ----------------------
             h   3                      
             m   4                      
             n   6                      
             x   9                      
             無(wú)法得到B列值       
            
          =====

          select * from test

          數(shù)據(jù):
          A B C
          1 1 1
          1 2 2
          1 3 3
          2 2 5
          3 4 6


          ---將B欄位值相同的對(duì)應(yīng)的C 欄位值加總
          select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
          from test

          A B C C_SUM
          1 1 1 1
          1 2 2 7
          2 2 5 7
          1 3 3 3
          3 4 6 6



          ---如果不需要已某個(gè)欄位的值分割,那就要用 null

          eg: 就是將C的欄位值summary 放在每行后面

          select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
          from test

          A B C C_SUM
          1 1 1 17
          1 2 2 17
          1 3 3 17
          2 2 5 17
          3 4 6 17

           

          求個(gè)人工資占部門工資的百分比

          SQL> select * from salary;

          NAME DEPT SAL
          ---------- ---- -----
          a 10 2000
          b 10 3000
          c 10 5000
          d 20 4000

          SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;

          NAME DEPT SAL PERCENT
          ---------- ---- ----- ----------
          a 10 2000 20
          b 10 3000 30
          c 10 5000 50
          d 20 4000 100

          二:開(kāi)窗函數(shù)           
                開(kāi)窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小,這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變化而變化,舉例如下:
          1:     
             over(order by salary) 按照salary排序進(jìn)行累計(jì),order by是個(gè)默認(rèn)的開(kāi)窗函數(shù)
             over(partition by deptno)按照部門分區(qū)
          2:
            over(order by salary range between 5 preceding and 5 following)
             每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過(guò)5,之后行幅度值不超過(guò)5
             例如:對(duì)于以下列
               aa
               1
               2
               2
               2
               3
               4
               5
               6
               7
               9
             
             sum(aa)over(order by aa range between 2 preceding and 2 following)
             得出的結(jié)果是
                      AA                       SUM
                      ---------------------- -------------------------------------------------------
                      1                       10                                                      
                      2                       14                                                      
                      2                       14                                                      
                      2                       14                                                      
                      3                       18                                                      
                      4                       18                                                      
                      5                       22                                                      
                      6                       18                                                                
                      7                       22                                                                
                      9                       9                                                                 
                       
             就是說(shuō),對(duì)于aa=5的一行 ,sum為   5-1<=aa<=5+2 的和
             對(duì)于aa=2來(lái)說(shuō) ,sum=1+2+2+2+3+4=14     ;
             又如 對(duì)于aa=9 ,9-1<=aa<=9+2 只有9一個(gè)數(shù),所以sum=9    ;
                        
          3:其它:
               over(order by salary rows between 2 preceding and 4 following)
                    每行對(duì)應(yīng)的數(shù)據(jù)窗口是之前2行,之后4行
          4:下面三條語(yǔ)句等效:           
               over(order by salary rows between unbounded preceding and unbounded following)
                    每行對(duì)應(yīng)的數(shù)據(jù)窗口是從第一行到最后一行,等效:
               over(order by salary range between unbounded preceding and unbounded following)
                     等效
               over(partition by null)
          posted on 2007-11-13 13:33 小小程序程序員混口飯吃 閱讀(50042) 評(píng)論(13)  編輯  收藏 所屬分類: oracle

          評(píng)論:
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2008-10-23 00:08 |
          很感謝bo主的這篇文章。  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2008-12-10 11:21 | 雪蒼狼
          感謝,寫(xiě)這文章的人,  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2008-12-25 15:37 | hlq
          您的文章講到:
          統(tǒng)計(jì)某商店的營(yíng)業(yè)額。
          date sale
          1 20
          2 15
          3 14
          4 18
          5 30
          規(guī)則:按天統(tǒng)計(jì):每天都統(tǒng)計(jì)前面幾天的總額
          得到的結(jié)果:
          DATE SALE SUM
          ----- -------- ------
          1 20 20 --1天
          2 15 35 --1天+2天
          3 14 49 --1天+2天+3天
          4 18 67 .
          5 30 97 .

          請(qǐng)問(wèn)這樣的sql 語(yǔ)句怎樣寫(xiě)?  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載[未登錄](méi) 2009-01-13 11:18 | lily
          這樣的帖子真多   回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2009-01-22 09:08 |
          select date,sale,sum(sale) over(order by date) from a  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2009-08-04 15:53 | wangs
          這帖子有點(diǎn)意思,這個(gè)問(wèn)題我來(lái)回答
          select date,sale,sum(sale)over(partition by 1 order by sale) num from test_s  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載[未登錄](méi) 2012-08-22 11:33 | FeiHu
          謝謝您!  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2013-06-26 11:23 | 菜鳥(niǎo)慢飛
          僅表達(dá)感謝,感謝博主  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載[未登錄](méi) 2013-07-11 20:33 | Justin
          寫(xiě)得很清晰,謝謝樓主!  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2013-09-03 11:10 | 小小小程序員
          非常感謝。正在學(xué)習(xí)中  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2013-12-10 10:24 | lin385200
          @hlq
          Select sale_date,Sum(sale_cnt) over
          (Order By to_number(sale_date) range between unbounded preceding and 0 following) From test_xxx  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2014-05-22 10:43 | 仙子
          學(xué)到了,謝謝樓主分享。  回復(fù)  更多評(píng)論
            
          # re: Oracle 語(yǔ)法之 OVER (PARTITION BY ..) 及開(kāi)窗函數(shù) 轉(zhuǎn)載 2015-06-18 16:23 | 濤聲依舊
          @雙
          66666666  回復(fù)  更多評(píng)論
            
          主站蜘蛛池模板: 高州市| 沿河| 鱼台县| 嘉峪关市| 莱芜市| 莱州市| 湟源县| 陇川县| 望奎县| 沾益县| 佛坪县| 昭平县| 金门县| 阿克| 南岸区| 禹城市| 天气| 西乌珠穆沁旗| 博乐市| 泸州市| 本溪市| 资阳市| 菏泽市| 三江| 满洲里市| 栾川县| 平远县| 临武县| 特克斯县| 连州市| 合川市| 勐海县| 沐川县| 丰顺县| 临清市| 马鞍山市| 浦江县| 滁州市| 赤峰市| 比如县| 紫金县|