PostgreSQL 中窗口函数 row_number rank dense_rank

窗口函数介绍

在 PostgreSQL 中，窗口函数（Window Function）是一种高级分析工具，它能让你在保留原始行数据的同时，像 GROUP BY 一样进行分组计算。与普通聚合函数不同，窗口函数不会将多行合并成一行输出，而是为每一行计算一个值，这个值通常基于与当前行相关的一组行（称为“窗口帧”）。

窗口函数的基本语法如下：

函数名() OVER (
    [PARTITION BY 列名,...] 
    [ORDER BY 列名 [ASC|DESC],...]
)

• PARTITION BY：可选，将数据划分为不同的分区，函数在每个分区内独立计算。
• ORDER BY：在需要排序的窗口函数中（如排名函数）必须指定，它定义了分区内行的排列顺序。

本教程将重点介绍三个最常用的排名型窗口函数：ROW_NUMBER、RANK 和 DENSE_RANK，并通过示例对比它们的差异，帮助你快速掌握实际用法。

准备示例数据

我们以一张简单的员工薪资表为例，表中记录了员工姓名、部门和月薪。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(20),
    department VARCHAR(20),
    salary NUMERIC(10,2)
);

INSERT INTO employees (name, department, salary) VALUES
('Alice',   'Engineering', 9000),
('Bob',     'Engineering', 8500),
('Charlie', 'Engineering', 9000),
('David',   'Marketing',   7200),
('Eve',     'Marketing',   6800),
('Frank',   'Marketing',   7200),
('Grace',   'Sales',       7500),
('Hank',    'Sales',       7500);

现在我们有 8 条记录，三个部门。其中 Engineering 部门有两人薪资同为 9000，Marketing 有两人薪资同为 7200，Sales 两人薪资相同。

ROW_NUMBER：简单行号

ROW_NUMBER() 为分区内的每一行分配一个唯一的、连续的整数，从 1 开始，没有间隙，不会因并列值而重复。即使两行的排序字段完全相同，它也会按某种内部顺序（通常为物理存储顺序，但不保证）赋予不同编号。

示例：按薪资降序为所有员工标上行号

SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

结果（可能如下）

注意：Alice 和 Charlie 薪资相同，但 row_num 分配了 1 和 2（具体谁排第一取决于内部顺序，通常与插入顺序有关）。如果你想确保顺序可预测，可以在 ORDER BY 中添加第二个字段（例如 ORDER BY salary DESC, name ASC）。

结合 PARTITION BY 使用

按部门分别对员工薪资排名：

SELECT 
    department,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

此时每个部门内部独立编号，行号都从 1 开始。

RANK：有间隙的排名

RANK() 函数为分区内的行分配排名，同样基于 ORDER BY 子句定义的顺序。当遇到相同值时，它会给出相同的排名，但接下来的排名会跳过后续的数字，产生间隙。例如，排名值可能出现 1, 1, 3, 4... 这样的情况。

示例：按薪资降序获取全局排名

SELECT 
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

结果

可以看到，两个 9000 并列第 1，下一个薪资 8500 直接跳到第 3（跳过了 2）。两个 7500 并列第 4，下一个 7200 排在第 6（跳过了 5）。这种排名方式符合现实中的竞赛排名逻辑（如奥运会金牌数并列）。

DENSE_RANK：无间隙的排名

DENSE_RANK() 函数与 RANK() 类似，对相同的值赋予相同的排名，但最大的区别在于它不会跳过任何排名序号，排名是连续的。如果出现并列，下一个不同值的排名紧跟着当前排名后一位。

示例：按薪资降序获取全局稠密排名

SELECT 
    name,
    department,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM employees;

结果

并列第一之后，8500 直接排名第 2；两个 7500 排名第 3；两个 7200 排名第 4，最后 6800 排名第 5。排名数字紧密连续，没有跳跃。

三者的直观对比

为了更清晰地看出差异，我们可以将三个函数放在同一个查询中，按全局薪资降序排列：

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

输出对比（重点看薪资 9000 和 7500 的行）

总结差异：

• ROW_NUMBER：纯连续编号，不理会重复值；每个行号唯一。
• RANK：相同值获得相同排名，之后产生空缺（间隙），下一排名 = 当前行号。
• DENSE_RANK：相同值获得相同排名，之后连续不中断，下一排名 = 当前排名 + 1。

常见应用场景

• 分页查询中的唯一行号：ROW_NUMBER() 最适合实现精确的分页效果，因为它不会产生重复值。例如配合 WHERE row_num BETWEEN 11 AND 20 获取第二页数据。
• Top‑N 分析：利用 ROW_NUMBER 或 RANK 可以轻松找出每组的前几名。例如，每个部门薪资最高的 2 名员工：

  SELECT * FROM (
      SELECT *,
          RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
      FROM employees
  ) sub WHERE rnk <= 2;
  

  若使用 RANK，当有并列第二时会多带出隐藏行；若用 ROW_NUMBER 则严格限定数量，但会随机舍弃并列行。
• 排名对比：DENSE_RANK 常用于需要连续排名的报表，如学生成绩排名（第一名并列，下一名仍是第二名）。
• 数据去重辅助：结合 ROW_NUMBER 和 PARTITION BY 可以为重复数据组分配 rn=1，从而保留一行并删除其他重复项。

进阶提示

1. ORDER BY 子句的重要性：这三个函数必须配合 OVER() 中的 ORDER BY 使用（除非只需要一个常量编号）。没有 ORDER BY 时，所有行被视为平级，RANK 和 DENSE_RANK 都返回 1，ROW_NUMBER 则返回不确定的编号。
2. 多个排序字段：可在 ORDER BY 中指定多个列，例如 ORDER BY salary DESC, name ASC，确保在值相同时有确定的二级排序，避免 ROW_NUMBER 结果不确定。
3. 性能考虑：窗口函数需要排序操作，在大数据量上使用请注意添加合适的索引，尤其是 PARTITION BY 和 ORDER BY 涉及的字段。
4. PostgreSQL 版本支持：这些窗口函数从 PostgreSQL 8.4 开始就已完整支持，你可以在任何现代版本中放心使用。

通过以上对比和示例，你应该能够根据实际业务需求在 ROW_NUMBER、RANK 和 DENSE_RANK 之间做出正确选择。动手在 PostgreSQL 中运行这些查询，观察数据变化，会帮助你更快地掌握窗口函数的核心思想。