语法

RANK() OVER([query_partition_clause] order_by_clause)

DENSE_RANK() OVER([query_partition_clause] order_by_clause)

功能

聚合函数 RANK 和 DENSE_RANK 主要的功能是计算一组数值中的排序值。

在 Oracle 9i 版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于 order_by_clause 子句中的 value_exprs 指定字段的。其语法为：

RANK() OVER([query_partition_clause] order_by_clause)

在 Oracle 9i 版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和 ORDER BY 子句中的字段个数、位置、类型完全一致。其语法为：

RANK(expr [, expr]...) WITHIN GROUP
(ORDER BY expr [ DESC | ASC ] [NULLS { FIRST | LAST }] [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)

参数

dense_rank 与 rank() 用法相当

区别

• DENSE_RANK 在并列关系中，相关等级则不会跳过。而 RANK 则会跳过。

• RANK 是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

• DENSE_RANK 是连续排序，有两个第二名时仍然跟着第三名

示例

（1）创建一个 t_table 表格，预先插入一些数，然后使用 RANK 函数列出 col2 分组后根据 col1 排序，并生成数字列。比较实用于在成绩表中查出各科前几名的信息。如下：

-- 创建表格
SQL> create table t_table(col1 int, col2 int);

表已创建。

-- 插入数据
insert into t_table(col1,col2) values(1,1);
insert into t_table(col1,col2) values(2,1);
insert into t_table(col1,col2) values(3,2);
insert into t_table(col1,col2) values(3,1);
insert into t_table(col1,col2) values(4,1);
insert into t_table(col1,col2) values(4,2);
insert into t_table(col1,col2) values(5,2);
insert into t_table(col1,col2) values(5,2);
insert into t_table(col1,col2) values(6,2);

-- 分析功能：使用 RANK 函数列出 col2 分组后根据 col1 排序，并生成数字列
SQL> SELECT a.*, RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM t_table a;

      COL1       COL2       Rank
---------- ---------- ----------
         1          1          1
         2          1          2
         3          1          3
         4          1          4
         3          2          1
         4          2          2
         5          2          3
         5          2          3
         6          2          5

已选择9行。


-- 合计功能：计算出数值 (4,1) 在 Orade By Col1,Col2 排序下的排序值，也就是 col1=4, col2=1 在排序以后的位置

SQL> SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM t_table;

      Rank
----------
         7

（2）创建一张学生成绩表 t_table2，然后插入成绩数据。最后使用 RANK 函数找出每门科目的前3名的分数。如下：

-- 创建表格
SQL> create table t_table2(subject varchar(10), score int);

表已创建。

-- 插入如下数据
insert into t_table2(subject, score) values('数学', 80);
insert into t_table2(subject, score) values('语文', 70);
insert into t_table2(subject, score) values('数学', 90);
insert into t_table2(subject, score) values('数学', 60);
insert into t_table2(subject, score) values('数学', 100);
insert into t_table2(subject, score) values('语文', 88);
insert into t_table2(subject, score) values('语文', 65);
insert into t_table2(subject, score) values('语文', 77);

-- 分析功能
SQL> select * from (select rank() over(partition by subject order by score desc) rk, a.* from t_table2 a) t where t.rk <= 3;

        RK SUBJECT         SCORE
---------- ---------- ----------
         1 数学              100
         2 数学               90
         3 数学               80
         1 语文               88
         2 语文               77
         3 语文               70

已选择6行。

（3）dense_rank 与 rank 用法相当，但是有一个区别：dence_rank 在并列关系是，相关等级不会跳过，rank 则跳过。例如：

-- 创建表
SQL> create table t_table3(A varchar(2), B varchar(10), C varchar(10));

表已创建。

-- 插入数据
insert into t_table3(A,B,C) values('a', 'liu', 'wang');
insert into t_table3(A,B,C) values('a', 'jin', 'shu');
insert into t_table3(A,B,C) values('a', 'cai', 'kai');
insert into t_table3(A,B,C) values('b', 'yang', 'du');
insert into t_table3(A,B,C) values('b', 'lin', 'ying');
insert into t_table3(A,B,C) values('b', 'yao', 'cai');
insert into t_table3(A,B,C) values('b', 'yang', '99');

-- 当 rank 时为
SQL> select m.a,m.b,m.c,rank() over(partition by a order by b) liu from t_table3 m;

A  B          C                 LIU
-- ---------- ---------- ----------
a  cai        kai                 1
a  jin        shu                 2
a  liu        wang                3
b  lin        ying                1
b  yang       99                  2
b  yang       du                  2
b  yao        cai                 4

已选择7行。

-- 如果用 dense_rank 时为
SQL> select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from t_table3 m;

A  B          C                 LIU
-- ---------- ---------- ----------
a  cai        kai                 1
a  jin        shu                 2
a  liu        wang                3
b  lin        ying                1
b  yang       99                  2
b  yang       du                  2
b  yao        cai                 3

已选择7行。