SQL DENSE_RANK

概要: このチュートリアルでは、SQL DENSE_RANK() 関数を使用して、ランク値にギャップがないパーティション内の行をランク付けする方法を学習します。

SQL DENSE_RANK() 関数の概要

DENSE_RANK() は、ランク値にギャップがないパーティション内の行にランクを割り当てるウィンドウ関数です。

各パーティション内の複数の行が同じ値を持つ場合、それらの行には同じランクが割り当てられます。次の行のランクは 1 増加します。

RANK() 関数とは異なり、DENSE_RANK() 関数は常に連続したランク値を生成します。

DENSE_RANK() 関数の構文は次のとおりです。

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

この構文では

• まず、PARTITION BY 句は、FROM 句によって生成された結果セットをパーティションに分割します。
• 次に、ORDER BY 句は各パーティション内の行の順序を指定します。
• 最後に、DENSE_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)

次のステートメントは、DENSE_RANK() 関数と RANK() 関数の両方を使用して、結果セットの各行にランクを割り当てます。

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

出力は次のとおりです。

SQL DENSE_RANK() 関数の例

デモの目的で、サンプルデータベースの employees テーブルと departments テーブルを使用します。

結果セット全体に SQL DENSE_RANK() を使用する例

次のステートメントは、DENSE_RANK() 関数を使用して、従業員を給与でランク付けします。

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

この例では、PARTITION BY 句を省略したため、DENSE_RANK() 関数は結果セット全体を単一のパーティションとして扱いました。

まず、ORDER BY 句は給与を降順にソートしました。次に、DENSE_RANK() 関数は、各従業員に給与額に基づいてランクを割り当てました。

次の図は、クエリの出力の一部を示しています。

パーティションに対して SQL 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)

この例では

• まず、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)

次の出力は、部門で最も給与の高い従業員を示しています。

このチュートリアルでは、SQL DENSE_RANK() 関数を使用して、ランク値にギャップがないパーティション内の行をランク付けする方法を学習しました。