NTILE (Transact-SQL)

Artigo
06/18/2013

Distribui as linhas de uma partição ordenada em um número de grupos especificado. Os grupos são numerados, iniciando em um. Para cada linha, NTILE retorna o número do grupo ao qual a linha pertence.

Ícone de vínculo de tópico Convenções de sintaxe Transact-SQL

Sintaxe

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

Argumentos

integer_expression
É uma expressão constante inteira positiva que especifica o número de grupos nos quais cada partição deve ser dividida. integer_expression pode ser do tipo int ou bigint.
<partition_by_clause>
Divide o conjunto de resultados produzido pela cláusula FROM nas partições às quais a função é aplicada. Para a sintaxe PARTITION BY, consulte Cláusula OVER (Transact-SQL).
<order_by_clause>
Determina a ordem na qual os valores de NTILE são atribuídos às linhas de uma partição. Um inteiro não pode representar uma coluna quando a <order_by_clause> é usada em uma função de classificação.

Tipos de retorno

bigint

Comentários

Se o número de linhas em uma partição não for divisível por integer_expression, isso causará grupos de dois tamanhos que diferem por um membro. Grupos maiores aparecem antes de grupos menores na ordem especificada pela cláusula OVER. Por exemplo, se o número total de linhas for 53 e o número de grupos for cinco, os três primeiros grupos terão 11 linhas e os dois grupos restantes terão 10 linhas cada. Por outro lado, se o número total de linhas for divisível pelo número de grupos, as linhas serão igualmente distribuídas entre os grupos. Por exemplo, se o número total de linhas for 50 e houver cinco grupos, cada pacote conterá 10 linhas.

Exemplos

A.Dividindo linhas em grupos

O exemplo a seguir divide linhas em quatro grupos de funcionários com base nas vendas no ano até o momento. Como o número total de linhas não é divisível pelo número de grupos, os dois primeiros grupos terão quatro linhas e os grupos restantes terão três linhas cada.

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

Aqui está o conjunto de resultados.

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.Dividindo o conjunto de resultados usando PARTITION BY

O exemplo a seguir adiciona o argumento PARTITION BY ao código no exemplo A. As linhas são particionadas primeiramente por PostalCode e depois são divididas em quatro grupos em cada PostalCode. O exemplo também declara uma variável @NTILE\_Var e usa essa variável para especificar o valor para o parâmetro 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

Aqui está o conjunto de resultados.

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)