使用 PARTITION BY 和 RANK/DENSE_RANK 查询部门内薪资 Top 2

Asthenian
9 阅读4分钟

使用 PARTITION BY 和 RANK/DENSE_RANK 查询部门内薪资 Top 2

在 SQL 查询中，获取每个分组内的前 N 条记录（如每个部门内薪资最高的员工）是一项常见需求。本文将讲解如何使用 PARTITION BY 结合 RANK() 或 DENSE_RANK() 窗口函数，查询部门内薪资排名前 2 的员工。

PARTITION BY 和 RANK()/DENSE_RANK() 详解

什么是 PARTITION BY？

PARTITION BY 是 SQL 窗口函数的一部分，用于将数据分成多个分区（类似于 GROUP BY，但不会聚合数据）。每个分区独立执行窗口函数的计算，保留原始行。

  • 作用：将数据按指定列分组，例如按 department_id 分区，每个部门的数据形成一个独立的分组。
  • 语法： 
    OVER (PARTITION BY column_name ORDER BY another_column)
    • PARTITION BY column_name：指定分区的列。
    • ORDER BY another_column：指定分区内的排序规则。

RANK() 和 DENSE_RANK()

RANK() 和 DENSE_RANK() 是窗口函数，用于为每行生成排名，结合 PARTITION BY 可在每个分区内计算排名。

  • RANK()：

    • 为每行分配排名，相同值的记录排名相同。
    • 排名会跳跃，例如：1,1,3（如果有两个第一名，下一名是第三）。
    • 示例： 
      RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
      按部门分区，薪资从高到低排序，生成排名。

  • DENSE_RANK()：

    • 与 RANK() 类似，但排名不跳跃，例如：1,1,2（两个第一名后，下一名是第二）。
    • 示例： 
      DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

  • 区别：

    • 如果数据中有重复值，RANK() 可能导致排名“空缺”，而 DENSE_RANK() 保持连续。
    • 选择哪种函数取决于业务需求：RANK() 适合严格按顺序排名，DENSE_RANK() 适合更紧凑的排名。

窗口函数的优势

与传统的 GROUP BY 不同，PARTITION BY 结合 RANK() 或 DENSE_RANK()：

  • 保留所有行数据，而不是聚合为一行。
  • 可以在每个分区内动态计算排名，适合复杂的排序需求。

解决方案分析

问题背景

假设有一个员工表 employees，包含以下字段：

  • employee_id：员工 ID
  • employee_name：员工姓名
  • department_id：部门 ID
  • salary：薪资

目标：查询每个部门内薪资排名前 2 的员工信息。

表结构与示例数据

employees 表数据如下：

employee_idemployee_namedepartment_idsalary
1Alice1015000
2Bob1016000
3Charlie1015500
4David1027000
5Eve1026500
6Frank1034500

SQL 查询

使用 PARTITION BY 和 RANK() 实现：

WITH RankedEmployees AS (
    SELECT
        employee_id,
        employee_name,
        department_id,
        salary,
        RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM
        employees
)
SELECT
    employee_id,
    employee_name,
    department_id,
    salary,
    salary_rank
FROM
    RankedEmployees
WHERE
    salary_rank <= 2;

查询结果

结果如下：

employee_idemployee_namedepartment_idsalarysalary_rank
2Bob10160001
3Charlie10155002
4David10270001
5Eve10265002
6Frank10345001

步骤解析

  1. PARTITION BY department_id：
    • 将数据按 department_id 分区，例如部门 101、102、103 各为一个分区。
  2. ORDER BY salary DESC：
    • 在每个分区内按薪资从高到低排序。
  3. RANK()：
    • 为每行生成排名，部门 101 中 Bob（6000）排名 1，Charlie（5500）排名 2。
  4. CTE（WITH 子句）：
    • 因为 RANK() 不能直接在 WHERE 中使用，需通过 CTE 或子查询存储临时结果。
  5. WHERE salary_rank <= 2：
    • 筛选每个部门排名 1 和 2 的记录。

使用 DENSE_RANK()

如果改用 DENSE_RANK()：

WITH RankedEmployees AS (
    SELECT
        employee_id,
        employee_name,
        department_id,
        salary,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM
        employees
)
SELECT
    employee_id,
    employee_name,
    department_id,
    salary,
    salary_rank
FROM
    RankedEmployees
WHERE
    salary_rank <= 2;

结果与 RANK() 相同（因示例数据无重复薪资）。若有重复薪资，DENSE_RANK() 可能包含更多记录。

注意事项

  • 性能：大数据量下，窗口函数可能较慢，建议为 department_id 和 salary 添加索引。
  • 重复薪资：若部门内薪资重复，RANK() 或 DENSE_RANK() 可能返回超过 2 条记录。需额外条件（如 employee_id）控制。
  • 空分区：无员工的部门不会出现在结果中。
  • RANK() vs DENSE_RANK()：选择取决于是否需要连续排名。

总结

PARTITION BY 结合 RANK() 或 DENSE_RANK() 是查询分组内 Top N 的高效方法。通过分区和排名，我们可以灵活处理复杂的排序需求。本例展示了如何查询部门内薪资 Top 2，适用于类似场景。希望这篇文章帮你深入理解 SQL 窗口函数！

avatar
文章
阅读
粉丝