必威体育Betway必威体育官网
当前位置:首页 > IT技术

Oracle中的窗口函数

时间:2019-08-15 15:13:19来源:IT技术作者:seo实验室小编阅读:55次「手机版」
 

oracle 函数

 在工作的过程中,我们有时候需要编写复杂的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】存储过程的创建,查看,执行和重新编译;

我在参加【中原银行】的【极客大赛】,请大家支持下我的队伍,给【农夫种豆队】投上您宝贵的一票(微信,无需注册,无需填写手机号码,点击一

oracle创建、删除索引等操作

1、创建索引 create index 索引名 on 表名(列名); 2、删除索引 drop index 索引名; 3、创建组合索引 create index 索引

oracle中游标详细用法

转自:http://blog.csdn.net/liyong199012/article/details/8948952 游标的概念:    游标是SQL的一个内存工作区,由系统或用户

Oracle数据库 入门教程

Oracle数据库1.1  数据库环境安装1.Oracle数据库产品是免费的,我们可以从Oracle的官方网站(http://www.oracle.com)下载到程序安

Oracle to_date to_char TIMESTAMP

1:对于时间而言如果是dto类 中的时间的话一般写成String 比较好,若是数据库是Date 则to_date 转换一下就可以了,相对比较灵活,而实体

分享到:

栏目导航

推荐阅读

热门阅读