oracle开窗函数排序_数据库开窗函数

oracle开窗函数排序_数据库开窗函数Oracle 分析函数_开窗函数详解Oracle分析函数是Oracle系统自带函数中的一种,是Oracle专门用来解决具有复杂统计需求的函数,它可以对数据进行分组然后基于组中数据进行分析统计,最后在每组数据集中

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:返回组中的最小值       下面利用学生选课系统当中的学生成绩表的数据来做案例讲解,原始数据如下:   
oracle分析函数_开窗函数      例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       结果如下:   
oracle分析函数_开窗函数   通过数据,我们可以发现:   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       结果如下:   
oracle分析函数_开窗函数       通过数据可以看出:   1、ROW_NUMBER函数排名是返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。   2、rank函数返回一个唯一的值,但是当碰到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。比如数学成绩都是84分的两个人并列第二名,但是“张三丰”同学就是直接是第四名。   3、我们经常会利用row_number函数的排名机制(排名的唯一性)来过滤重复数据,即按照某一个特定的排序条件,通过排名为1的数据来重复数据当中最新的数据值。   总结       Oracle分析函数还有很多,在这里就没法一一概述,但是使用方式上和以上的几个函数类似,都是通过开窗函数进行分组,然后通过特定字段的排序,从而函数的统计值附加在数据行后面。

2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/86106.html

(0)
上一篇 2024年 7月 26日
下一篇 2024年 7月 26日

相关推荐

关注微信