orcale分析函数(二)

转载 2007年09月17日 10:52:00
分析函数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)
 

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

一.分析函数(OVER) 一、Oracle分析函数简介: 在日常的生产环境中,我们接触得比较多的是OLTP系统(即Online Transaction Process),这些系统的特点是具...
  • taolinsen
  • taolinsen
  • 2013年11月03日 13:02
  • 1693

orcale常用函数之转换函数-------orcale笔记

除了字符、数值和日期函数外,Oracle 还提供转换函数。转换函数将值从一种数据类型转换为另一种数据类型。Oracle 所提供的一些转换函数如下所示。 1.TO_CHAR(d [, fmt ]) ...
  • u011701534
  • u011701534
  • 2013年10月22日 21:50
  • 460

Oracle之分析函数、开窗函数

一、rank() over(partition by 字段1 order by 字段2) 从最经典的一个例子来说: 这是原始表结构: 现在需要增加一列,用来统计每天不同城市的完成率排名。 这意...
  • WalkToProgram
  • WalkToProgram
  • 2015年09月28日 11:08
  • 1011

orcale常用函数之聚合函数----------orcale笔记

=====================================聚合函数======================================================== /...
  • u011701534
  • u011701534
  • 2013年10月22日 21:03
  • 460

分析函数用法及窗口子句 range/rows差别

分析函数的语法结构一般是: 分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。 即由以下三部分组成: 分析函数名:如sum、max、...
  • u012557814
  • u012557814
  • 2017年05月05日 17:40
  • 1351

opencv3.0 函数学习 6——threshold 二值化

threshold 全局二值化 一幅图像包括目标物体、背景还有噪声,要想从多值的数字图像中直接提取出目标物体,最常用的方法就是设定一个全局的阈值T,用T将图像的数据分成两部分:大于T的像素群和小于T的...
  • banjiding8023
  • banjiding8023
  • 2016年08月08日 15:04
  • 2535

oracle分析函数技术详解(配上开窗函数over())

一、Oracle分析函数入门 分析函数是什么? 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个...
  • haiross
  • haiross
  • 2013年11月11日 11:36
  • 32360

orcale常用函数总结

1.数字函数round:四舍五入 trunc: 截断(保留小数点的问题) mod: 求余举例说明:select round(8.88,1) from dual; 结果:8.9 select t...
  • zhang_zha_zha
  • zhang_zha_zha
  • 2017年04月07日 13:49
  • 280

Oracle 分析函数与聚合函数(重点理解)

一、Oracle分析函数的语法与作用:   rank ( )             over ( [query_partition_clause]order_by_clause ) dense_r...
  • myflysun
  • myflysun
  • 2017年04月22日 21:14
  • 631

Orcale自定义函数

CREATE OR REPLACE FUNCTION "MY_DATABASE"."F_GET_USER_COUNT_BY_DEPART" --自定义函数名 ( ...
  • culous
  • culous
  • 2018年01月10日 16:37
  • 14
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:orcale分析函数(二)
举报原因:
原因补充:

(最多只允许输入30个字)