RSS

Archivo de la categoría: SQL Server

Funciones Ranking Microsoft SQL Server

FuncionesRanking

MartinLutherKing

“Hemos aprendido a volar como los pájaros, a nadar como los peces, pero no hemos aprendido el arte de vivir juntos, como hermanos.” – Martin Luther King (1929-1968) Pastor Estadounidense.

Las funciones de categoría devuelven un valor de categoría para cada fila de una partición. Según la función que se utilice, algunas filas pueden recibir el mismo valor que otras. Las funciones de categoría son no deterministas. Muchas personas ya utilizan y conocen estas funciones, pero en algunos casos otros no… a estos les escribo.

Esta característica está disponible desde la version de SQL Server 2005, ya hace un tiempo estas características benefician tanto a administradores de datos como a desarrolladores orientadas a resolver necesidades de la era moderna y logrando en lo mayor posible satisfacer eficientemente las diversas necesidades y requerimientos en el tratamiento de la data empresarial. Las funciones de ranking las cuales sirven para definir diferentes maneras de enumeración secuencial para los resultados, es decir, rankear un set de resultados.

Entre los diferentes usos tenemos por ejemplo, el poder enumerar secuencialmente cada fila de los resultados, también enumerar secuencialmente grupos para un set de resultados, es decir, aquí se introduce funcionalidad para usar expresiones de ranking para un conjunto de resultados. Esta característica podemos explotarla para ciertos escenarios, por ejemplo, cuando en nuestras aplicaciones .NET se requiera paginar, clasificar y ordenar los resultados en una grilla (GridView).

Anteriormente se podía usar TOP para rankear resultados, pero no podíamos especificar el orden del ranking, y si requeríamos dicha funcionalidad se tenía que crear ciertos algoritmos específicos para lograrlo. Con las funciones de ranking podemos hacer esta tarea mucho más fácil.

Las funciones de ranking son 4: ROW_NUMBER(), RANK (), DENSE_RANK() Y NTILE(integer_expression), cuyas sintaxis pasamos a verlas:

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>) RANK () OVER ([<partition_by_clause>] <order_by_clause>) DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>) NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

Estas expresiones de ranking de basan en ciertos algoritmos que serán aplicados a una columna específica, y donde en todo momentos se usarán en combinación con las cláusulas PARTITION BY y ORDER BY.

Función ROW_NUMBER

Devuelve el número secuencial de una fila dentro de una partición de un conjunto de resultados, comenzando con 1 para la primera fila de cada partición.

Por ejemplo:

USE AdventureWorks2012; 
GO
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, 
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD" 
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

 El conjunto de resultados es el siguiente:

Ranking_1

Opcionalmente podemos usar la cláusula PARTITION BY con cada una de las funciones ranking.

En el ejemplo siguiente se usa el argumento PARTITION BY para crear particiones del conjunto de resultados de la consulta por la columna TerritoryName.La cláusula ORDER BY especificada en la cláusula OVER ordena las filas de cada partición por la columna SalesYTD.La cláusula ORDER BY de la instrucción SELECT ordena todo el conjunto de resultados de la consulta por TerritoryName.

USE AdventureWorks2012;

GO

SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1),

ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0

ORDER BY TerritoryName;

El conjunto de resultados es el siguiente.

Ranking_2

Función RANK

Rankea los datos de acuerdo a lo que se especifique para la cláusula ORDER BY, y lo hace rankeando verdaderamente la enumeración los resultados. Se diferencia de ROW_NUMBER() en lo siguiente. ROW_NUMBER() enumera así: 1,2,3,4,5… cada fila, en cambio RANK() no tendría por qué hacerlo necesariamente de la misma manera, pudiendo enumerar así: 1,2,4,4,7,8,8,8,9…

Por ejemplo:

USE AdventureWorks2012;

GO

SELECT i.ProductID, p.Name, i.LocationID, i.Quantity

,RANK() OVER

(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank

FROM Production.ProductInventory AS i

INNER JOIN Production.Product AS p

ON i.ProductID = p.ProductID

WHERE i.LocationID BETWEEN 3 AND 4

ORDER BY i.LocationID;

GO

Ranking_3

Función DENSE_RANK

Es parecido a RANK(), y se diferencia en que no produce saltos en la enumeración de los conjuntos de resultados. Es decir, la enumeración sería así: 1,2,2,3,4,5,5,6,7,8,8,9. Enumeración repetida pero secuencial, sin saltar números.

 
USE AdventureWorks2012;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO

 Ranking_4

Función NTILE

Esta función lo que hace es limitar la numeración máxima de los resultados, por ejemplo, si es especifica NTILE(3000) entonces la numeración será hasta 3000 a partir, obviamente, desde 1.

Por ejemplo:

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

 Ranking_5

Para finalizar, cabe destacar que a todas las funciones se le puede la cláusula PARTITION BY y que las mismas pueden trabajar en juntas como muestra el siguiente ejemplo.

— Trabajando las funciones Ranking todas USE AdventureWorks2012;

GO

SELECT p.FirstName, p.LastName

,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS “Row Number”

,RANK() OVER (ORDER BY a.PostalCode) AS Rank

,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS “Dense Rank”

,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile

,s.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;

FuncionesRanking

Con estas funciones se pueden crear muchas combinaciones y las mismas vienen a ayudar sobremanera a administradores de bases de datos y desarrolladores. A solucionar problemas que antes se consideraban complicados de una manera sumamente sencilla.

Espero aprendieras algo nuevo con este artículo y que fuera lo bastante sencillo, escribeme o deja tu comentario sobre cualquier duda o preguntas sobre este tema.

 

Anuncios
 
Deja un comentario

Publicado por en enero 8, 2013 en Microsoft, SQL Server

 

SQL SERVER – Descargar SQL Server 2012 Developer Training Kit – Actualización de julio 2012

La oxidación por falta de uso gasta mucho más las herramientas que el propio trabajo. 
-Benjamin Franklin

Hola, una vez más navegando por la web el encontre que esta disponible una nueva actualización para la instalación de SQL Server 2012 Developer Kit. El cual es muy conveniente y hay muchos prefieren descargar kit de instalación en lugar del instalador web. Este kit ya es un recurso único, que ofrece una visión completa del producto en pocas palabras. Un desarrollador puede aprender de muchos lugares – libros, webcasts, tutoriales, blogs, etc Sin embargo, he encontrado que los kits de desarrollador de formación son el mejor punto de partida para cualquier producto. Comience con ellos primero, ver cuáles son las nuevas características y cuál es el mensaje de un nuevo producto viene con. El SQL Server 2012 Developer Training Kit incluye contenido técnico con laboratorios, demostraciones y presentaciones diseñadas para ayudarle a aprender cómo desarrollar SQL Server 2012 de base de datos y soluciones de BI. El contenido nuevo y actualizado se harán públicos periódicamente y se puede descargar on-demand usando el instalador web.

Aquí está la actualización Descargar SQL Server 2012 Developer Training Kit (actualizado en julio de 2012)

Developer kit también contiene video como así también detalles de cómo ejecutar los laboratorios también. Sugiero todos los entusiastas de SQL Server debe descargar este kit de desarrollo e instalarlo. Creo que he probado la mayoría de los laboratorios, así visto todos los vídeos de este kit de entrenamiento. Sin embargo, cada vez que vea este video otra vez que aprendo algo nuevo, que no he encontrado antes. Me gustaría saber su opinión acerca de lo que es su característica favorita en SQL Server 2012 y cuál es su método preferido de aprendizaje de SQL Server.

Referencia: Pinal Dave ( http://blog.sqlauthority.com )

 
1 comentario

Publicado por en agosto 20, 2012 en SQL Server

 

En este Blog…


“Comienzo con la premisa de que la función del líder es producir más líderes, no más 
seguidores.” - Ralph Nader

Hoy he puesto en marcha este blog que se dedica a comentar sobre el tema de la inteligencia de negocios (Business Intelligence), un tema nada original ya que existe mucha información al respecto, pero aunque nada original no me parece disparatada la idea de tratar este tema ya que esta tecnología cada día cobra mas fuerza en las organizaciones que tienen visión y que realizan en base esta su planificación estratégica.
En el mismo voy compartir mis experiencias y pensamientos acerca de las herramientas para la creación de soluciones la inteligencia de negocios y las tendencias de este mercado en Republica Dominicana.
La característica principal de este blog es que utilizando un lenguaje llano y lo menos técnico posible estaré comentando acerca de la inteligencia de negocios, haciendo posible que lo entiendan los informáticos y para hacer las cosas un poco más simples para los no tan informáticos y que de esta manera puedan tener una idea de esta tecnología, ya que la inteligencia de negocios a diferencia de otras áreas de la tecnología de la información no es una herramienta del uso y consumo propio de los profesionales informáticos. Por lo tanto, debería ser una tecnología manejada en todos los niveles por toda la organización.
Me decidí a empezar este blog ya que considero que debo aportar mi granito de arena para que esta tecnología pueda ser de provecho para las organizaciones que aun no dan ese paso, espero que poco a poco que este blog se convierta en mi principal herramienta relacionada a mi actividad profesional.
Por último quisiera recordarte dejar siempre tus comentarios y preguntas acerca de los temas expuestos en el blog.

¿Quieres preguntarme?