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列值      
 
＝＝＝＝＝
二：開窗函數          
     開窗函數指定了分析函數工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化，舉例如下：
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)