注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

太极虫的博客

好好学习,天天向上

 
 
 

日志

 
 

Oracle统计分析函数集之一   

2008-04-07 08:29:07|  分类: ORACLE技术 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

  评论这张
 
阅读(15)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017