ROW_NUMBER SQL 函数：如何显示行号

在 SQL 中，数据集常常是无序的，这会给分析带来挑战。为了理解数据集中各行之间的关系，我们可以使用 ROW_NUMBER() 函数。

该函数会为结果集中的行分配连续编号，从而提供清晰的顺序，便于后续处理和分析。您可以对整个数据集进行编号，也可以对数据集中的不同分组分别编号。

本文假设您已了解SQL 基础。我们将讲解常用的 ROW_NUMBER() 函数的基础知识，并提供难度逐步提升的示例。

ROW_NUMBER() 语法

以下是 ROW_NUMBER() 函数的基本语法：

ROW_NUMBER() OVER([PARTITION BY value expression, ... ] [ORDER BY order_by_clause])技术支持

下面拆解关键组成部分：

• ROW_NUMBER()：函数本体，用于生成连续的行号。
• OVER (...)：窗口函数（如 ROW_NUMBER()）必需的子句，用于定义计算行号的上下文。
• PARTITION BY value_expression：可选子句，按照指定列或表达式将结果集划分为多个分区。行号会在每个分区内分别计算。
• ORDER BY order_by_clause：可选子句，指定在每个分区内（若未使用 PARTITION BY 则为整个结果集）分配行号的顺序。

举例来说，下面展示了在更大的 SQL 查询中使用 ROW_NUMBER() 的方式：

SELECT Val_1, 
    ROW_NUMBER() OVER(PARTITION BY group_1, ORDER BY number DESC) AS rn
FROM Data;技术支持

ROW_NUMBER() 示例

接下来的三个示例将使用免费的 DataLab IDE。我们会用到已内置于 DataLab 的 Employees 示例数据集，其中包含以下四列：

• first_name：字符串字段
• last_name：字符串字段
• gender：字符串字段，取值为 “M” 或 “F”
• hire_date：员工入职日期

可以使用如下 SQL 代码查询该数据集：

SELECT e.first_name, e.last_name, e.gender, e.hire_date
    FROM employees.employees e
LIMIT 100; -- Optionally reduce the size of the output技术支持

按字母顺序为员工编号

在使用 ROW_NUMBER() 前，明确目标非常重要——这将帮助我们确定是否以及如何进行分区和排序。在本例中，我们希望按字母顺序排列所有员工。因为要对整个数据集中的所有员工排序，所以不需要 PARTITION BY 子句。我们将按照姓氏（last_name）为员工排序，并将编号列命名为 name_row_number。

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(ORDER BY e.last_name) AS name_row_number    
FROM employees.employees e;技术支持

为处理并列（即具有相同姓氏的员工），可以通过添加更多列来细化排序。下面的示例先按 last_name 排序，然后当多个员工的姓氏相同时，再按名字（first_name）排序。

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(ORDER BY e.last_name, e.first_name) AS name_row_number    
FROM employees.employees e;技术支持

在性别分组内按最新入职日期为员工编号

现在，我们希望在各自的性别分组内，按入职日期从新到旧为员工排序。我们仍将使用 ORDER BY 子句按 hire_date 排序，但这次使用降序（DESC）以优先显示最新入职的员工。

为了在每个性别组内分别编号，我们将加入 PARTITION BY gender 子句。这意味着每个不同的性别组的行号都会从 1 重新开始。

完整查询如下：

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(PARTITION BY gender ORDER BY hire_date DESC) AS hire_row_number    
FROM employees.employees e;技术支持

随后，我们可以使用 WHERE 子句查询该结果，以找出每个性别中最“新”的员工：

WITH RankedEmployees AS (
    SELECT e.first_name, e.last_name, e.gender, e.hire_date,
        ROW_NUMBER() OVER(PARTITION BY gender ORDER BY hire_date DESC) AS hire_row_number
    FROM employees.employees e
)
SELECT first_name, last_name, gender, hire_date
FROM RankedEmployees
WHERE hire_row_number = 1;技术支持

通过 JOIN 在性别分组内按薪资为员工排序

最后一个示例中，我们将结合性别对员工按薪资进行排名。为此，我们基于 emp_no 列将 employees 表与 salaries 表进行连接：

SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary
    FROM employees.employees e
JOIN employees.salaries s ON e.emp_no = s.emp_no
LIMIT 100;技术支持

接下来，我们将同时使用 PARTITION BY 和 ORDER BY。通过按 gender 分区，可在每个性别组内分别排名；通过按 salary 降序排序，则可使高薪者排在前列。

完整查询如下：

SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary,
    ROW_NUMBER () OVER(PARTITION BY e.gender ORDER BY s.salary DESC) AS salary_row_number
    FROM employees.employees e
JOIN employees.salaries s ON e.emp_no = s.emp_no
LIMIT 100;技术支持

为了比较各性别组的最高薪资，我们可以使用 WHERE 子句对结果进行筛选。下面的查询将返回每个性别组中薪资排名前 5 的员工，并按其在各自性别组内的排名排序。此类查询有助于从数据集中洞察薪酬公平性。

WITH RankedSalaries AS (
    SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary,
        ROW_NUMBER() OVER(PARTITION BY e.gender ORDER BY s.salary DESC) AS salary_row_number
    FROM employees.employees e
    JOIN employees.salaries s ON e.emp_no = s.emp_no
)
SELECT first_name, last_name, gender, hire_date, salary
FROM RankedSalaries
WHERE salary_row_number <= 5
ORDER BY salary_row_number, gender;技术支持

结论

ROW_NUMBER() 函数适用于当我们面对无序数据集、并希望为进一步分析而为各行分配清晰的连续编号的场景。通过 ORDER BY 定义编号的具体顺序，通过 PARTITION BY 为数据中的不同分组分别建立独立的编号序列。

如果您觉得本文有帮助并希望进一步学习 SQL，欢迎查看我们的其他SQL 课程。