Oracle 分析函数_开窗函数详解 Oracle分析函数是Oracle系统自带函数中的一种,是Oracle专门用来解决具有复杂统计需求的函数,它可以对数据进行分组然后基于组中数据进行分析统计,最后在每组数据集中的每一行中返回这个统计值。 Oracle分析函数不同于分组统计(group by),group by只能按照分组字段返回一个固定的统计值,但是不能在原来的数据行上带上这个统计值,而Oracle分析函数正是Oracle专门解决这类统计需求所开发出来的函数。 Oracle分析函数都会带上一个开窗函数over(),所以常把两者结合一起讲解。 Oracle分析函数的语法结构: select table.column, Analysis_function()OVER( [partition by 字段] [order by 字段 [windos]] ) as 统计值 from table 语法解析: 1、Analysis_function:指定分析函数名,常用的分析函数有sum、max、first_value、last_value、rank、row_number等等。 2、over():开窗函数名,partition by指定进行数据分组的字段,order by指定进行排序的字段,windos指定数据窗口(即指定分析函数要操作的行数),使用的语法形式大概如下: over(partition by xxx order by yyy rows between zzz) 下面就通过几个案例来讲解一下几个常用的分析函数。 first_value:返回组中数据窗口的第一个值。 last_value:返回组中数据窗口的最后一个值。 max:返回组中的最大值 min:返回组中的最小值 下面利用学生选课系统当中的学生成绩表的数据来做案例讲解,原始数据如下:
例1、利用min、max分别取出不同课程当中的学生成绩的最高值和最低值。 需求:在原始数据上附带上每门课的最高成绩和最低成绩。 代码如下: select c.stuname, b.coursename, t.score, –组中成绩最大值 max(t.score) over(partition by t.courseid) as score_max, –组中成绩最小值 min(t.score) over(partition by t.courseid) as score_min, –分组窗口的第一个值 (指定窗口为组中第一行到末尾行) first_value(t.score) over(partition by t.courseid order by t.score desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as score_first, –分组窗口的最后一个值(指定窗口为组中第一行到末尾行) last_value(t.score) over(partition by t.courseid order by t.score desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as score_last, –分组窗口的第一个值 (不指定窗口) first_value(t.score) over(partition by t.courseid order by t.score desc ) as score_first_1, –分组窗口的最后一个值(不指定窗口) last_value(t.score) over(partition by t.courseid order by t.score desc ) as score_last_1 from STUDENT.SCORE t, student.course b, student.stuinfo c where t.courseid = b.courseid and t.stuid = c.stuid 结果如下:
通过数据,我们可以发现: 1、min和max分析函数是直接组中的最小值和最大值。 2、first_value和last_value是返回窗口的第一行和最后一行数据,由于我们通过成绩字段对分组内的数据进行了降序排序,所以也可以达到在一定的窗口内最大值和最小值的功能。 3、排序不指定窗口时,就是按照组内的第一行到当前行作为窗口,然后取出窗口的第一行和最后一行。 4、窗口子语句当中的第一行是 unbounded preceding,当前行是 current row,最后一行是 unbounded following,所以正是利用窗口范围是第一行到最后一行,得到同一课程内的最大成绩和最小成绩。 ROW_NUMBER/RANK:根据开窗函数中排序的字段返回在组内的有序的偏移量,即可得到在组内的位置。 案例2、利用row_number、rank学生课程成绩的排名,具体代码如下: select c.stuname, b.coursename, t.score, –组内排名 row_number() over(partition by t.courseid order by t.score desc) as “row_number排名”, –组内排名 rank() over(partition by t.courseid order by t.score desc) as “rank排名” from STUDENT.SCORE t, student.course b, student.stuinfo c where t.courseid = b.courseid and t.stuid = c.stuid 结果如下:
通过数据可以看出: 1、ROW_NUMBER函数排名是返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。 2、rank函数返回一个唯一的值,但是当碰到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。比如数学成绩都是84分的两个人并列第二名,但是“张三丰”同学就是直接是第四名。 3、我们经常会利用row_number函数的排名机制(排名的唯一性)来过滤重复数据,即按照某一个特定的排序条件,通过排名为1的数据来重复数据当中最新的数据值。 总结 Oracle分析函数还有很多,在这里就没法一一概述,但是使用方式上和以上的几个函数类似,都是通过开窗函数进行分组,然后通过特定字段的排序,从而函数的统计值附加在数据行后面。
2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/86106.html