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

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

将有序分区中的行分发到指定数目的组中。Distributes the rows in an ordered partition into a specified number of groups. 各个组有编号,编号从一开始。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 expression that specifies the number of groups into which each partition must be divided. integer_expression 可以是 int 或 bigint 类型。integer_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,有五个组,则每组将包含 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. 有关详细信息,请参阅 Deterministic and Nondeterministic FunctionsFor 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

以下示例将 PARTITION BY 参数添加到示例 A 中的代码。首先按 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

以下示例使用 NTILE 函数根据其 2003 年的分配销售配额将一组销售人员划分为四个组。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)