Oracle 学习之窗口函数(over)
来源:互联网 发布:个人如何注册淘宝店铺 编辑:程序博客网 时间:2024/05/27 06: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;
- 1
- 2
- 3
- 4
- 5
- 6
如果是使用over 窗口函数,这个sql就变成了一行代码,如下
select t.*, (t.score-avg(t.score) over( partition by t.subject_id)) as gaps from test_student_score t
- 1
- 2
- 3
上面两句脚本都得到如下结果
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;
- 1
- 2
- 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;
- 1
- 2
- 3
结果如下
3.和错行函数(lag,lead)联合使用.lag上几行,lead 下几行
/*语法*/ lag(exp_str,offset,defval) over() Lead(exp_str,offset,defval) over() --exp_str要取的列 --offset取偏移后的第几行数据 --defval:没有符合条件的默认值
- 1
- 2
- 3
- 4
- 5
- 6
测试数据
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
以上就是Oracle 窗口函数的几个简单的应用.
- Oracle 学习之窗口函数(over)
- Oracle 学习之窗口函数(over)
- Oracle中窗口函数over()的学习
- oracle窗口函数over()的理解
- ORACLE窗口函数over的使用
- Oracle 分析函数之over()
- oracle分析函数之over()
- Oracle之over分析函数
- SQL2005学习笔记-窗口函数(OVER)
- Oracle 窗口函数over()与窗口子语句
- oracle over()函数及ratio_to_report()函数学习
- Oracle over函数和rank函数学习
- oracle之round函数、over()函数
- Sql Server2005 Transact-SQL 新兵器学习总结之-窗口函数(OVER)
- OVER 窗口函数
- oracle 分析函数之 over 用法
- Oracle开发专题之:分析函数(OVER)
- Oracle开发专题之:分析函数(OVER)
- NC休假、加班、销假合并报表sql
- sos
- pandas 日期处理
- python操作csv文件范例
- hsf入门
- Oracle 学习之窗口函数(over)
- iOS开发之App从点击到启动
- 爬取豆瓣TOP250电影的评分、评价人数、短评等信息,并在其保存在sql数据库中。
- Spring依赖注入
- Ueditor 表情本地化
- UTC 与 Unix时间戳
- 【Android training】应用程序内容共享
- vim基本快捷键
- 炫酷的空调调节器控件