Oracle 学习之窗口函数(over)

原创 2016年08月28日 18:16:16

      在工作的过程中,我们有时候需要编写复杂的sql脚本.在这个时候,如果掌握一些数据库函数,对我们的工作可以事半功倍的作用. 这篇主要记录学习Oracle 的窗口函数的一些体会.
      窗口函数的名字是over()函数,常用的有两个属性partition by和order by,partition by类似于group by,我们通常将group by叫做分组,而partition by称作分区,分组返回通常是一行,partition by返回的是多行。
      窗口函数的威力是与其他函数联合使用,窗口函数主要和统计函数,排名函数,错行的函数联合使用.
      1.和统计函数(max,min,avg,sum等)连用.比如现在有一个简单的例子,查询每个同学单科成绩和班级相应单科成绩的差值.结果是这样,张三语文+1,张三数学-1.代表了张三语文比班级语文均分高一分,数学成绩比班级均分少一分.这是一个简单的需求.测试数据如下

这里写图片描述
      我们可以使用传统的group by 来查出每门功课的平均分,然后查出差值,sql脚本如下

select t1.*, (t1.score - t3.avgs) as gaps
  from test_student_score t1,
       (select t2.subject_id, avg(t2.score) as avgs
          from test_student_score t2
         group by t2.subject_id) t3
 where t1.subject_id = t3.subject_id;

      如果是使用over 窗口函数,这个sql就变成了一行代码,如下

select t.*,
       (t.score-avg(t.score) over( partition by t.subject_id)) as gaps 
   from test_student_score t

      上面两句脚本都得到如下结果
这里写图片描述
      2.和排名函数(rank,dense_rank)连用.先说明连个函数的意义:rank是不连续排名函数(1,1,3,3,5),dense_rank 是连续排名函数(1,1,2,2,3).
例:查出科目3 各同学成绩排名情况

这里写图片描述

      2.1使用不连续排名rank()

 select t.*, rank() over(order by t.score desc) as ranks
       from test_student_score t
          where t.subject_id = 3;

      得到的结果
这里写图片描述

      2.2使用连续排名dense_rank()

 select t.*, dense_rank() over(order by t.score desc) as ranks
   from test_student_score t
  where t.subject_id = 3;

结果如下
这里写图片描述

      3.和错行函数(lag,lead)联合使用.lag上几行,lead 下几行

/*语法*/  
lag(exp_str,offset,defval) over()  
Lead(exp_str,offset,defval) over()  
--exp_str要取的列  
--offset取偏移后的第几行数据  
--defval:没有符合条件的默认值 

测试数据
select * from test_student_score t where t.subject_id = 3;
这里写图片描述

      查上一行和下一行数据

  select t.subject_id,
         t.subject_id,
         lag(t.score, 1, -1) over(order by t.score) as lags,
         t.score,
         lead(t.score, 1, -1) over(order by t.score) as leads
    from test_student_score t
   where t.subject_id = 3;

这里写图片描述

以上就是Oracle 窗口函数的几个简单的应用.

版权声明:本文为博主原创文章,未经博主允许不得转载。

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

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

oracle的窗口windowing函数

目录 ========================================= 1.窗口函数简介 2.窗口函数示例-全统计 3.窗口函数进阶-滚动统计(累积/均值) 4.窗口函数进...
  • czw698
  • czw698
  • 2012年03月31日 11:55
  • 1295

Oracle 窗口函数

与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称...
  • yan19880901
  • yan19880901
  • 2012年10月31日 14:54
  • 790

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

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

oracle的分析函数over及开窗关键字range

oracle的分析函数over及开窗关键字range 博客分类:  DB OracleCC++C#工作  oracle的分析函数over 及开窗函数 一:分析函数over...
  • zs064811
  • zs064811
  • 2016年07月21日 10:00
  • 960

oracle分析函数:一、窗口子句的使用

/************************ *标题:分析函数analytic functions *时间:2016-07-07 *作者:clark ******************...
  • u011538954
  • u011538954
  • 2016年07月07日 16:25
  • 942

oracle中over()分析函数的用法

摘自: http://www.poluoluo.com/jzxy/201004/81921.html   百度文库也记载了oracle中over()分析函数的用法 在泡坛子的时候中无意中发现了这...
  • myflysun
  • myflysun
  • 2014年03月07日 00:18
  • 7420

Oracle 学习之窗口函数(over)

在工作的过程中,我们有时候需要编写复杂的sql脚本.在这个时候,如果掌握一些数据库函数,对我们的工作可以事半功倍的作用. 这篇主要记录学习Oracle 的窗口函数的一些体会.       窗口函...
  • zhu2695
  • zhu2695
  • 2017年11月15日 17:44
  • 102

Oracle中窗口函数over()的学习

Oracle中窗口函数over()的学习        近一个月没有写博客了,今天学习了一下Oracle中窗口函数的使用,所以将自己的学习心得分享给大家。         使用窗口函数可以解决一些...
  • long2010yu2010
  • long2010yu2010
  • 2013年08月27日 21:46
  • 2613

Oracle FIRST_VALUE函数语法详解及应用实例

查询语句中发现与主键匹配的机构名有多个,业务要求只显示排在最前面的一个。 试了group by,发现主查询返回记录数会狂增,用FIRST_VALUE后问题基本解决。 SQL语句与以下类似: ACSN ...
  • hpdlzu80100
  • hpdlzu80100
  • 2016年12月30日 20:05
  • 832
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 学习之窗口函数(over)
举报原因:
原因补充:

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