SQL DENSE_RANK() 函数

摘要：在本教程中，您将学习如何使用 SQL DENSE_RANK() 函数对分区中的行进行排名，且排名值没有间隙。

SQL DENSE_RANK() 函数简介 #

DENSE_RANK() 是一个窗口函数，它为分区中的每一行分配一个排名，且排名值没有间隙。

如果同一分区中的两行或多行具有相同的值，它们将获得相同的排名。下一行的排名将加一。

与 RANK() 函数不同，DENSE_RANK() 函数总是生成连续的排名值。

以下是 DENSE_RANK() 窗口函数的语法：

DENSE_RANK() OVER (
   PARTITION BY expression1 [{,expression2...}]
   ORDER BY expression1 [ASC|DESC], [{,expression2...}]
)Code language: SQL (Structured Query Language) (sql)

在此语法中：

PARTITION BY 子句将结果集划分为多个分区。
ORDER BY 指定每个分区中行的顺序。
DENSE_RANK() 函数应用于每个分区中按指定顺序排列的行。当跨越分区边界时，它会重置排名值。

基本的 DENSE_RANK 函数示例 #

首先，创建一个新表 dense_rank_demos，它有一个名为 v 的列：

CREATE TABLE IF NOT EXISTS dense_rank_demos (v VARCHAR);Code language: SQL (Structured Query Language) (sql)

试一试

其次，向 dense_rank_demos 表中插入一些行：

INSERT INTO
  dense_rank_demos (v)
VALUES
  ('A'),
  ('B'),
  ('B'),
  ('C'),
  ('D'),
  ('D'),
  ('E');Code language: SQL (Structured Query Language) (sql)

试一试

第三，从 dense_rank_demos 表中检索数据：

SELECT v FROM dense_rank_demos;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 v
---
 A
 B
 B
 C
 D
 D
 ECode language: SQL (Structured Query Language) (sql)

最后，使用 DENSE_RANK() 和 RANK() 函数为结果集的每一行分配排名：

SELECT
  v,
  DENSE_RANK() OVER ( ORDER BY v) my_dense_rank,
  RANK() OVER (ORDER BY v) my_rank
FROM
  dense_rank_demos;Code language: SQL (Structured Query Language) (sql)

输出

 v | my_dense_rank | my_rank
---+---------------+---------
 A |             1 |       1
 B |             2 |       2
 B |             2 |       2
 C |             3 |       4
 D |             4 |       5
 D |             4 |       5
 E |             5 |       7Code language: SQL (Structured Query Language) (sql)

在结果集上使用 SQL DENSE_RANK() 的示例 #

为了演示，我们将使用示例数据库中的 employees 和 departments 表。

以下语句使用 DENSE_RANK() 函数按薪资对员工进行排名：

SELECT
  first_name,
  last_name,
  salary,
  DENSE_RANK() OVER (
    ORDER BY salary DESC
  ) salary_rank
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 first_name  |  last_name  |  salary  | salary_rank
-------------+-------------+----------+-------------
 Steven      | King        | 24000.00 |           1
 Neena       | Kochhar     | 17000.00 |           2
 Lex         | De Haan     | 17000.00 |           2
 John        | Russell     | 14000.00 |           3
 Karen       | Partners    | 13500.00 |           4
 Michael     | Hartstein   | 13000.00 |           5
 Shelley     | Higgins     | 12000.00 |           6
 Nancy       | Greenberg   | 12000.00 |           6
 Den         | Raphaely    | 11000.00 |           7
...Code language: SQL (Structured Query Language) (sql)

在此示例中，DENSE_RANK() 函数将整个结果集视为单个分区，因为我们没有使用 PARTITION BY 子句。

ORDER BY 子句将员工薪资从高到低排序，DENSE_RANK() 函数根据薪资额为每位员工分配一个排名。

在分区上使用 SQL DENSE_RANK() 函数的示例 #

以下语句使用 DENSE_RANK 函数按薪资对每个部门内的员工进行排名：

SELECT
  first_name,
  last_name,
  department_name,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department_name
    ORDER BY salary DESC
  ) salary_rank
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id;Code language: SQL (Structured Query Language) (sql)

试一试

输出

first_name  |  last_name  | department_name  |  salary  | salary_rank
-------------+-------------+------------------+----------+-------------
 Shelley     | Higgins     | Accounting       | 12000.00 |           1
 William     | Gietz       | Accounting       |  8300.00 |           2
 Jennifer    | Whalen      | Administration   |  4400.00 |           1
 Steven      | King        | Executive        | 24000.00 |           1
 Neena       | Kochhar     | Executive        | 17000.00 |           2
 Lex         | De Haan     | Executive        | 17000.00 |           2
 Nancy       | Greenberg   | Finance          | 12000.00 |           1
 Daniel      | Faviet      | Finance          |  9000.00 |           2
 John        | Chen        | Finance          |  8200.00 |           3
 Jose Manuel | Urman       | Finance          |  7800.00 |           4
 Ismael      | Sciarra     | Finance          |  7700.00 |           5
 Luis        | Popp        | Finance          |  6900.00 |           6
...Code language: SQL (Structured Query Language) (sql)

在此示例中：

首先，PARTITION BY 子句按部门将员工划分为多个分区。
然后，ORDER BY 子句按薪资对每个部门（分区）中的员工进行排序。
第三，DENSE_RANK() 函数根据薪资顺序为每个分区中的每一行分配一个排名。

如果您只想查找在各自部门中薪资最高的员工，可以在 FROM 子句中使用子查询，如下所示：

SELECT
  *
FROM
  (
    SELECT
      first_name,
      last_name,
      department_name,
      salary,
      DENSE_RANK() OVER (
        PARTITION BY
          department_name
        ORDER BY
          salary DESC
      ) salary_rank
    FROM
      employees e
      INNER JOIN departments d ON d.department_id = e.department_id
  ) t
WHERE
  salary_rank = 1;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 first_name | last_name | department_name  |  salary  | salary_rank
------------+-----------+------------------+----------+-------------
 Shelley    | Higgins   | Accounting       | 12000.00 |           1
 Jennifer   | Whalen    | Administration   |  4400.00 |           1
 Steven     | King      | Executive        | 24000.00 |           1
 Nancy      | Greenberg | Finance          | 12000.00 |           1
 Susan      | Mavris    | Human Resources  |  6500.00 |           1
 Alexander  | Hunold    | IT               |  9000.00 |           1
 Michael    | Hartstein | Marketing        | 13000.00 |           1
 Hermann    | Baer      | Public Relations | 10000.00 |           1
 Den        | Raphaely  | Purchasing       | 11000.00 |           1
 John       | Russell   | Sales            | 14000.00 |           1
 Adam       | Fripp     | Shipping         |  8200.00 |           1Code language: SQL (Structured Query Language) (sql)

摘要 #

使用 SQL DENSE_RANK() 函数对分区中的行进行排名，且排名值没有间隙。

数据库 #

本教程是否有帮助？