Oracle排名函数

Oracle 排名函数详解

在Oracle数据库中，排名函数（Ranking Functions）用于对数据进行排序并生成一个唯一的序号或排名。这些函数在处理需要排名的数据时非常有用，例如销售排名、学生成绩排名等。Oracle提供了几种不同的排名函数，包括 ROW_NUMBER(), RANK(), 和 DENSE_RANK()。下面将详细解释每个函数的用法和区别。

1. ROW_NUMBER()

ROW_NUMBER() 函数为结果集中的每一行分配一个唯一的连续整数。即使两行或多行的值相同，它们也会被赋予不同的序号。

语法：

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

• PARTITION BY: 可选子句，用于将数据划分为分区，并在每个分区内独立应用排名。
• ORDER BY: 必选子句，指定用于排序的列。

示例： 假设有一个名为 employees 的表，包含以下数据：

employee_id | department_id | salary ------------|---------------|------- 1 | 10 | 5000 2 | 10 | 6000 3 | 20 | 5000 4 | 20 | 7000

使用 ROW_NUMBER() 对每个部门的员工按工资进行排名：

SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees;

结果：

employee_id | department_id | salary | row_num ------------|---------------|--------|--------- 2 | 10 | 6000 | 1 1 | 10 | 5000 | 2 4 | 20 | 7000 | 1 3 | 20 | 5000 | 2

2. RANK()

RANK() 函数为结果集中的每一行分配一个排名，如果两行或多行的值相同，则它们会被赋予相同的排名，并且后续行的排名会跳过相应的数量。

语法：

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

示例： 继续使用上面的 employees 表，使用 RANK() 对每个部门的员工按工资进行排名：

SELECT employee_id, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_num FROM employees;

结果：

employee_id | department_id | salary | rank_num ------------|---------------|--------|---------- 2 | 10 | 6000 | 1 1 | 10 | 5000 | 2 4 | 20 | 7000 | 1 3 | 20 | 5000 | 2

如果有第三名员工的工资也是5000，那么他的排名会是3，但接下来的排名会跳到4。

3. DENSE_RANK()

DENSE_RANK() 函数与 RANK() 类似，但它不会跳过后续的排名数字。即使有重复的值，后续行的排名也会紧接着前一个排名。

语法：

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

示例： 继续使用上面的 employees 表，使用 DENSE_RANK() 对每个部门的员工按工资进行排名：

SELECT employee_id, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank_num FROM employees;

结果：

employee_id | department_id | salary | dense_rank_num ------------|---------------|--------|---------------- 2 | 10 | 6000 | 1 1 | 10 | 5000 | 2 4 | 20 | 7000 | 1 3 | 20 | 5000 | 2

如果有第三名员工的工资也是5000，那么他的排名会是2，但下一个排名仍然是3而不是4。

总结

• ROW_NUMBER(): 为每一行分配一个唯一的连续整数。
• RANK(): 为每一行分配一个排名，如果有重复值，则跳过相应数量的后续排名。
• DENSE_RANK(): 为每一行分配一个排名，即使有重复值，也不会跳过后续排名。

选择哪种排名函数取决于具体的需求和数据特点。希望这篇文档能帮助你更好地理解和使用Oracle中的排名函数。