浅谈Oracle中的row_number()、rank()、dense_rank()

1. Oracle分析函数介绍

分析函数是一种特殊类型的SQL函数，通常用于执行基于排序和聚合的计算，并可用于在单个查询的结果集中为每个记录计算统计信息。

Oracle中有很多种类型的分析函数，在这篇文章中我们将聚焦于row_number()、rank()和dense_rank()，这些函数通常用于计算排名或对结果集进行排序。接下来我们来一一介绍它们的用法和区别。

2. row_number()

row_number()函数返回一个数字，该数字表示结果集中的每一行的排序位置。通常在需要对结果集进行排序并将每行与其位置相关联的情况下使用该函数。

2.1 row_number()函数的语法：

SELECT column1, column2, ..., column_n, 

    ROW_NUMBER() OVER (
        [PARTITION BY partition_expression, ... ]
        ORDER BY sort_expression [ASC|DESC], ... 
    ) row_number
FROM table_name;

其中：

partition_expression：可选项，定义分区的字段或表达式。

sort_expression：必需项，定义排序字段或表达式。

ASC：表示按升序排序（默认值）。

DESC：表示按降序排序。

2.2 row_number()函数的示例：

假设我们有一个销售订单表，包含以下字段：

我们可以使用row_number()函数在查询结果集中计算每个销售订单的排名：

SELECT order_id, product_name, order_date, sales_amount, 

    ROW_NUMBER() OVER (
        ORDER BY sales_amount DESC
    ) row_number
FROM sales_orders;

上面的查询将返回以下结果：

注意：分析函数必须位于SELECT语句中的FROM子句之后。

3. rank()

rank()函数是另一种用于对结果集进行排名的分析函数。rank()函数确定相同值的排名，并跳过后续排名。例如，如果有两个值的排名为3，则下一个值的排名将为5（而不是4）。

3.1 rank()函数的语法：

SELECT column1, column2, ..., column_n, 

    RANK() OVER (
        [PARTITION BY partition_expression, ... ]
        ORDER BY sort_expression [ASC|DESC], ... 
    ) rank
FROM table_name;

3.2 rank()函数的示例：

假设我们继续使用上面的销售订单表，我们可以使用rank()函数计算每个销售订单的排名：

SELECT order_id, product_name, order_date, sales_amount, 

    RANK() OVER (
        ORDER BY sales_amount DESC
    ) rank
FROM sales_orders;

上面的查询将返回以下结果：

区别于row_number()函数的是，如果存在值相同的行，rank()将为它们分配相同的排名，并跳过后续排名。

4. dense_rank()

dense_rank()函数与rank()函数非常相似，但不会跳过后续排名，而是为它们分配连续的排名。例如，如果有两个值的排名为3，则下一个值的排名将为4（而不是5）。

4.1 dense_rank()函数的语法：

SELECT column1, column2, ..., column_n, 

    DENSE_RANK() OVER (
        [PARTITION BY partition_expression, ... ]
        ORDER BY sort_expression [ASC|DESC], ... 
    ) dense_rank
FROM table_name;

4.2 dense_rank()函数的示例：

假设我们继续使用上面的销售订单表，我们可以使用dense_rank()函数计算每个销售订单的排名：

SELECT order_id, product_name, order_date, sales_amount, 

    DENSE_RANK() OVER (
        ORDER BY sales_amount DESC
    ) dense_rank
FROM sales_orders;

上面的查询将返回以下结果：

相比于rank()函数的区别在于，即使存在值相同的行，dense_rank()也将为它们分配连续的排名。

5. 总结

在Oracle中，row_number()、rank()和dense_rank()是常用的分析函数。这些函数可以帮助我们计算排名或对结果集进行排序。row_number()函数用于分配唯一的排名，rank()函数用于确定相同值的排名，并跳过后续排名，而dense_rank()函数用于确定相同值的排名，并为它们分配连续的排名。需要根据具体的需求选择合适的函数使用。