NTILE (Transact-SQL)

Artículo
06/18/2013

Distribuye las filas de una partición ordenada en un número especificado de grupos. Los grupos se numeran a partir del uno. Para cada fila, NTILE devuelve el número del grupo al que pertenece la fila.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

integer_expression
Es una expresión constante de tipo entero positivo que especifica el número de grupos en que se debe dividir cada partición. integer_expression puede ser de tipo int o bigint.
<partition_by_clause>
Divide el conjunto de resultados producido por la cláusula FROM en particiones a las que se aplica la función. Para la sintaxis de PARTITION BY, vea OVER (cláusula de Transact-SQL).
<order_by_clause>
Determina el orden en que los valores NTILE se asignan a las filas de una partición. Un entero no puede representar una columna cuando se usa la cláusula <order_by_clause> en una función de categoría.

Tipos de valor devueltos

bigint

Comentarios

Si el número de filas de una partición no es divisible por integer_expression, esto producirá grupos de dos tamaños que difieren en un miembro. Los grupos de mayor tamaño preceden a los grupos de menor tamaño en el orden especificado por la cláusula OVER. Por ejemplo, si el número total de filas es 53 y el número de grupos es cinco, los tres primeros grupos tienen 11 filas y los otros dos grupos tienen 10 filas cada uno. Por otra parte, si el número total de filas es divisible por el número de grupos, las filas se distribuyen por igual entre los grupos. Por ejemplo, si el número total de filas es 50 y hay cinco grupos, cada grupo contiene 10 filas.

Ejemplos

A.Dividir filas en grupos

En el ejemplo siguiente se dividen las filas en cuatro grupos de empleados según sus ventas anuales hasta la fecha. Puesto que el número total de filas no es divisible por el número de grupos, los dos primeros grupos tienen cuatro filas y los grupos restantes tienen tres filas cada uno.

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

El conjunto de resultados es el siguiente.

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.Dividir el conjunto de resultados mediante PARTITION BY

En el ejemplo siguiente se agrega el argumento PARTITION BY al código del ejemplo A. Primero se dividen por PostalCode y, a continuación, en cuatro grupos dentro de cada PostalCode. En el ejemplo también se declara una variable @NTILE\_Var y se utiliza esa variable para especificar el valor del 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

El conjunto de resultados es el siguiente.

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)