NTILE (Transact-SQL)

Статья
03/07/2014

Распределяет строки упорядоченной секции в заданное количество групп. Группы нумеруются, начиная с единицы. Для каждой строки функция NTILE возвращает номер группы, которой принадлежит строка.

Применимо для следующих объектов: SQL Server (SQL Server 2008 по текущую версию), База данных SQL Windows Azure (с первоначального выпуска по текущий выпуск).

Значок ссылки на раздел Синтаксические обозначения в 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 не детерминирована. Дополнительные сведения см. в разделе Детерминированные и недетерминированные функции.

Примеры

А. Разделение строк на группы

Следующий пример делит строки на четыре группы сотрудников по их показателям продаж за текущий год. Так как общее число строк не делится на количество групп, первые две группы будут состоять из четырех строк, а остальные — из трех.

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

Ниже приводится результирующий набор.

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)

Б. Разделение результирующего набора с помощью ключевого слова PARTITION BY

В следующем примере аргумент PARTITION BY добавляется к коду из примера А. Сначала строки секционируются по PostalCode, а затем разделяются на четыре группы в каждом из значений PostalCode. В этом примере также объявляется переменная @NTILE\_Var, которая используется для указания значения параметра integer_expression.

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

Ниже приводится результирующий набор.

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)