Oracle分析函数RANK()，DENSE_RANK()，ROW_NUMBER()的用法

使用方法举例

---创建测试表

SQL> desc t_test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- --------------------------

 T_ID                                                       NUMBER
 T_GROUP                                            NUMBER
 T_NUM                                                  NUMBER

SQL> select * from t_test;

      T_ID    T_GROUP      T_NUM
---------- ---------- ----------
        23          1       5500
        24          1       6600
        25          1       4900
        26          3       5800
        27          3       4700
        28          6       6900
        29          6       7800
        30          8       5900
        31          8       6000
        32          8       6000
        33          8       7000

已选择11行。

SQL> SELECT T_ID,T_GROUP,T_NUM,
  2    RANK() OVER(PARTITION BY T_GROUP ORDER BY T_NUM ASC) T_RANK,
  3    DENSE_RANK() OVER(PARTITION BY T_GROUP ORDER BY T_NUM ASC) T_DENSE_RANK,
  4    ROW_NUMBER() OVER(PARTITION BY T_GROUP ORDER BY T_NUM ASC) T_ROW_NUMBER
  5   FROM T_TEST T;

      T_ID    T_GROUP      T_NUM     T_RANK T_DENSE_RANK T_ROW_NUMBER
---------- ---------- ---------- ---------- ------------ ------------
        25          1       4900          1            1            1
        23          1       5500          2            2            2
        24          1       6600          3            3            3
        27          3       4700          1            1            1
        26          3       5800          2            2            2
        28          6       6900          1            1            1
        29          6       7800          2            2            2
        30          8       5900          1            1            1
        31          8       6000          2            2            2
        32          8       6000          2            2            3
        33          8       7000          4            3            4

已选择11行。

三种分析函数详解

RANK()

Aggregate Syntax

rank_aggregate::=

Analytic Syntax

rank_analytic::=

Purpose

RANK calculates the rank of a value in a group of values. The return type is NUMBER.

1.As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.

2.As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.

Aggregate Example

SQL> SELECT RANK(1, 6600) WITHIN GROUP(ORDER BY T_GROUP, T_NUM) AGG
  2    FROM T_TEST T;

       AGG
----------
         3

Analytic Example

SQL> SELECT T_ID,T_GROUP,T_NUM,
  2    RANK() OVER(PARTITION BY T_GROUP ORDER BY T_NUM ASC) T_RANK
  3   FROM T_TEST T;

      T_ID    T_GROUP      T_NUM     T_RANK
---------- ---------- ---------- ----------
        25          1       4900          1
        23          1       5500          2
        24          1       6600          3
        27          3       4700          1
        26          3       5800          2
        28          6       6900          1
        29          6       7800          2
        30          8       5900          1
        31          8       6000          2
        32          8       6000          2
        33          8       7000          4

已选择11行。

DENSE_RANK()

Aggregate Syntax

dense_rank_aggregate::=

Analytic Syntax

dense_rank_analytic::=

Purpose

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

This function accepts as arguments any numeric datatype and returns NUMBER.

    1.As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the order_by_clause of the aggregate match by position. Therefore, the number of arguments must be the same and types must be compatible.

    2.As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.

Aggregate Example

SQL> SELECT DENSE_RANK(1, 6600) WITHIN GROUP(ORDER BY T_GROUP, T_NUM) AGG
  2    FROM T_TEST T;

       AGG
----------
         3

Analytic Example

SQL> SELECT T_ID,T_GROUP,T_NUM,
  2    DENSE_RANK() OVER(PARTITION BY T_GROUP ORDER BY T_NUM ASC) T_DENSE_RANK
  3   FROM T_TEST T;

      T_ID    T_GROUP      T_NUM T_DENSE_RANK
---------- ---------- ---------- ------------
        25          1       4900            1
        23          1       5500            2
        24          1       6600            3
        27          3       4700            1
        26          3       5800            2
        28          6       6900            1
        29          6       7800            2
        30          8       5900            1
        31          8       6000            2
        32          8       6000            2
        33          8       7000            3

ROW_NUMBER()

Syntax

Purpose

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order.

You cannot nest analytic functions by using ROW_NUMBER or any other analytic function for expr. However, you can use other built-in function expressions for expr. Refer to "About SQL Expressions" for information on valid forms of expr.

Examples

SQL> SELECT T_ID,T_GROUP,T_NUM,
  2    ROW_NUMBER() OVER(PARTITION BY T_GROUP ORDER BY T_NUM ASC) T_ROW_NUMBER
  3   FROM T_TEST T;

      T_ID    T_GROUP      T_NUM T_ROW_NUMBER
---------- ---------- ---------- ------------
        25          1       4900            1
        23          1       5500            2
        24          1       6600            3
        27          3       4700            1
        26          3       5800            2
        28          6       6900            1
        29          6       7800            2
        30          8       5900            1
        31          8       6000            2
        32          8       6000            3
        33          8       7000            4

已选择11行。

SUMMARY

RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。