SQL RANK

摘要：在本教學中，您將學習如何使用 SQL RANK() 函數來找出結果集中每一列的排名。

MySQL RANK() 函數介紹

RANK() 函數是一個視窗函數，它會為結果集分割中的每一列指定一個排名。

一列的排名由 1 加上它之前的所有排名數量決定。

RANK() 函數的語法如下：

RANK() OVER (
	PARTITION BY <expr1>[{,<expr2>...}]
	ORDER BY <expr1> [ASC|DESC], [{,<expr2>...}]
)
Code language: SQL (Structured Query Language) (sql)

在這個語法中：

• 首先，PARTITION BY 子句會根據一或多個條件將結果集中的列分配到不同的分割區。
• 其次，ORDER BY 子句會排序每個分割區中的列。
• RANK() 函數會對每個分割區的列進行操作，並在跨越每個分割區邊界時重新初始化。

相同的列值會獲得相同的排名。當多個列共享相同的排名時，下一列的排名不會是連續的。這類似於奧運頒獎，如果兩位運動員並列金牌，就不會有銀牌。

以下陳述式建立一個新的資料表，名稱為 t，並插入一些範例資料

CREATE TABLE t (
	col CHAR
);
	
INSERT INTO t(col)
VALUES('A'),('B'),('B'),('C'),('D'),('D'),('E');
	
	
SELECT 
	*
FROM
	t;
Code language: SQL (Structured Query Language) (sql)

以下陳述式使用 RANK() 函數為結果集的列指定排名

SELECT
	col,
	RANK() OVER (
		ORDER BY col
	) myrank
FROM
	t;
Code language: SQL (Structured Query Language) (sql)

下圖顯示輸出結果

如輸出結果清楚所示，第二列和第三列由於具有相同的值而共享相同的排名。第四列獲得排名 4，因為 RANK() 函數跳過了排名 3。

請注意，如果您想要連續的排名，可以使用 DENSE_RANK() 函數。

SQL RANK() 函數範例

我們將使用範例資料庫中的 employees 和 departments 資料表進行示範。

在結果集上使用 SQL RANK() 函數範例

以下陳述式會依員工的薪資對其進行排名

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

以下顯示部分輸出

在此範例中，我們省略了 PARTITION BY 子句，因此整個結果集被視為單一分割區。

ORDER BY 子句依薪資排序結果中的列。然後，RANK() 函數會根據薪資由高至低的順序，應用於結果中的每一列。

在分割區上使用 SQL RANK() 函數範例

以下陳述式會找出部門中薪資第二高的員工

WITH payroll AS (
	SELECT 
		first_name, 
		last_name, 
		department_id,
		salary, 
		RANK() OVER (
			PARTITION BY department_id
			ORDER BY salary) salary_rank
	FROM 
		employees
)
SELECT 
	first_name, 
	last_name,
	department_name,
	salary
FROM 
	payroll p
	INNER JOIN departments d 
		ON d.department_id = p.department_id
WHERE 
	salary_rank = 2;	
Code language: SQL (Structured Query Language) (sql)

在通用資料表運算式中，我們依部門找出員工的薪資排名

• 首先，PARTITION BY 子句會將員工記錄按部門劃分為不同的分割區。
• 然後，ORDER BY 子句會依薪資排序每個分割區中的員工。
• 最後，RANK() 函數會為每個分割區的員工指定排名。薪資相同的員工會獲得相同的排名。

下圖說明通用資料表運算式的部分結果集

外部查詢只選取薪資排名為 2 的員工。它也與 departments 資料表聯結，以在最終結果集中傳回部門名稱。

下圖顯示查詢的輸出

在本教學中，您學習了如何使用 SQL RANK() 函數來為結果集中的每一列指定排名。