Oracle文法之OVER(PARTITION BY)及開窗函數

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)