oracle窗口函数多列分区,Oracle 学习之窗口函数(over)

在工作的过程中,我们有时候需要编写复杂的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脚本如下

1

2

3

4

5

6select 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就变成了一行代码,如下

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

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

2.1使用不连续排名rank()

order by t.score desc) as ranks from test_student_score t where t.subject_id = 3;

得到的结果

2.2使用连续排名dense_rank()3;

结果如下

3.和错行函数(lag,lead)联合使用.lag上几行,lead 下几行6/*语法*/

lag(exp_str,offset,defval) over()

Lead(exp_str,136); font-weight:400; margin-bottom:0px; margin-left:0px; margin-right:0px; margin-top:0px; padding-bottom:0px; padding-left:0px; padding-right:0px; padding-top:0px">over()

--exp_str要取的列

--offset取偏移后的第几行数据

--defval:没有符合条件的默认值

测试数据 select * from test_student_score t where t.subject_id = 3;

查上一行和下一行数据

6

7select t.subject_id,t.subject_id,lag(t.score,1,-1) over(by t.score) as lags,t.score,lead(t.score,136); font-weight:400; margin-bottom:0px; margin-left:0px; margin-right:0px; margin-top:0px; padding-bottom:0px; padding-left:0px; padding-right:0px; padding-top:0px">as leads 3;

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

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。