Oracle中的窗口函数

本文介绍Oracle窗口函数的应用,包括over()函数与partitionby、orderby属性的使用,与统计函数、排名函数、错行函数的结合使用,展示了如何简化复杂SQL查询。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

      在工作的过程中,我们有时候需要编写复杂的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中的窗口函数窗口函数是一种用于查询和计算的特殊函数。它可以和其他函数(如统计函数、排名函数、错行函数)联合使用,能够在一次查询中进行多步计算。窗口函数的常用属性是partition by和order by,其中partition by类似于group by,用于将结果分成不同的分区,而order by用于指定排序的顺序。 引用给出了一个使用窗口函数的例子,查询每个同学单科成绩和班级相应单科成绩的差值。使用窗口函数可以简化这个查询,代码如下: ```sql select t.*, (t.score-avg(t.score) over( partition by t.subject_id)) as gaps from test_student_score t ``` 这个查询使用了over函数和partition by子句来计算每个同学单科成绩和班级相应单科成绩的差值。 引用中展示了另一个常用的窗口函数示例,用于去重。这个查询使用了row_number()函数配合over函数和partition by子句来对数据进行编号,并筛选出每个分组中的第一行数据。 总结来说,Oracle中的窗口函数是一种强大的工具,可以简化复杂的查询和计算,常用的属性有partition by和order by。它可以和其他函数联合使用,可以满足各种数据分析和计算的需求。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* [Oracle中的窗口函数](https://blog.youkuaiyun.com/wyqwilliam/article/details/82555712)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT0_1"}}] [.reference_item style="max-width: 50%"] - *3* [oracle的分组排序函数(窗口函数)](https://blog.youkuaiyun.com/m0_46636892/article/details/122984409)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT0_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值