16、分析函数_开窗函数

Oracle分析函数

Oracle分析函数是Oracle系统自带函数中的一种，是Oracle专门用来解决具有复杂统计需求的函数，它可以对数据进行分组然后基于组中数据进行分析统计，最后在每组数据集中的每一行中返回这个统计值。

Oracle分析函数不同于分组统计（group by），group by只能按照分组字段返回一个固定的统计值，但是不能在原来的数据行上带上这个统计值，而Oracle分析函数正是Oracle专门解决这类统计需求所开发出来的函数。

Oracle分析函数都会带上一个开窗函数over（），所以常把两者结合一起讲解。

select table.column, 
Analysis_function()OVER(
[partition by 字段]
[order by 字段 [windows]]
) as 统计值
from table

语法解析：

• Analysis_function：指定分析函数名，常用的分析函数有sum、max、first_value、last_value、rank、row_number等等。

• over()：开窗函数名，partition by指定进行数据分组的字段，order by指定进行排序的字段，windos指定数据窗口（即指定分析函数要操作的行数），使用的语法形式大概如下：

  over(partition by xxx order by yyy rows between zzz)

常用的分析函数：
first_value：返回组中数据窗口的第一个值。

first_value：返回组中数据窗口的第一个值。
last_value：返回组中数据窗口的最后一个值。
max：返回组中的最大值
min：返回组中的最小值

实例：

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

以上执行结果：

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

• ROW_NUMBER函数排名是返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
• rank函数返回一个唯一的值，但是当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。比如数学成绩都是84分的两个人并列第二名，但是“张三丰”同学就是直接是第四名。
• 我们经常会利用row_number函数的排名机制（排名的唯一性）来过滤重复数据，即按照某一个特定的排序条件，通过获取排名为1的数据来获取重复数据当中最新的数据值。