Menu

Funciones de categoría 4/4, NTILE(n)

Septiembre 17, 2013 - SQL Server

Para finalizar nuestra serie de funciones de categoría entraremos en detalle de la función NTILE(n), esta función divide un resultado en grupos, la cantidad de grupos a crear será dada por el valor n. Si la cantidad de grupos no es divisible exactamente en el número de registros se crearan grupos con más o menos registros, ubicándose en orden ascendente.

Requisitos

Manos a la obra!

La sintaxis general para esta función en la siguiente:

NTILE(n) [PARTITION BY col] OVER(ORDER BY col)

Donde n se refiere a la cantidad de grupos que se desean crear sobre los registros. Para mostrar el funcionamiento de esta función utilizaremos la siguiente consulta:

SELECT
Production.Product.Name,
SUM(Sales.SalesOrderDetail.OrderQty) AS 'OrderQty',
Production.ProductCategory.Name AS 'Category'
FROM Production.ProductCategory
INNER JOIN Production.ProductSubcategory
ON ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
INNER JOIN Production.Product
ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
INNER JOIN Sales.SalesOrderDetail
ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
GROUP BY Production.Product.Name,
Production.ProductCategory.Name
HAVING SUM(Sales.SalesOrderDetail.OrderQty) > 6000

image

Tenemos un listado de ordenes mayores a 6000. Vamos a utilizar la función NTILE para dividir estos 6 registros en 4 grupos.

SELECT
Production.Product.Name,
SUM(Sales.SalesOrderDetail.OrderQty) AS 'OrderQty',
Production.ProductCategory.Name AS 'Category',
NTILE(4) OVER (ORDER BY SUM(Sales.SalesOrderDetail.OrderQty) DESC) AS 'Rank'
FROM Production.ProductCategory
INNER JOIN Production.ProductSubcategory
ON ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
INNER JOIN Production.Product
ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
INNER JOIN Sales.SalesOrderDetail
ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
GROUP BY Production.Product.Name,
Production.ProductCategory.Name
HAVING SUM(Sales.SalesOrderDetail.OrderQty) > 6000
ORDER BY Rank

image

Podemos observar que el resultado son 4 grupos, como 6 no es divisible entre 4 de manera exacta, SQL Server crear los dos primeros grupo de a 2 registros cada uno y luego dos grupos de a 1 cada uno (esta asignación al parecer la hace de manera aleatoria) teniendo en cuenta obviamente el criterio de ordenamiento que le especificamos.

PARTITION BY

Como todas las funciones de categoría que hemos visto a los largo de la serie, NTILE(n) también puede ser utilizada con PARTITION BY haciendo que la función NTILE(n) se ejecute para cada subconjunto de resultados:

SELECT
Production.Product.Name,
SUM(Sales.SalesOrderDetail.OrderQty) AS 'OrderQty',
Production.ProductCategory.Name AS 'Category',
NTILE(3) OVER (PARTITION BY Production.ProductCategory.Name ORDER BY SUM(Sales.SalesOrderDetail.OrderQty) DESC) AS 'Rank'
FROM Production.ProductCategory
INNER JOIN Production.ProductSubcategory
ON ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
INNER JOIN Production.Product
ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
INNER JOIN Sales.SalesOrderDetail
ON Production.Product.ProductID = Sales.SalesOrderDetail.ProductID
GROUP BY Production.Product.Name,
Production.ProductCategory.Name
HAVING SUM(Sales.SalesOrderDetail.OrderQty) > 6000
ORDER BY Production.ProductCategory.Name

image

Vemos que en cada subconjunto de resultados se crean la cantidad de grupos que solicitamos (En este caso 3), en la categoría Clothing no se crea un tercer grupo porque no se tiene esa cantidad de registros disponibles. Para este modo tambien aplica la regla de si no es divisible exactamente creara grupos con mas o menos registros.

Conclusión

Han sido 4 entradas explicando las funciones de categoría en SQL Server, a pesar de no ser utilizadas a diario si debemos entender su funcionamiento ya que realizar estas consultas sin estas funciones es posible pero bastante complicado, espero que hayan aprendido algo y como siempre…

Happy coding Sonrisa!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *