Krypton Solid

La última tecnología en raciones de 5 minutos

Cómo utilizar la función de rango en SQL Server 2005

SQL Server 2005 Transact-SQL incluye un conjunto de funciones que le permiten clasificar las filas en su conjunto de resultados. Incluyendo…

una o más de estas funciones de clasificación de SQL Server en la cláusula SELECT de su consulta, puede asignar automáticamente una clasificación a cada fila. La forma en que se clasifican las filas depende de la función que utilice.

Actualmente, T-SQL admite cuatro funciones de clasificación: ROW_NUMBER, RANK, DENSE_RANK y NTILE. Definiré estas funciones de rango en SQL Server y le mostraré cómo funcionan, pero primero veamos el entorno de prueba que usaré para demostrar estas funciones.

Para obtener los datos que necesitaba, utilicé el siguiente código para crear la tabla Sales.Quota en la base de datos de muestra de SQL Server 2005 AdventureWorks:

USE AdventureWorks
VAMOS
--Drop Sales.Quotas tabla si existe
SI OBJECT_ID (N'Sales.Quotas ', N'U') NO ES NULO
TABLA DROP Ventas Cuotas
VAMOS
--Crear tabla Sales.Quotas
SELECCIONE e.FirstName, e.LastName, q.SalesQuota AS Quota,
DATENAME (m, q.QuotaDate) AS [Month], AÑO (q.QuotaDate) AS [Year]
INTO Ventas.Cuotas
FROM Sales.SalesPersonQuotaHistory q
INNER JOIN HumanResources.vEmployee e
ON q.SalesPersonID = e.EmployeeID
DONDE SalesQuota ENTRE 210000 y 280000
ORDEN POR e.LastName, q.QuotaDate

Como puede ver, simplemente extraigo datos de un par de otras tablas en la base de datos para crear un conjunto de datos de prueba significativos.

Aquí está la declaración SELECT que uso para consultar la nueva tabla:

SELECCIONE
ROW_NUMBER () OVER (ORDER BY Quota DESC) AS [RowNumber],
RANK () OVER (ORDER BY Quota DESC) COMO [Rank],
DENSE_RANK () OVER (ORDER BY Quota DESC) COMO [DenseRank],
NTILE (5) OVER (ORDER BY Quota DESC) COMO [NTile],
Apellido, Cuota, [Month], [Year]
DESDE Ventas.Cuotas

La instrucción SELECT utiliza las cuatro funciones de clasificación para clasificar las filas. Incluyo todas las funciones en una declaración, por lo que puede comparar los resultados devueltos por cada función, como se muestra en el siguiente conjunto de resultados:

Numero de fila Rango DenseRank NTilo Apellido Cuota Mes Año
1 1 1 1 Campbell 280000,00 enero 2002
2 1 1 1 Vargas 280000,00 enero 2004
3 3 2 1 Campbell 267000,00 abril 2002
4 4 3 2 Vargas 266000,00 enero 2002
5 5 4 2 Ansman-Wolfe 264000,00 enero 2002
6 6 5 2 Jiang 263000,00 mes de julio 2003
7 7 6 3 Saraiva 247000,00 abril 2003
8 8 7 3 Vargas 244000,00 mes de julio 2001
9 9 8 3 Vargas 239000,00 enero 2003
10 10 9 4 Campbell 234000,00 enero 2004
11 11 10 4 Ansman-Wolfe 226000,00 octubre 2002
12 11 10 4 Campbell 226000,00 mes de julio 2001
13 13 11 5 Ansman-Wolfe 224000,00 abril 2003
14 14 12 5 Varkey Chudukatil 217000,00 enero 2003
15 15 13 5 Ansman-Wolfe 210000,00 mes de julio 2002

(15 hileras afectadas)

A medida que avanza en las siguientes secciones, consulte la instrucción SELECT y el conjunto de resultados según sea necesario para comprender mejor cómo funciona la función de clasificación.

Función ROW_NUMBER

La función ROW_NUMBER es la más básica de las funciones de clasificación. Como puede ver en el conjunto de resultados (la columna RowNumber), la función numera cada fila secuencialmente, comenzando con 1. Si vuelve a consultar la consulta, verá que el primer elemento en la cláusula SELECT es la función ROW_NUMBER. Cuando utilice esta función, primero especifique el nombre de la función, seguido de los paréntesis vacíos. No pasa ningún valor a la función.

Después de la función de clasificación, especifique la función OVER. Para esta función, pasa una cláusula ORDER BY como argumento. La cláusula especifica la columna (o columnas) que desea clasificar. En este caso, clasifico los valores en la columna Cuota, en orden descendente. Como resultado, las filas del conjunto de resultados se clasifican comenzando con la cantidad de cuota más alta. Si vuelve a consultar el conjunto de resultados, verá que la fila con el valor de Cuota más alto se clasifica como 1 y la fila con el valor más bajo está en el puesto 15. (El conjunto de resultados contiene 15 filas).

Eso es todo lo que hay que hacer para usar la función ROW_NUMBER, y las otras funciones de clasificación funcionan de la misma manera, solo que los resultados son ligeramente diferentes.

Función RANK en SQL Server

La siguiente función de clasificación en la lista SELECT es RANK. Una vez más, especifica el nombre de la función, seguido de la función OVER, que nuevamente incluye la cláusula ORDER BY. Sin embargo, como puede ver en el conjunto de resultados (la columna Clasificación),

los valores clasificados son ligeramente diferentes a los que vio para la función ROW_NUMBER. Sí, el valor de cuota más alto está clasificado como 1, pero, debido a que dos filas comparten el mismo valor más alto, ambas están clasificadas como 1.

Cuando utiliza la función RANK, todos los valores compartidos se clasificarán de la misma manera. Pero observe que el valor de rango en sí se basa en la posición de la fila en el conjunto de resultados, no en el número secuencial de la fila. Por ejemplo, el valor de la cuota en la tercera fila es 267.000. Ese es el segundo valor de cuota más alto, pero debido a que cae en la tercera fila, recibe una clasificación de 3, en lugar de 2. La función RANK omite el 2 porque la segunda fila coincide con la primera fila. Si la cuarta fila compartiera el mismo valor que la tercera fila, también se clasificaría como 3. Pero debido a que el valor es menor y está en la cuarta fila, se clasificará como 4.

Función DENSE_RANK

La función DENSE_RANK tiene un enfoque diferente. Al igual que la función RANK, a las dos primeras filas se les asigna un valor de 1. Sin embargo, la función DENSE_RANK usa numeración secuencial, en lugar de vincular el rango al número de fila. Como resultado, a la tercera fila se le asigna un valor de 2 porque la columna Cuota contiene el segundo valor más alto y a la cuarta fila se le asigna un valor de 3 porque es el tercer valor más alto, y así sucesivamente.

Las funciones ROW_NUMBER, RANK y DENSE_RANK son similares en la forma en que devuelven resultados. La diferencia está en si la numeración es secuencial y si está vinculada al número de fila. La función NTILE, sin embargo, es un poco diferente a estas tres funciones.

Función NTILE

Si vuelve a consultar la instrucción SELECT, puede ver que cuando especifica la función NTILE, pasa un número entero como argumento a la función, a diferencia de las otras funciones de clasificación donde no pasa ningún argumento. La función NTILE divide el conjunto de resultados

Visite nuestro foro de intercambio de conocimientos de TI:
  • «¿Cómo puedo escribir un script SQL para encontrar los 10 días más ocupados del mes?» Obtenga la respuesta de un experto.
  • Haga su pregunta sobre SQL Server.

en el número de grupos especificados por este argumento. Por ejemplo, en la instrucción SELECT, especifico 5, lo que significa que el conjunto de resultados se dividirá en cinco grupos. Como hay 15 filas en el conjunto de resultados, cada grupo contendrá tres filas. Las filas se agrupan según el valor de la columna Cuota.

Como resultado, las tres filas con los valores de Cuota más altos están en el primer grupo y reciben una clasificación de 1. Las tres filas con los siguientes valores de Cuota más altos están en el segundo grupo y reciben una clasificación de 2. y así sucesivamente. Debido a que solo hay cinco grupos, la clasificación más alta es 5, que se asigna al grupo con los tres valores de Cuota más bajos. Nuevamente, consulte el conjunto de resultados para comprender mejor cómo la función NTILE agrupa los datos y luego clasifica a cada grupo.

Como puede ver, las funciones de clasificación en SQL Server son bastante sencillas y hacen que la clasificación de su conjunto de resultados sea un proceso relativamente simple. Cada función le ofrece una forma ligeramente diferente de clasificar el conjunto de resultados. Para obtener más información sobre cualquiera de estas funciones, consulte los Libros en pantalla de Microsoft SQL Server.

SOBRE EL AUTOR
Robert Sheldon es consultor técnico y autor de numerosos libros, artículos y material de capacitación relacionados con Microsoft Windows, varios sistemas de administración de bases de datos relacionales y diseño e implementación de inteligencia empresarial. Puede encontrar más información en http://www.rhsheldon.com.

Deja un comentario

También te puede interesar...

Definición de ventas brutas

¿Qué son las ventas brutas? Las ventas brutas son una medida de las ventas totales de una empresa, no ajustadas por los costos de generar esas ventas. La fórmula de ventas brutas se calcula agregando

Definición de multa por pago insuficiente

¿Qué es una multa por pago insuficiente? Se impone una sanción fiscal a una persona física o jurídica por no pagar lo suficiente del impuesto total estimado y la retención adeudada. Si una persona tiene

Definición de préstamo de dinero duro

¿Qué es un préstamo de dinero pesado? Un préstamo de dinero duro es un tipo de préstamo garantizado por bienes raíces. Los préstamos en efectivo se consideran préstamos de «último recurso» o préstamos puente a

Cascada vs.Desarrollo ágil: un estudio de caso

Si pudieras hacer el mismo proyecto con el mismo grupo de personas, primero usando una metodología en cascada y luego usando una metodología Agile, ¿cuáles crees que serían los resultados? Bueno, aunque no es exactamente

Telstra pierde oferta para duplicar el costo mayorista

El Tribunal de competencia australiano (ACT) ayer rechazó los esfuerzos de Telstra para duplicar el precio mayorista para acceder a él. Servicio de bucle local incondicional (ULLS) en áreas metropolitanas. Telstra había propuesto un aumento

Definición de venta por propietario (FSBO)

¿Qué está a la venta por el propietario (FSBO)? Landlord Sale (FSBO) es un término que se refiere a un método de listar una propiedad para la venta. Cuando una casa tiene una lista FSBO,

Cómo eliminar cuentas de Poste Italiane

Después del registro en correo italiano, ¿has notado que no usas mucho el servicio y por lo tanto te gustaría saber cómo cancelar tu cuenta? ¿Notó que accidentalmente creó dos cuentas en el sitio web

Productos de seguridad online para Japón

Las soluciones de protección de identidad tienen como objetivo proteger el comercio electrónico japonés del fraude en línea. JAPÓN – Safewww Inc., un proveedor de soluciones de autenticación y protección de identidad para transacciones comerciales

Definición de la brecha de implementación

¿Cuál es el retraso de implementación? La brecha de implementación es la demora entre un evento macroeconómico adverso y la implementación de una respuesta de política fiscal o monetaria por parte del gobierno y el

Definición de peonza de velas

¿Qué es un candelabro Spinning Top? Una encimera giratoria es un patrón de velas que tiene un cuerpo realmente corto, que está centrado verticalmente entre las sombras superior e inferior alargadas. El patrón de velas