Oracle语法之OVER

 

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

 

求个人工资占部门工资的百分比

二:开窗函数         

      开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:

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)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值