NTILE (Transact-SQL)
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
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. Larger groups come before smaller groups in the order specified by the OVER clause. 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. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.
A. Dividing rows into groups
The following example divides the rows into four groups. Because the total number of rows is not divisible by the number of groups, the first group has four rows and the remaining groups have three rows each.
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
,s.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. Dividing the result set by using PARTITION BY
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. The example also declares a variable @NTILE_Var and uses that variable to specify the value for the integer_expression parameter.
USE AdventureWorks2008R2;
GO
DECLARE @NTileVar int = 4;
SELECT p.FirstName, p.LastName
,NTILE(@NTileVar) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS Quartile
,s.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
ORDER BY LastName;
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)
(14 row(s) affected)
