第二高值

redeye

問題

+———––+——+
| 欄位名稱 | 類型 |
+———––+——+
| id | int |
| salary | int |
+———––+——+
id 是這個表的主鍵。
表中的每一行包含員工的薪水資訊。

查詢並返回 Employee 表中第二高的不同薪水。如果不存在第二高的薪水，查詢應該返回 null（若使用 Pandas，則返回 None）。
查詢結果如下所示。

範例 1：

輸入：
Employee 表：

+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

輸出：

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

範例 2：

輸入：
Employee 表：

+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+

輸出：

+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+

解答

SELECT 
    MAX(salary) AS SecondHighestSalary
FROM (
    SELECT salary, 
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employee
) ranked
WHERE rank = 2;

這段 SQL 的功能是使用 DENSE_RANK() 函數為 Employee 表中的每個薪水值進行排名，按薪水從高到低排序，並將排名結果輸出為一個新列 rank。

詳細解析

SQL 語法

SELECT salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM Employee;

1.	SELECT salary
•	從 Employee 表中選取 salary 列。
2.	DENSE_RANK() 函數
•	一個窗口函數，用於對數據集中的行分配排名。
•	DENSE_RANK 特性：
•	為每個唯一值分配一個排名。
•	當多行的值相同時，它們會共享相同的排名。
•	不會跳過排名（與 RANK() 不同）。
•	例如，若排名為 1, 2, 2，下一個排名將是 3，而不是 4。
3.	OVER (ORDER BY salary DESC)
•	定義了排名的排序規則。
•	ORDER BY salary DESC 表示按 salary 列從高到低排序。
•	每個薪水值都根據這個順序分配排名。
4.	AS rank
•	將計算出的排名結果命名為新列 rank。

示例輸入數據

+----+--------+
| id | salary |
+----+--------+
| 1 | 300 |
| 2 | 200 |
| 3 | 200 |
| 4 | 100 |
+----+--------+

執行結果

+--------+------+
| salary | rank |
+--------+------+
| 300 | 1 |
| 200 | 2 |
| 200 | 2 |
| 100 | 3 |
+--------+------+

解釋

1.	薪水 300 是最高的，排名為 1。
2.	薪水 200 出現兩次，因為值相同，它們共享相同的排名 2。
3.	薪水 100 是下一個唯一值，排名為 3。
4.	沒有跳過排名（例如，沒有 rank = 4）。

對比 DENSE_RANK 和 RANK

如果改用 RANK()，輸出結果會如下：

+--------+------+
| salary | rank |
+--------+------+
| 300 | 1 |
| 200 | 2 |
| 200 | 2 |
| 100 | 4 | -- 跳過了3
+--------+------+

•	使用 RANK() 時，排名會跳過被重複值佔據的位次。

適用場景

•	DENSE_RANK 適用於需要連續排名的場景（例如統計唯一的前幾名）。
•	RANK 更適合需要考慮排名差異（例如比賽名次）。