NTILE (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

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.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

integer_expression
É uma expressão 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 em partições nas quais a função é aplicada. Para obter a sintaxe de PARTITION BY, confira 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 bucket conterá 10 linhas.

NTILE é não determinística. Para obter mais informações, veja Funções determinísticas e não determinísticas.

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 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  

Este é 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 do parâmetro 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  

Este é 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)  

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

C. Dividindo linhas em grupos

O exemplo a seguir usa a função NTILE para dividir um conjunto de vendedores em quatro grupos com base em suas cotas de vendas atribuídas para o ano de 2003. Como o número total de linhas não é divisível pelo número de grupos, o primeiro grupo terá cinco linhas e os grupos restantes terão quatro linhas cada.

-- 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;  

Este é o conjunto de resultados.

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. 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 SalesTerritoryCountry e depois divididas em dois grupos em cada SalesTerritoryCountry. Observe que ORDER BY na cláusula OVER ordena NTILE e que ORDER BY da instrução SELECT ordena o conjunto de resultados.

-- 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;  

Este é o conjunto de resultados.

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

Consulte Também

RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL)
Funções de classificação (Transact-SQL)
Funções internas (Transact-SQL)