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句は、結果セット内の行を1つ以上の基準でパーティションに分割します。
• 次に、ORDER BY句は、各パーティション内の行をソートします。
• RANK()関数は、各パーティションの行に対して実行され、各パーティション境界を越えると再初期化されます。

同じ列の値には同じランクが割り当てられます。複数の行が同じランクを共有する場合、次の行のランクは連続しません。これは、オリンピックのメダル授与に似ており、2人の選手が金メダルを共有した場合、銀メダルはありません。

次のステートメントは、新しいテーブル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)

次の図は出力結果を示しています。

出力結果に明確に示されているように、2行目と3行目は同じ値を持つため、同じランクを共有しています。4行目は、RANK()関数がランク3をスキップするため、ランク4を取得します。

連続したランクが必要な場合は、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()関数の使用例

次のステートメントは、各部署で2番目に高い給与の従業員を見つけます。

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()関数の使用方法を学習しました。