NTILE (Transact-SQL)NTILE (Transact-SQL)

本主題的適用對象: 是(從 2008年起) 的 SQL Server是Azure SQL Database是Azure SQL 資料倉儲yesParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

將排序分割區中的資料列散發到指定數目的群組中。Distributes the rows in an ordered partition into a specified number of groups. 這些群組從 1 開始編號。The groups are numbered, starting at one. 對於每個資料列,NTILE 都會傳回資料列所屬群組的號碼。For each row, NTILE returns the number of the group to which the row belongs.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )  

引數Arguments

integer_expressioninteger_expression
用於指定每個分割區必須劃分之群組數的正整數常數運算式。Is a positive integer constant expression that specifies the number of groups into which each partition must be divided. integer_expression 可為 int 類型或 bigintinteger_expression can be of type int, or bigint.

<partition_by_clause><partition_by_clause>
FROM 子句產生的結果集分割成函式所要套用的分割區。Divides the result set produced by the FROM clause into partitions to which the function is applied. 如需 PARTITION BY 語法,請參閱 OVER 子句 (Transact-SQL)For the PARTITION BY syntax, see OVER Clause (Transact-SQL).

<order_by_clause><order_by_clause>
指定 NTILE 值指派給分割區中之資料列的順序。Determines the order in which the NTILE values are assigned to the rows in a partition. 在次序函式中使用 <order_by_clause> 時,整數無法表示資料行。An integer cannot represent a column when the <order_by_clause> is used in a ranking function.

傳回類型Return Types

bigintbigint

RemarksRemarks

如果 integer_expression 無法整除分割區中的資料列數,兩個大小的群組會相差一個成員。If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. 在 OVER 子句所指定的順序中,較大群組會在較小群組的前面。Larger groups come before smaller groups in the order specified by the OVER clause. 例如,如果資料列總數是 53,群組數目是 5,前三個群組會有 11 個資料列,後兩個群組會有 10 個資料列。For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. 如果群組數目可以整除資料列的總數,資料列就會平均分散在各個群組中。If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. 例如,如果資料列總數是 50,有 5 個群組,每個貯體都會包含 10 個資料列。For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

NTILE 不具決定性。NTILE is nondeterministic. 如需詳細資訊,請參閱 決定性與非決定性函數For more information, see Deterministic and Nondeterministic Functions.

範例Examples

A.A. 將資料列分割成數個群組Dividing rows into groups

在下列範例中,根據員工年初至今的銷售收益,將資料列歸類為四組員工。The following example divides rows into four groups of employees based on their year-to-date sales. 由於群組數目無法整除資料列的總數,前兩組有四個資料列,其他組各有三個資料列。Because the total number of rows is not divisible by the number of groups, the first two groups have four rows and the remaining groups have three rows each.

USE AdventureWorks2012;   
GO  
SELECT p.FirstName, p.LastName  
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile  
    ,CONVERT(nvarchar(20),s.SalesYTD,1) AS SalesYTD  
    , a.PostalCode  
FROM Sales.SalesPerson AS s   
INNER JOIN Person.Person AS p   
    ON s.BusinessEntityID = p.BusinessEntityID  
INNER JOIN Person.Address AS a   
    ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0;  
GO  

以下為結果集:Here is the result set.


FirstName      LastName              Quartile  SalesYTD       PostalCode  
-------------  --------------------- --------- -------------- ----------  
Linda          Mitchell              1         4,251,368.55   98027  
Jae            Pak                   1         4,116,871.23   98055  
Michael        Blythe                1         3,763,178.18   98027  
Jillian        Carson                1         3,189,418.37   98027  
Ranjit         Varkey Chudukatil     2         3,121,616.32   98055  
José           Saraiva               2         2,604,540.72   98055  
Shu            Ito                   2         2,458,535.62   98055  
Tsvi           Reiter                2         2,315,185.61   98027  
Rachel         Valdez                3         1,827,066.71   98055  
Tete           Mensa-Annan           3         1,576,562.20   98055  
David          Campbell              3         1,573,012.94   98055  
Garrett        Vargas                4         1,453,719.47   98027  
Lynn           Tsoflias              4         1,421,810.92   98055  
Pamela         Ansman-Wolfe          4         1,352,577.13   98027  

(14 row(s) affected)  

B.B. 使用 PARTITION BY 分割結果集Dividing the result set by using PARTITION BY

下列範例會在範例 A 的程式碼中加入 PARTITION BY 引數。資料列會先由 PostalCode 進行資料分割,接著再依據每個 PostalCode 分成四個群組。The following example adds the PARTITION BY argument to the code in example A. The rows are first partitioned by PostalCode and then divided into four groups within each PostalCode. 此範例也會宣告 @NTILE_Var 變數,並且使用該變數來指定 integer_expression 參數的值。The example also declares a variable @NTILE_Var and uses that variable to specify the value for the integer_expression parameter.

USE AdventureWorks2012;  
GO  
DECLARE @NTILE_Var int = 4;  

SELECT p.FirstName, p.LastName  
    ,NTILE(@NTILE_Var) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS Quartile  
    ,CONVERT(nvarchar(20),s.SalesYTD,1) AS SalesYTD  
    ,a.PostalCode  
FROM Sales.SalesPerson AS s   
INNER JOIN Person.Person AS p   
    ON s.BusinessEntityID = p.BusinessEntityID  
INNER JOIN Person.Address AS a   
    ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0;  
GO  

以下為結果集:Here is the result set.

FirstName    LastName             Quartile SalesYTD      PostalCode  
------------ -------------------- -------- ------------  ----------  
Linda        Mitchell             1        4,251,368.55  98027  
Michael      Blythe               1        3,763,178.18  98027  
Jillian      Carson               2        3,189,418.37  98027  
Tsvi         Reiter               2        2,315,185.61  98027  
Garrett      Vargas               3        1,453,719.47  98027  
Pamela       Ansman-Wolfe         4        1,352,577.13  98027  
Jae          Pak                  1        4,116,871.23  98055  
Ranjit       Varkey Chudukatil    1        3,121,616.32  98055  
José         Saraiva              2        2,604,540.72  98055  
Shu          Ito                  2        2,458,535.62  98055  
Rachel       Valdez               3        1,827,066.71  98055  
Tete         Mensa-Annan          3        1,576,562.20  98055  
David        Campbell             4        1,573,012.94  98055  
Lynn         Tsoflias             4        1,421,810.92  98055  

(14 row(s) affected)  

範例: Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

C.C. 將資料列分割成數個群組Dividing rows into groups

下列範例會根據 2003 年他們受指派的銷售配額,使用 NTILE 函式將一組銷售人員分割成四個群組。The following example uses the NTILE function to divide a set of salespersons into four groups based on their assigned sales quota for the year 2003. 由於群組數目無法整除資料列的總數,所以第一個群組會有五個資料列,其餘群組會有四個資料列。Because the total number of rows is not divisible by the number of groups, the first group has five rows and the remaining groups have four rows each.

-- Uses AdventureWorks  

SELECT e.LastName, NTILE(4) OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,  
       CONVERT (varchar(13), SUM(SalesAmountQuota), 1) AS SalesQuota  
FROM dbo.DimEmployee AS e   
INNER JOIN dbo.FactSalesQuota AS sq   
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE sq.CalendarYear = 2003  
    AND SalesTerritoryKey IS NOT NULL AND SalesAmountQuota <> 0  
GROUP BY e.LastName  
ORDER BY Quartile, e.LastName;  

以下為結果集:Here is the result set.

LastName          Quartile SalesYTD  
----------------- -------- ------------`  
Blythe            1        4,716,000.00  
Carson            1        4,350,000.00  
Mitchell          1        4,682,000.00  
Pak               1        5,142,000.00  
Varkey Chudukatil 1        2,940,000.00  
Ito               2        2,644,000.00  
Saraiva           2        2,293,000.00  
Vargas            2        1,617,000.00  
Ansman-Wolfe      3        1,183,000.00  
Campbell          3        1,438,000.00  
Mensa-Annan       3        1,481,000.00  
Valdez            3        1,294,000.00  
Abbas             4          172,000.00  
Albert            4          651,000.00  
Jiang             4          544,000.00  
Tsoflias          4          867,000.00

D.D. 使用 PARTITION BY 分割結果集Dividing the result set by using PARTITION BY

下列範例會將 PARTITION BY 引數新增至範例 A 的程式碼中。資料列會先由 SalesTerritoryCountry 進行分割,接著依據每個 SalesTerritoryCountry 分成兩個群組。The following example adds the PARTITION BY argument to the code in example A. The rows are first partitioned by SalesTerritoryCountry and then divided into two groups within each SalesTerritoryCountry. 請注意,OVER 子句中的 ORDER BY 會排序 NTILE,而 SELECT 陳述式的 ORDER BY 會排序結果集。Notice that the ORDER BY in the OVER clause orders the NTILE and the ORDER BY of the SELECT statement orders the result set.

-- Uses AdventureWorks  

SELECT e.LastName, NTILE(2) OVER(PARTITION BY e.SalesTerritoryKey ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,  
       CONVERT (varchar(13), SUM(SalesAmountQuota), 1) AS SalesQuota  
   ,st.SalesTerritoryCountry  
FROM dbo.DimEmployee AS e   
INNER JOIN dbo.FactSalesQuota AS sq   
    ON e.EmployeeKey = sq.EmployeeKey  
INNER JOIN dbo.DimSalesTerritory AS st  
    ON e.SalesTerritoryKey = st.SalesTerritoryKey  
WHERE sq.CalendarYear = 2003  
GROUP BY e.LastName,e.SalesTerritoryKey,st.SalesTerritoryCountry  
ORDER BY st.SalesTerritoryCountry, Quartile;  

以下為結果集:Here is the result set.

LastName          Quartile SalesYTD       SalesTerritoryCountry  
----------------- -------- -------------- ------------------  
Tsoflias          1         867,000.00     Australia  
Saraiva           1        2,293,000.00    Canada  
Varkey Chudukatil 1        2,940,000.00    France  
Valdez            1        1,294,000.00    Germany  
Alberts           1          651,000.00    NA  
Jiang             1          544,000.00    NA  
Pak               1        5,142,000.00    United Kingdom  
Mensa-Annan       1        1,481,000.00    United States  
Campbell          1        1,438,000.00    United States  
Reiter            1        2,768,000.00    United States  
Blythe            1        4,716,000.00    United States  
Carson            1        4,350,000.00     United States  
Mitchell          1        4,682,000.00     United States  
Vargas            2        1,617,000.00     Canada  
Abbas             2          172,000.00     NA  
Ito               2        2,644,000.00     United States  
Ansman-Wolfe      2        1,183,000.00     United States

另請參閱See Also

RANK (Transact-SQL) RANK (Transact-SQL)
DENSE_RANK (Transact-SQL) DENSE_RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL) ROW_NUMBER (Transact-SQL)
次序函式 (Transact-SQL) Ranking Functions (Transact-SQL)
內建函數 (Transact-SQL)Built-in Functions (Transact-SQL)