Поделиться через

  • Статья

NTILE (Transact-SQL)

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

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

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

Аргументы

  • integer_expression
    Положительное целое выражение-константа, указывающее количество групп, на которые необходимо разделить каждую секцию. Аргумент integer_expression может иметь тип int или bigint.

  • <partition_by_clause>
    Делит результирующий набор, сформированный предложением FROM, на секции, к которым применяется функция RANK. Синтаксис предложения PARTITION BY см. в разделе Предложение OVER (Transact-SQL).

  • <order_by_clause>
    Определяет порядок назначения значений функции NTILE строкам секции. Дополнительные сведения см. в разделе Предложение ORDER BY (Transact-SQL). Целое значение не может представлять столбец < order_by_clause > при использовании его в ранжирующей функции.

Типы возвращаемых данных

bigint

Замечания

Если количество строк в секции не делится на integer_expression, формируются группы двух размеров, отличающихся на единицу. В порядке, заданном предложением OVER, группы большего размера следуют перед группами меньшего размера. Например, если общее число строк равно 53, а число групп равно пяти, первые три группы будут состоять из 11 строк, а оставшиеся две — из 10. С другой стороны, если общее число строк делится на число групп, строки распределяются равномерно по всем группам. Например, если общее число строк равно 50 и задано пять групп, каждый сегмент будет состоять из 10 строк.

Примеры

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

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

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address 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

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

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 s 
    INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0
ORDER BY LastName;
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)

(Обработано строк: 14)