SQL NTILE

概要: このチュートリアルでは、SQL NTILE() 関数を使用して、結果セットを指定された数のバケットに分割する方法を学習します。

SQL NTILE() 関数の概要

SQL NTILE() は、結果セットを指定された数のほぼ等しいグループ、つまりバケットに分割できるウィンドウ関数です。 各グループには、1から始まるバケット番号が割り当てられます。 グループ内の各行について、NTILE() 関数は、行が属するグループを表すバケット番号を割り当てます。

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

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

構文を詳しく見てみましょう。

buckets

バケットの数。正の整数リテラルまたは正の整数に評価される式です。

PARTITION BY

PARTITION BY 句は、FROM 句から返された結果セットを、NTILE() 関数が適用されるパーティションに分割します。

ORDER BY

ORDER BY 句は、NTILE() が適用される各パーティション内の行の順序を指定します。

行数が buckets で割り切れない場合、NTILE() 関数は、差が1である2つのサイズのグループになります。 大きいグループは常に、ORDER BY 句で指定された順序で小さいグループの前に来ます。

行の合計が buckets で割り切れる場合、行はグループ間で均等に分割されます。

次のステートメントは、1から10までの10個の整数を格納する t という名前の新しいテーブルを作成します。

CREATE TABLE t (
	col INT NOT NULL
);
	
INSERT INTO t(col) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
	
	
SELECT * FROM t;
Code language: SQL (Structured Query Language) (sql)

NTILE() 関数を使用して10行を3つのグループに分割すると、最初のグループは4行、他の2つのグループは3行になります。

SELECT 
	col, 
	NTILE (3) OVER (
		ORDER BY col
	) buckets
FROM 
	t;
Code language: SQL (Structured Query Language) (sql)

出力は次のとおりです。

出力に明確に示されているように、最初のグループには4行があり、他のグループには3行があります。

次のステートメントは、3つのバケットではなく2つのバケットを使用します。

SELECT 
	col, 
	NTILE (2) OVER (
		ORDER BY col
	) buckets
FROM 
	t;
Code language: SQL (Structured Query Language) (sql)

これで、同じ行数の2つのグループができました。

SQL NTILE() 関数の例

サンプルデータベースの次の employees テーブルを参照してください。

結果セットの例に対する SQL NTILE() 関数の使用

次のステートメントは、NTILE() 関数を使用して、従業員を給与に基づいて5つのバケットに分割します。

SELECT
	first_name, 
	last_name, 
	salary,
	NTILE(5) OVER (
		ORDER BY salary DESC
	) salary_group
FROM 
	employees;
Code language: SQL (Structured Query Language) (sql)

出力は次のとおりです。

パーティションの例に対する SQL NTILE() 関数の使用

次のステートメントは、各部門の従業員を2つのグループに分けます。

SELECT
	first_name, 
	last_name, 
	department_name,
	salary,
	NTILE(2) OVER (
		PARTITION BY department_name
		ORDER BY salary
	) salary_group
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 句は、各パーティションの従業員を給与でソートしました。
• 最後に、NTILE() 関数は、各パーティションの各行にバケット番号を割り当てました。 部門が変更されるたびに、バケット番号がリセットされます。

このチュートリアルでは、SQL NTILE() 関数を使用して、結果セットを指定された数のバケットに分割する方法を学習しました。