使用方法举例
---创建测试表
SQL> desc t_test;
名称 是否为空? 类型
----------------------------------------- -------- --------------------------
T_ID NUMBER
T_GROUP NUMBER
T_NUM NUMBER
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函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。