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 #

PARITITION BY 子句將 FROM 子句返回的結果集劃分為多個分割槽，NTILE() 函式將應用於這些分割槽。

ORDER BY #

ORDER BY 子句指定了 NTILE() 函式應用到的每個分割槽中行的順序。

請注意，如果行數不能被 buckets 整除，NTILE() 函式將產生兩種大小的組，其大小相差為 1。較大的組總是按照 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() 函式將結果集劃分為指定數量的分桶。

資料庫 #

• PostgreSQL NTILE 函式
• Oracle NTILE 函式
• SQL Server NTILE 函式
• MySQL NTILE 函式
• SQLite NTILE 函式