rank(),dense_rank(),row_number()分析函数用法和区别

使用情况：比如在一张buyer交易历史表中,要取出最新的三条记录。这时候需要先按seller分组，然后再按交易日期进行降序排列，最后加上要取出条数的条件。这时就可以根据不同的需求，通过这三个方法来实现。

举例说明：

select * from test1;

SQL> select * from test1;

NAME                      VALUE

-------------------- ----------

b                            67

a                           100

c                            88

d                             0

e                            77

f                            90

g                            80

h                            76

i                            10

j                           100

k                            68

11 rows selected.

name代表学生名称,value代表某科成绩。

---------------------------------------------

dense_rank()

查询该科成绩排第二的学生的姓名和成绩

select * from

(

select name,value,dense_rank() over (order by value desc) id from test1

) where id=2

NAME                      VALUE

-------------------- ----------

f                            90

查询该科成绩前5的学生的姓名和成绩

NAME                      VALUE         ID

-------------------- ---------- ----------

a                           100          1

j                           100          1

f                            90          2

c                            88          3

g                            80          4

e                            77          5

注意：如上，dense_rank()分析函数处理时，比如说有成绩相同的，排序号算一个，接下来的排序号不变。

--------------------------------

rank()

查询该科成绩排第二的学生的姓名和成绩

SQL> select * from

  2  (

  3  select name,value,rank() over (order by value desc) id from test1

  4  ) where id=2

  5  ;

no rows selected

结果为空。

我们再查询查询该科成绩前5的学生的姓名和成绩

SQL> select * from

  2  (

  3  select name,value,rank() over (order by value desc) id from test1

  4  ) where id<=5;

NAME                      VALUE         ID

-------------------- ---------- ----------

a                           100          1

j                           100          1

f                            90          3

c                            88          4

g                            80          5

注意：rank()分析函数处理时，如有成绩相同的，接下来的排序号将空缺，有n个并列的，接下来的号将跳跃n-1。

如：有3个100分，则接下来排序号将从4开始。

rank()与dense_rank()的区别就在这里

--------------------------------

row_number()

查询查询该科成绩前5的学生的姓名和成绩

SQL> select * from

  2  (

  3  select name,value,row_number() over (order by value desc) id from test1

  4  ) where id<=5

  5  ;

NAME                      VALUE         ID

-------------------- ---------- ----------

a                           100          1

j                           100          2

l                           100          3

f                            90          4

c                            88          5

可以看到，成绩并列的学生按照姓名排序，排序号并无相同，同时接下来的成绩的排序号紧接着上面的排序号，没有间隔。