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() 函式對分割槽中的行進行排名，且排名值沒有間隙。

資料庫 #

本教程是否有幫助？