oracle常用函数之over

  over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：
over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数
over(partition by deptno) 按照部门分区
over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录
over(order by salary rows between 50 perceding and 150 following)前50行，后150行
over（order by salary rows between unbounded preceding and unbounded following）所有行
over（order by salary range between unbounded preceding and unbounded following）所有行

例子:
1.统计各班成绩第一名的同学信息
    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
   注意：
    1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果         
    2.rank()和dense_rank()的区别是：
      --rank()是跳跃排序，有两个第二名时接下来就是第四名
      --dense_rank()是连续排序，有两个第二名时仍然跟着第三名
2.分类统计 (并显示信息)
    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

3：统计某商店的营业额。       
     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            .

select date,sale,sum(sale) over(partition by null order by date)
from market
这个是取每行记录之前的所有sale和,如果不加order by date,则是求总和

二：开窗函数          
      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
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(就是不到5行的全取,超过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                                                                
            
             
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)