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分析函数还有很多,在这里就没法一一概述,但是使用方式上和以上的几个函数类似,都是通过开窗函数进行分组,然后通过特定字段的排序,从而获取函数的统计值附加在数据行后面。