NTILE (Transact-SQL)
Применимо: 
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)
Распределяет строки упорядоченной секции в заданное количество групп. Группы нумеруются, начиная с единицы. Для каждой строки функция NTILE возвращает номер группы, которой принадлежит строка.
Соглашения о синтаксисе Transact-SQL
Синтаксис
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Аргументы
integer_expression
Это положительное целое выражение, указывающее число групп, на которые необходимо разделить каждую секцию. integer_expression может иметь тип int или bigint.
<partition_by_clause>
Делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция. Синтаксис PARTITION BY см. в статье Предложение OVER (Transact-SQL).
<order_by_clause>
Определяет порядок назначения значений функции NTILE строкам секции. Целое значение не может представлять столбец при использовании <order_by_clause> в ранжирующей функции.
Типы возвращаемых данных
bigint
Замечания
Если количество строк в секции не делится на integer_expression, формируются группы двух размеров, отличающихся на единицу. В порядке, заданном предложением OVER, группы большего размера следуют перед группами меньшего размера. Например, если общее число строк равно 53, а число групп равно пяти, первые три группы будут состоять из 11 строк, а оставшиеся две — из 10. С другой стороны, если общее число строк делится на число групп, строки распределяются равномерно по всем группам. Например, если общее число строк равно 50 и задано пять групп, каждый контейнер будет состоять из 10 строк.
Функция NTILE не детерминирована. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.
Примеры
А. Разделение строк на группы
Следующий пример делит строки на четыре группы сотрудников по их показателям продаж за текущий год. Так как общее число строк не делится на количество групп, первые две группы будут состоять из четырех строк, а остальные — из трех.
USE AdventureWorks2022;
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
Вот результирующий набор.
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. Разделение результирующего набора с помощью ключевого слова PARTITION BY
В следующем примере к коду из примера A добавляется аргумент PARTITION BY. Строки вначале разделяются по столбцу PostalCode, а затем разделяются на четыре группы для каждого значения PostalCode. В этом примере также объявляется переменная @NTILE_Var, которая используется для указания значения параметра integer_expression.
USE AdventureWorks2022;
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
Вот результирующий набор.
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 Synapse Analytics и система платформы аналитики (PDW)
C. Разделение строк на группы
В приведенном ниже примере функция NTILE используется для разделения множества продавцов на четыре группы в соответствии с назначенными им квотами на продажу в 2003 году. Так как общее число строк не делится на количество групп, первая группа будет состоять из пяти строк, а остальные — из четырех.
-- 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;
Вот результирующий набор.
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. Разделение результирующего набора с помощью ключевого слова PARTITION BY
В приведенном ниже примере к коду из примера A добавляется аргумент PARTITION BY. Строки сначала разделяются по столбцу SalesTerritoryCountry, а затем разделяются на две группы для каждого значения SalesTerritoryCountry. Обратите внимание на то, что ORDER BY в предложении OVER упорядочивает NTILE, а ORDER BY в инструкции SELECT упорядочивает результирующий набор.
-- 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;
Вот результирующий набор.
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
См. также
RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL)
Ранжирование функций (Transact-SQL)
Встроенные функции (Transact-SQL)