用实例说话：ROW_NUMBER() 在 Oracle 分析函数中的灵活应用

1. ROW_NUMBER() 函数与 OVER() 的使用

1.1 什么是 ROW_NUMBER() 函数？

ROW_NUMBER() 是 Oracle 中的一个分析函数，用于为结果集中的每一行分配唯一的序号。这个序号根据指定的排序规则递增，可以通过 OVER() 子句中的 ORDER BY 子句来指定排序条件。与 Oracle 的 ROWNUM 类似，但 ROW_NUMBER() 提供了更多的灵活性，例如可以在每个分区内重新开始编号。

1.2 ROW_NUMBER() 与 OVER() 的基本语法

ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)

• PARTITION BY：可选项，用于指定分组依据。每个分区内的行会从 1 开始编号。
• ORDER BY：必选项，用于指定在每个分区内的排序顺序。该排序决定了 ROW_NUMBER() 的编号顺序。

2. 示例：ROW_NUMBER() 在实际中的应用

为了更好地理解 ROW_NUMBER() 的使用，以下是一个创建表并插入数据的示例。假设我们有一个名为 test_7 的表，包含以下数据：

DROP TABLE test_7 CASCADE CONSTRAINTS PURGE;

CREATE TABLE test_7 (
    type VARCHAR2(5),
    qty  VARCHAR2(5)
);

INSERT INTO test_7 VALUES('1', '6');
INSERT INTO test_7 VALUES('2', '9');
INSERT INTO test_7 VALUES('3', '5');
INSERT INTO test_7 VALUES('1', '6');
INSERT INTO test_7 VALUES('1', '7');
INSERT INTO test_7 VALUES('2', '7');
INSERT INTO test_7 VALUES('3', '7');
INSERT INTO test_7 VALUES('1', '9');

2.1 使用 ROW_NUMBER() 对数据进行编号

假设我们希望为每个 type 分区内的数据按 qty 的大小进行排序，并为每一行分配一个递增的序号。SQL 语句如下：

SELECT 
    type,
    qty,
    TO_CHAR(ROW_NUMBER() OVER (PARTITION BY type ORDER BY qty)) || '/' || 
    TO_CHAR(COUNT(*) OVER (PARTITION BY type)) AS cnt2
FROM 
    test_7;

• ROW_NUMBER() OVER (PARTITION BY type ORDER BY qty): 为每个 type 分区内的数据根据 qty 列的排序结果生成递增的序号。例如，对于 type 值为 1 的行，按 qty 排序后，如果 qty 有 6、7、9 三个值，则编号依次为 1、2、3。
• COUNT(*) OVER (PARTITION BY type): 统计每个 type 分区内的行数。输出结果为序号和总行数的组合，形如 1/3, 2/3, 3/3 等。

2.2 运行结果

test_7 表的数据如下：

TYPE  QTY
----  ----
1     6
1     7
1     9
2     9
2     7
3     5
3     7
3     7

运行上述 SQL 语句后，结果可能如下：

TYPE  QTY  CNT2
----  ---- ------
1     6    1/3
1     7    2/3
1     9    3/3
2     7    1/2
2     9    2/2
3     5    1/3
3     7    2/3
3     7    3/3

在这个结果中，每个 type 分区内的行按 qty 排序，ROW_NUMBER() 对每行数据分配一个递增的序号，而 COUNT(*) 则统计了每个分区内的总行数。

3. ROW_NUMBER() 与其他排名函数的对比

3.1 RANK() 函数

• 功能：RANK() 返回分区内数据的排名值。如果遇到相同的值，它们将获得相同的排名，但接下来的排名将跳过。例如，如果有两个并列第二名，下一名将是第四名。
• 示例：

  SELECT 
      type, 
      qty, 
      RANK() OVER (PARTITION BY type ORDER BY qty) AS rank_value
  FROM 
      test_7;
  

3.2 DENSE_RANK() 函数

• 功能：DENSE_RANK() 与 RANK() 类似，但不同之处在于，如果有两个并列第二名，下一名将是第三名，而不是第四名。
• 示例：

  SELECT 
      type, 
      qty, 
      DENSE_RANK() OVER (PARTITION BY type ORDER BY qty) AS dense_rank_value
  FROM 
      test_7;
  

3.3 ROW_NUMBER() 函数

• 功能：ROW_NUMBER() 返回分区内数据的唯一序号，不考虑数据是否相同。即使值相同，编号也会递增。

通过这些函数，可以灵活地为分区内的数据进行排序和编号，满足不同的业务需求。

4. ROW_NUMBER() 的实战应用

假设我们在一个员工表 emp 中想要统计每个部门（deptno）的员工薪资排名，同时为薪资相同的员工保留相同的排名次序：

SELECT 
    empno, 
    deptno, 
    sal,
    RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank_value,
    DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dense_rank_value,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_number_value
FROM 
    emp;

• RANK() 会跳过排名。例如，如果有两个员工的薪资并列第二，下一名员工的排名将为第四。
• DENSE_RANK() 不会跳过排名。如果有两个员工的薪资并列第二，下一名员工的排名将为第三。
• ROW_NUMBER() 无论薪资是否相同，都会给每个员工分配唯一的编号。

ROW_NUMBER() 是 Oracle 中一个功能强大的分析函数，尤其在与 OVER() 子句结合使用时，可以灵活地为结果集中的每一行生成唯一编号。这种编号方式在排序和分区处理数据时尤为有用。通过与其他排名函数的对比，你可以选择最适合你业务需求的排名方式。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。
​
​

​
学习成功人士的经验，提供全面的学习资源和社群支持，多种副业选择，总有一个适合你。
​