NTILE (Transact-SQL)
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.
.gif "Ícone de vínculo de tópico")
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 RANK é aplicada. Para obter a sintaxe de 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. Para obter mais informações, consulte Cláusula ORDER BY [Transact-SQL]. 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 as linhas em quatro grupos. Como o número total de linhas não é divisível pelo número de grupos, o primeiro grupo terá quatro linhas e os grupos restantes terão três linhas cada.
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
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. Primeiro, as linhas são particionadas por PostalCode e depois 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 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
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)
(14 linha(s) afetada(s))