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()函數將有序分區的行分佈到指定數量的存儲區中。