隨筆-109  評論-187  文章-25  trackbacks-0
           
          oracle的分析函數over 及開窗函數
          一:分析函數over
          Oracle從8.1.6開始提供分析函數,分析函數用于計算基于組的某種聚合值,它和聚合函數的不同之處是
          對于每個組返回多行,而聚合函數對于每個組只返回一行。
          下面通過幾個例子來說明其應用。                                       
          1:統計某商店的營業額。        
               date       sale
               1           20
               2           15
               3           14
               4           18
               5           30
              規則:按天統計:每天都統計前面幾天的總額
              得到的結果:
              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:統計各班成績第一名的同學信息
              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              
             
              通過:   
              --
              select * from                                                                       
              (                                                                            
              select name,class,s,rank()over(partition by class order by s desc) mm from t2
              )                                                                            
              where mm=1
              --
              得到結果:
              NAME   CLASS S                       MM                                                                                        
              ----- ----- ---------------------- ----------------------
              dss    1      95                      1                      
              gds    2      92                      1                      
              gf     3      99                      1                      
              ddd    3      99                      1          
             
              注意:
              1.在求第一名成績的時候,不能用row_number(),因為如果同班有兩個并列第一,row_number()只返回一個結果          
              2.rank()和dense_rank()的區別是:
                --rank()是跳躍排序,有兩個第二名時接下來就是第四名
                --dense_rank()l是連續排序,有兩個第二名時仍然跟著第三名
               
               
          3.分類統計 (并顯示信息)
              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                
             得到結果:
             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                      
             無法得到B列值       
            
          =====

          select * from test

          數據:
          A B C
          1 1 1
          1 2 2
          1 3 3
          2 2 5
          3 4 6


          ---將B欄位值相同的對應的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



          ---如果不需要已某個欄位的值分割,那就要用 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

           

          求個人工資占部門工資的百分比

          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

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

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

          請問這樣的sql 語句怎樣寫?  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載[未登錄] 2009-01-13 11:18 | lily
          這樣的帖子真多   回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載 2009-01-22 09:08 |
          select date,sale,sum(sale) over(order by date) from a  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載 2009-08-04 15:53 | wangs
          這帖子有點意思,這個問題我來回答
          select date,sale,sum(sale)over(partition by 1 order by sale) num from test_s  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載[未登錄] 2012-08-22 11:33 | FeiHu
          謝謝您!  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載 2013-06-26 11:23 | 菜鳥慢飛
          僅表達感謝,感謝博主  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載[未登錄] 2013-07-11 20:33 | Justin
          寫得很清晰,謝謝樓主!  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載 2013-09-03 11:10 | 小小小程序員
          非常感謝。正在學習中  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載 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  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載 2014-05-22 10:43 | 仙子
          學到了,謝謝樓主分享。  回復  更多評論
            
          # re: Oracle 語法之 OVER (PARTITION BY ..) 及開窗函數 轉載 2015-06-18 16:23 | 濤聲依舊
          @雙
          66666666  回復  更多評論
            
          主站蜘蛛池模板: 辛集市| 富锦市| 察隅县| 石渠县| 临汾市| 南江县| 子洲县| 泸定县| 紫阳县| 略阳县| 囊谦县| 吉隆县| 金堂县| 沅陵县| 宜阳县| 鲁甸县| 池州市| 保德县| 达州市| 新巴尔虎右旗| 沈丘县| 广平县| 理塘县| 保德县| 宝兴县| 油尖旺区| 中山市| 朝阳区| 铁力市| 濮阳县| 泸定县| 绥德县| 长治市| 和林格尔县| 汝城县| 景谷| 肃北| 永胜县| 韶山市| 东至县| 奉贤区|