SQL Server Ntile()函数

在本教程中，将学习如何使用SQL Server NTILE()函数将有序分区的行分配到指定数量的存储区中。

SQL Server NTILE()函数简介

SQL Server NTILE()是一个窗口函数，它将有序分区的行分配到指定数量的大致相等的组或桶中。 它从一个开始为每个组分配一个桶号。 对于组中的每一行，NTILE()函数分配一个桶号，表示该行所属的组。

NTILE()函数的语法如下：

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

在上面语法中，

• buckets - 行划分的桶数。 存储桶可以是表达式或子查询，其计算结果为正整数。 它不能是一个窗口功能。
• PARTITION BY子句将结果集的行分配到应用了NTILE()函数的分区中。
• ORDER BY子句指定应用NTILE()的每个分区中行的逻辑顺序。

如果行数不能被桶整除，则NTILE()函数返回两个大小的组，它们的差值为1。 较大的组总是按照OVER()子句中ORDER BY指定的顺序位于较小的组之前。

另一方面，如果行的总数可以被桶整除，则该函数在桶之间均匀地划分行。

SQL Server NTILE()函数说明

以下语句创建一个名为ntile_demo的新表，该表存储10个整数：

CREATE TABLE sales.ntile_demo (
 v INT NOT NULL
);

INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);


SELECT * FROM sales.ntile_demo;

下面语句使用NTILE()函数将十行数据分为三组：

SELECT 
 v, 
 NTILE (3) OVER (
 ORDER BY v
 ) buckets
FROM 
 sales.ntile_demo;

执行上面查询语句，得到以下结果：

如输出中显示的那样，第一组有四行，另外两组有三行。以下语句使用NTILE()函数将行分配到五个桶中：

SELECT 
 v, 
 NTILE (5) OVER (
 ORDER BY v
 ) buckets
FROM 
 sales.ntile_demo;

执行上面查询语句，得到以下结果：

如上所见，输出有五个组，每个组的行数相同。

SQL Server NTILE()函数示例

下面创建一个视图来演示NTILE()函数的用法。

以下语句创建一个视图，按月返回2017年的净销售额。

CREATE VIEW sales.vw_netsales_2017 AS
SELECT 
 c.category_name,
 DATENAME(month, o.shipped_date) month, 
 CONVERT(DEC(10, 0), SUM(i.list_price * quantity * (1 - discount))) net_sales
FROM 
 sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN production.products p on p.product_id = i.product_id
INNER JOIN production.categories c on c.category_id = p.category_id
WHERE 
 YEAR(shipped_date) = 2017
GROUP BY
 c.category_name,
 DATENAME(month, o.shipped_date);

执行下面查询语句，查询视图中的数据：

SELECT 
 category_name, 
 [month], 
 net_sales
FROM 
 sales.vw_netsales_2017
ORDER BY
 category_name, 
 [month],
 net_sales;

执行上面查询语句，得到以下结果：

1. 在查询结果集中使用SQL Server NTILE()函数示例
以下示例使用NTILE()函数根据净销售额将月份分配到4个桶：

WITH cte_by_month AS(
 SELECT
 month, 
 SUM(net_sales) net_sales
 FROM 
 sales.vw_netsales_2017
 GROUP BY 
 month
)
SELECT
 month, 
 FORMAT(net_sales,'C','en-US') net_sales,
 NTILE(4) OVER(
 ORDER BY net_sales DESC
 ) net_sales_group
FROM 
 cte_by_month;

执行上面查询语句，得到以下结果：

2. 在分区上使用SQL Server NTILE()函数示例
此示例使用NTILE()函数将每月的净销售额划分为4个组：

SELECT
 category_name,
 month, 
 FORMAT(net_sales,'C','en-US') net_sales,
 NTILE(4) OVER(
 PARTITION BY category_name
 ORDER BY net_sales DESC
 ) net_sales_group
FROM 
 sales.vw_netsales_2017;

执行上面查询语句，得到以下结果：

在本教程中，学习了如何使用SQL Server NTILE()函数将有序分区的行分布到指定数量的存储区中。