Oracle排序分析函数

Oracle自拓展SQL功能中,分析函数(Analytical Function)是非常强大的工具。

本篇我们介绍几个Oracle典型的排序分析函数,来帮助我们解决实际问题。

1、从rownum谈起

 

 

选择Oracle 11gR2进行测试

Oracle排序分析函数

 

基础表EMP,并对字段 sal 排序

Oracle排序分析函数

常见的功能需求,是先用order by排序,之后用rownum从1开始标记。但是,rownum往往不会像我们希望的如此工作。

Oracle排序分析函数

最后的数据集合,的确是按照我们希望的sal排序动作结果。但是rownum并没有按照我们希望的出现排序"序号"作用。

这个问题的根源是Oracle rownum的机理。rownum并不是一个真实存在的数据列,而是一个随数据集生成而生成的伪数据列。rownum是oracle系统顺序分配为从查询返回的行的编号,oracle在select出一条记录后便加上一个rownum,而不等所有的结果都select出来后再加上rownum。

上面的结果,正是Oracle首先生成了rownum数据列,之后再按照sal进行排序后的结果。所以,rownum并不能像我们想象的那样处理。

 

一些方法可以使用在这个问题上,主要是嵌套子查询方法,让我们可以使用ronwum来解决这个问题。

Oracle排序分析函数

 

结果正确,不过在官方文档中,Oracle推荐使用分析函数来解决序号问题,根据不同的实际需求,可以使用row_number()rank()dense_rank()几个选择。

row_number() 根据order by顺序排出一个名次,不会出现并列排名 1 2 3 4 5

rank() 根据order by排序,会出现并列排名,下一个值会跳过并列值 比如 1 2 2 4 5

desc_rank() 根据order by排序,出现并列排名以后,下一个值不跳过并列值 1 2 2 3 4

2、分析函数ROW_NUMBER()、RANK()、DENSE_RANK()

 

 

 

分析函数最大的一个功能是可以使用partition可选参数,用来在其中分组,这三个分析函数都可以在各个分组内从1开始排序。

 

ROW_NUMBER()函数是一个单纯的序号生成器,序号没有重复值,当结果有相同的排序取值时,相同值有不同的排序,可以利用它实现分页显示。

DENSE_RANK()是连续排序,当结果有相同的排序取值时,相同值有相同的排序,并且序号继续下去。

 

RANK()是跳跃排序,当结果有相同的排序取值时,相同值有相同的排序,并且序号跳跃继续下去,有两个第二名时接下来就是第四名(同样是在各个分组内)。

 

语法:ROW_NUMBER()|RANK()|DENSE_RANK() OVER(PARTITION BY COLUMN ORDER BY COLUMN ASC|DESC)

Oracle排序分析函数

Oracle排序分析函数,布布扣,bubuko.com

Oracle排序分析函数

上一篇:关于一条sql条件查询+排序


下一篇:MySQL学习之索引(一)