Oracle中 ROW_NUMBER()的语法及在对应不同需求下应如何使用

Oracle数据库中的ROW_NUMBER()函数是一个窗口函数，它为查询结果集中的每一行分配一个唯一的序号。这个函数在数据分析、分页查询、数据去重和排名问题等方面非常有用。ROW_NUMBER()函数的语法如下：

ROW_NUMBER() OVER ( [ PARTITION BY column ] ORDER BY column [ ASC | DESC ] )

参数说明：

• PARTITION BY column：可选参数，用于将结果集分为多个分区（组），每个分区内部单独排序和编号。
• ORDER BY column [ ASC | DESC ]：必需参数，用于指定分配行号时的排序顺序。ASC表示升序，DESC表示降序。

用法示例：

假设我们有一个名为employees的表，其中包含员工的姓名、部门和薪资信息。我们想要为每个部门的员工按薪资排序并分配一个序号。

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

在这个例子中，ROW_NUMBER()函数会在每个部门内部根据薪资降序为员工分配一个序号。如果两个员工的薪资相同，他们会得到不同的序号，因为ROW_NUMBER()确保了每个序号是唯一的。

实际应用：

ROW_NUMBER()函数常用于各种场景，比如：

• 分页查询：获取每个部门薪资最高的前三名员工。
• 数据去重：与PARTITION BY结合使用，为每个分区的重复数据分配序号，然后只选择序号为1的行。
• 排名问题：为每个部门或产品类别生成一个排名列表。

注意事项：

• ROW_NUMBER()分配的序号可能会在分区内发生变化，因为它是基于当前分区的排序结果。
• 如果没有指定PARTITION BY，则整个结果集被视为一个单一分区。
• ROW_NUMBER()的结果是在查询执行期间生成的，因此它不会持久化存储在数据库中。
  ROW_NUMBER()是Oracle中非常强大和灵活的函数，通过与其他SQL功能和子查询结合使用，可以解决各种复杂的数据分析问题。以下是一些示例，展示如何将ROW_NUMBER()与其他功能结合使用：

1. 分页查询

在Oracle中，可以使用ROW_NUMBER()来实现分页查询，类似于MySQL中的LIMIT和OFFSET。例如，获取员工表中薪资排名第四到第六的员工信息：

SELECT *
FROM (
  SELECT
    employee_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM
    employees
) WHERE rn BETWEEN 4 AND 6;

2. 数据去重

使用ROW_NUMBER()与PARTITION BY可以去除重复数据。例如，如果想要获取每个部门薪资最高的员工：

SELECT *
FROM (
  SELECT
    department_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM
    employees
) WHERE rn = 1;

3. 窗口函数的链式使用

可以将ROW_NUMBER()与其他窗口函数结合使用。例如，计算每个员工在其部门内的薪资排名和薪资百分比：

SELECT
  department_id,
  employee_name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
  RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS salary_percentage
FROM
  employees;

4. 与聚合函数结合

ROW_NUMBER()也可以与聚合函数结合使用。例如，计算每个部门薪资最高的前两名员工的平均薪资：

SELECT
  department_id,
  AVG(salary) AS top_two_avg_salary
FROM (
  SELECT
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM
    employees
) WHERE rn <= 2
GROUP BY
  department_id;

5. 子查询中的ROW_NUMBER()

ROW_NUMBER()常用于子查询中，以便在外层查询中进一步处理。例如，获取每个部门薪资最高的员工，但只限于那些薪资超过平均薪资的部门：

SELECT
  department_id,
  employee_name,
  salary
FROM (
  SELECT
    department_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM
    employees
) WHERE rn = 1
AND department_id IN (
  SELECT
    department_id
  FROM
    employees
  GROUP BY
    department_id
  HAVING
    AVG(salary)