ORACLE分析函数row_number、rank、dense_rank的区别和使用

一、数据准备

现有每月工资发放记录表 t_salary(mon, name, salary)，三个字段分别为月份、姓名、工资。

select * from t_salary;

二、row_number函数

    语法：row_number() over( partition by 分组字段 order by 排序字段 asc|desc)

1、它用来根据分组字段进行分组，再在分组内使用排序字段对分组记录排序，此函数计算的结果就是每组内记录排序后的序列值。
2、在排序时碰到相同值的数据时，序列值按照记录集中记录的顺序依次递增。
3、“ partition by 分组字段 ” 可以不写，此时记录集中所有记录为一组。
例：
  使用row_number函数查询工资发放表中每个月内各个员工的工资排名，首先需要根据月份分组，然后再在分组内对工资降序排序，得到的序列值即工资排名（注意每组内相同工资记录的序列值是由记录集中记录的初始顺序决定的）

select 
  mon,
  name,
  salary,
  row_number() over(partition by mon order by salary desc) pm
from t_salary

三、rank函数

    语法：rank() over( partition by 分组字段 order by 排序字段 asc|desc)

1、rank函数与row_number函数作用类似。
2、与row_number函数的不同点在于：对于值相同的排序字段，计算得出的序列值是一样的，且序列值是不连续的。比如两个记录排名并列第一，计算得出的序列值依次为1、1，则排名第二的记录计算的出的序列值只能是3。
例：
  使用rank函数查询工资发放表中每个月内各个员工的工资排名，首先需要根据月份分组，然后再在分组内对工资降序排序，得到的序列值即工资排名

select 
  mon,
  name,
  salary,
  rank() over(partition by mon order by salary desc) pm
from t_salary

四、dense_rank函数

    语法：dense_rank() over( partition by 分组字段 order by 排序字段 asc|desc)

1、dense_rank函数与row_number函数作用也类似。
2、与row_number函数的不同点在于：对于值相同的排序字段，计算得出的序列值是一样的，且序列值是连续的。比如两个记录排名并列第一，计算得出的序列值依次为1、1，则排名第二的记录计算的出的序列值只能是2。
例：
  使用dense_rank函数查询工资发放表中每个月内各个员工的工资排名，首先需要根据月份分组，然后再在分组内对工资降序排序，得到的序列值即工资排名

select 
  mon,
  name,
  salary,
  dense_rank() over(partition by mon order by salary desc) pm
from t_salary

  综上所述，实际使用中，我们需要根据具体情况使用不同的函数获取排名编号值。rank函数、dense_rank函数之间的差别在于序列值连不连续，而这两者与row_number函数的差别在于对于排序字段值相同的记录给出的序列值相不相同。