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

最新推荐文章于 2022-04-24 14:33:38 发布

原创最新推荐文章于 2022-04-24 14:33:38 发布 · 890 阅读

0 ·

CC 4.0 BY-SA版权

Oracle Function 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了SQL中的三种排名函数RANK(), DENSE_RANK() 和 ROW_NUMBER() 的使用方法及区别。通过实例展示了如何利用这些函数进行数据分析和报表制作，特别强调了它们在处理重复数据时的不同行为。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用方法举例

---创建测试表

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行。