oracle的分析函数over 及开窗函数

系统管理员 2022-08-07 13:59 183阅读 0赞

一：分析函数 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)