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 函数