SQL NTILE

摘要：在本教學中，您將學習如何使用 SQL NTILE() 函數將結果集分割成指定數量的桶。

SQL NTILE() 函數概述

SQL NTILE() 是一個視窗函數，可讓您將結果集分割成指定數量的近似相等群組或桶。它會為每個群組指定一個從一開始的桶編號。對於群組中的每一列，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

PARITITION BY 子句將從 FROM 子句傳回的結果集劃分為要應用 NTILE() 函數的分割區。

ORDER BY

ORDER BY 子句指定每個分割區中要應用 NTILE() 的列排序。

請注意，如果列數無法被 buckets 整除，NTILE() 函數會產生兩種大小的群組，其差異為一。較大的群組始終在 ORDER BY 子句指定的順序中排在較小的群組之前。

如果總列數可以被 buckets 整除，則列會在群組之間平均分配。

下列語句會建立一個名為 t 的新資料表，其中儲存從 1 到 10 的 10 個整數

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() 函數將十列分割成三個群組，則第一個群組將有四列，其他兩個群組將有三列。

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

以下顯示輸出

如輸出清楚顯示，第一個群組有四列，而其他群組有三列。

下列語句使用兩個而不是三個桶

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

現在，我們有兩個群組，它們具有相同的列數。

SQL NTILE() 函數範例

請參閱範例資料庫中的下列 employees 資料表

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

下列語句使用 NTILE() 函數根據員工的薪資將員工分割成五個桶

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() 函數的範例

下列語句將每個部門中的員工分成兩個群組

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() 函數將結果集分割成指定數量的桶。