Volver a la lista de artículos Artículos
Lectura de 16 minutos

Cómo Clasificar Filas en SQL: Una Guía Completa

Author's photo
Kamil Bladoszewski

Índice

No te preocupes más por las funciones RANK de SQL. Este artículo le guiará a través de los casos de uso más comunes de los rankings.

Los rankings se utilizan a nuestro alrededor todos los días. Los torneos deportivos, las mejores películas y series de televisión en Netflix, las tiendas con los productos más baratos... estos son sólo algunos ejemplos de clasificaciones que puede haber visto recientemente.

Utilizar las funciones RANK de SQL puede ser difícil a veces. La variedad de construcciones diferentes es enorme. Puedes perderte fácilmente en todo lo que hay que aprender sobre la clasificación. La clave es entender los conceptos importantes y saber dónde buscar información adicional.

En esta guía, encontrarás los conocimientos que te permitirán escribir muchos tipos de consultas de clasificación SQL. Primero, explicaré cómo funciona la clasificación. Luego, mostraré un montón de ejemplos. Si está buscando casos de uso específicos, puede simplemente sumergirse en la sección Casos de Uso de la Clasificación SQL.

Las funciones de clasificación de SQL son Funciones de ventana

Empecemos por lo básico. ¿Qué son exactamente las funciones de clasificación en SQL? Son parte de una familia de funciones llamadas funciones de ventana. Las funciones de ventana utilizan un ámbito (ventana), que mira una parte de los datos para calcular el resultado. A continuación, se desplaza a otra parte de los datos y calcula el resultado de esa porción. Puede tener una idea básica de cómo funciona en Ejemplo de Función de Ventana SQL con Explicaciones.

Aunque hay muchas funciones de ventana diferentes, sólo me centraré en la clasificación. Tampoco explicaré en profundidad el funcionamiento interno de las funciones de ventana. Este artículo se centrará en las funciones de clasificación de SQL y en cómo utilizarlas en diferentes situaciones. Para aprender más sobre las funciones de ventana, consulte este Funciones de ventana curso.

Funciones Básicas de Clasificación

Los tipos más comunes (y simples) de funciones de clasificación son

  • RANK()
  • DENSE_RANK()
  • ROW_NUMBER()

¿Qué hacen y en qué se diferencian unas de otras? Voy a explicar brevemente cada una de ellas. Luego, compararé las clasificaciones de cada una de las funciones.

La función RANK() crea una clasificación de las filas basada en una columna proporcionada. Comienza asignando un "1" a la primera fila en el orden y luego da números más altos a las filas inferiores en el orden. Si las filas tienen el mismo valor, se clasifican igual. Sin embargo, el siguiente puesto se desplaza en consecuencia. Por ejemplo, si dos filas son la 5ª (tienen el mismo rango), la siguiente fila será la 7ª (es decir, la 6ª no existe).

La función DENSE_RANK() es bastante similar. La única diferencia es que no deja huecos en la clasificación. Aunque más de una fila puede tener el mismo rango, la siguiente fila obtendrá el siguiente rango. Por ejemplo, si dos filas tienen el 5º puesto, la siguiente fila tendrá el 6º.

La función ROW_NUMBER() es diferente. Si las filas tienen el mismo valor, todas obtendrán clasificaciones consecutivas (no la misma clasificación que con las funciones anteriores). Por ejemplo, si dos filas tienen el mismo valor (ambas estarían en 5º lugar con las funciones anteriores), ROW_NUMBER() las colocaría en 5º y 6º lugar.

Ahora bien, podría preguntarse: Si varias filas tienen el mismo valor y estamos utilizando ROW_NUMBER(), ¿cómo podemos saber qué fila será la primera, la segunda, etc.? La respuesta es bastante sombría: ¡no podemos! Depende de muchos factores, y no se puede predecir qué fila obtendrá qué rango.

Estas son las funciones de clasificación SQL básicas y (probablemente) las más utilizadas. Si quieres conocer otras funciones de clasificación, puedes leer esta visión general de las funciones de clasificación.

RANK() vs. DENSE_RANK() vs. ROW_NUMBER()

Ahora que conoces la teoría de las funciones de clasificación, vamos a comparar los resultados de estas funciones. Para ello, clasifiquemos a algunas personas en función de las puntuaciones de sus exámenes. Observa los datos (tabla exam_result):

first_namelast_namepoints
EmyrDownes70
DinaMorin70
Evie-MayBoyer80
NoraParkinson60
TrystanOconnor40
ErykMyers90
MarleneDuncan90
MariusPowell90
JoanneGoddard50
RayhanWilliamson90

Vamos a crear una consulta que ordena las filas por la columna de puntos utilizando las funciones de clasificación descritas anteriormente:

SELECT
  RANK() OVER(ORDER BY points DESC) AS rank,
  DENSE_RANK() OVER(ORDER BY points DESC) AS dense_rank,
  ROW_NUMBER() OVER(ORDER BY points DESC) AS row_number,
  first_name,
  last_name,
  points
FROM exam_result;

Vamos a analizar el código antes de ejecutarlo para ver los resultados. La primera columna que queremos mostrar es rank. Será simplemente el ranking creado usando la función RANK(). Sin embargo, ¿qué significa OVER(ORDER BY points DESC)? Es parte del concepto de función ventana. OVER() es una parte obligatoria de todas las funciones de clasificación (en realidad, de todas las funciones ventana). Indica a la función el ámbito de los datos (ventana) y el orden en que se colocarán las filas. En este caso, sólo proporcionamos el orden. Puede encontrar un ejemplo de definición de una ventana en la sección RANK() OVER(PARTITION BY ...)-Singular columna.

Como ahora entiende la primera columna, también debería entender las dos siguientes. La única diferencia es el nombre de las funciones de clasificación. Además, las tres últimas columnas deberían explicarse por sí mismas, ya que ha visto los datos.

Bien, veamos los resultados:

rankdense_rankrow_numberfirst_namelast_namepoints
111MarleneDuncan90
112RayhanWilliamson90
113MariusPowell90
114ErykMyers90
525Evie-MayBoyer80
636EmyrDownes70
637DinaMorin70
848NoraParkinson60
959JoanneGoddard50
10610TrystanOconnor40

¿Puede identificar las diferencias entre cada función? En las primeras filas, las columnas rank y dense_rank parecen iguales. Sin embargo, row_number parece diferente desde el principio. Aunque Marlene, Rayhan, Marius y Eryk tienen la misma cantidad de puntos, la función ROW_NUMBER() les asignó valores distintos. Como se ha mencionado anteriormente, el orden en el que estas personas obtuvieron su rango no es determinista.

Cabe destacar que se puede hacer que el resultado de ROW_NUMBER() sea más determinista. Simplemente hay que añadir más columnas al orden (por ejemplo, ROW_NUMBER() OVER (ORDER BY points DESC, last_name ASC)). Esto se explicará con más detalle en la sección Rango sobre múltiples columnas.

También puede hacer que ROW_NUMBER() sea aún menos determinista. Aunque la parte ORDER BY es una necesidad para RANK() y DENSE_RANK(), ROW_NUMBER() no la requiere en absoluto. Así es como puede numerar las filas sin ningún orden específico.

Otra diferencia puede verse más abajo en la tabla. Evie-May ocupa el quinto lugar en la función RANK() y el segundo en la función DENSE_RANK(). La primera función observa cuántas filas tienen la misma clasificación y la siguiente fila se clasifica en consecuencia. La segunda función simplemente mira el valor anterior y la siguiente fila recibe la siguiente clasificación.

Tómese un momento para ver las clasificaciones anteriores y asegúrese de que entiende la diferencia entre estas tres funciones de clasificación SQL. Es importante saber qué función utilizar en cada caso. Si necesita más explicaciones, consulte Cómo utilizar las funciones de clasificación en SQL.

Casos de uso de la clasificación SQL

Es hora de sumergirse en algunas consultas de SQL RANK. Comenzaremos con algunas consultas simples y gradualmente aumentaremos la complejidad. El plan es el siguiente:

Tenga en cuenta que aunque utilizaremos principalmente la función RANK() de SQL, los ejemplos pueden aplicarse a DENSE_RANK() y ROW_NUMBER(). Depende de sus necesidades. Por lo tanto, es importante entender las diferencias entre estas funciones.

En los ejemplos, utilizaremos los datos modificados del ejemplo anterior. La columna exam_date muestra cuándo la persona hizo el examen, y la columna de la ciudad muestra en qué city la persona hizo el examen. Echa un vistazo:

first_namelast_nameexam_datecitypoints
EmyrDownes2018-12-18San Francisco70
DinaMorin2019-01-17Los Angeles70
Evie-MayBoyer2019-01-23San Francisco80
NoraParkinson2019-02-16San Diego60
TrystanOconnor2019-02-28Los Angeles40
ErykMyers2019-06-07San Francisco90
MarleneDuncan2019-06-13San Diego90
MariusPowell2019-11-13San Diego90
JoanneGoddard2019-12-18San Diego50
MariusWilliamson2020-01-02San Diego90

Rango sobre una sola columna

Empezaremos con el caso de uso más sencillo: crear una clasificación basada en una sola columna. Basaremos nuestro ranking en los puntos:

SELECT
  RANK() OVER(ORDER BY points DESC) AS ranking,
  first_name,
  last_name,
  points
FROM exam_result;

La consulta es bastante simple, como el ejemplo anterior. Crea una clasificación basada en el número de puntos en orden descendente. Usamos RANK() OVER(ORDER BY ...) para indicar qué columna debe usarse para el ordenamiento. El resultado es el siguiente:

rankingfirst_namelast_namepoints
1MarleneDuncan90
1MariusWilliamson90
1MariusPowell90
1ErykMyers90
5Evie-MayBoyer80
6EmyrDownes70
6DinaMorin70
8NoraParkinson60
9JoanneGoddard50
10TrystanOconnor40

Si desea crear una clasificación basada en una columna diferente o en un orden diferente (es decir, ascendente en lugar de descendente), simplemente cambie el nombre de la columna o cambie la palabra clave DESC por la palabra clave ASC.

Ordenar sobre múltiples columnas

Utilizar múltiples columnas para ordenar también es sencillo. Sólo tiene que añadir el nombre de la siguiente columna después de la coma. Si los valores de la primera columna son iguales, entonces se toma en consideración la segunda columna, y así sucesivamente. Eche un vistazo:

SELECT
  RANK() OVER(ORDER BY points DESC, first_name ASC) AS ranking,
  first_name,
  last_name,
  points
FROM exam_result;

Esta consulta es similar a la anterior. Hemos añadido first_name ASC a la cláusula de ordenación. De esta forma, si varias filas tienen el mismo valor en la primera columna, se toma en consideración la segunda. Tenga en cuenta que si la segunda columna es la misma, la clasificación se resuelve en base a la función que estamos utilizando (en nuestro caso RANK()). Observa el resultado:

rankingfirst_namelast_namepoints
1ErykMyers90
2MariusWilliamson90
2MariusPowell90
4MarleneDuncan90
5Evie-MayBoyer80
6DinaMorin70
7EmyrDownes70
8NoraParkinson60
9JoanneGoddard50
10TrystanOconnor40

Como puedes ver, las personas con el mismo número de puntos obtienen diferentes clasificaciones porque tienen nombres diferentes. Las únicas excepciones son las dos personas llamadas Marius. Como tienen el mismo nombre y el mismo número de puntos, tienen la misma clasificación.

Vale la pena mencionar que la ordenación por algunas columnas puede ser útil si se quiere que el resultado de la función ROW_NUMBER() sea determinista. Observe esta consulta:

SELECT
  ROW_NUMBER() OVER(ORDER BY points DESC, last_name ASC) AS ranking,
  first_name,
  last_name,
  points
FROM exam_result;

Dado que todos nuestros datos tienen un apellido diferente, podemos añadirlo a la cláusula de ordenación para poder predecir quién obtiene qué rango. Fíjate:

rankingfirst_namelast_namepoints
1MarleneDuncan90
2ErykMyers90
3MariusPowell90
4MariusWilliamson90
5Evie-MayBoyer80
6EmyrDownes70
7DinaMorin70
8NoraParkinson60
9JoanneGoddard50
10TrystanOconnor40

En este caso, la función ROW_NUMBER() funciona como RANK() y DENSE_RANK() porque cada fila puede colocarse de forma determinista. Sin embargo, ten en cuenta que si hubiera personas con el mismo apellido, la función ROW_NUMBER() sería no determinista aunque estemos ordenando por dos columnas.

RANK() con los 10 primeros resultados

A menudo creamos clasificaciones para mostrar los resultados más importantes (por ejemplo, los 10 primeros, los 100 primeros, etc.). Como es de esperar, se pueden mostrar los principales resultados utilizando funciones de clasificación de SQL. Sin embargo, una consulta de este tipo es un poco más complicada. Eche un vistazo:

SELECT
  *
FROM (
  SELECT
    RANK() OVER(ORDER BY points DESC) AS ranking,
    first_name,
    last_name,
    points
  FROM exam_result
) AS a
WHERE ranking <= 3;

En esta consulta, utilizamos una subconsulta para calcular la clasificación y, a continuación, en la consulta principal, filtramos los resultados para que sólo se muestren los tres primeros. Los resultados pueden sorprenderle:

rankingfirst_namelast_namepoints
1MarleneDuncan90
1MariusWilliamson90
1MariusPowell90
1ErykMyers90

Como puede ver, los resultados muestran cuatro filas, y cada una de ellas tiene el mismo valor en la columna de clasificación. Esto se debe a nuestros datos. Las cuatro filas tienen el mismo número de puntos y, por tanto, obtienen el mismo rango en la subconsulta.

Para escribir una consulta de este tipo por su cuenta, ponga su consulta de clasificación dentro de una subconsulta, y escriba un filtro alrededor de ella. Por ejemplo, para mostrar el 10, cambie WHERE ranking <= 3 por WHERE ranking <= 10.

También vale la pena observar lo que ocurre cuando se utiliza DENSE_RANK() con estos datos. Eche un vistazo a la siguiente consulta y sus resultados:

SELECT
  *
FROM (
  SELECT
    DENSE_RANK() OVER(ORDER BY points DESC) AS ranking,
    first_name,
    last_name,
    points
  FROM exam_result
) AS a
WHERE ranking <= 3;
rankingfirst_namelast_namepoints
1MarleneDuncan90
1MariusWilliamson90
1MariusPowell90
1ErykMyers90
2Evie-MayBoyer80
3EmyrDownes70
3DinaMorin70

Como puede ver, ¡hay muchas filas en el resultado! Una vez más, se debe a la forma en que DENSE_RANK() clasifica cada fila. Este ejemplo muestra claramente que es importante entender la diferencia entre cada función y saber cuál usar en una situación determinada.

Clasificación por fecha

SELECT
  RANK() OVER(ORDER BY exam_date ASC) AS ranking,
  first_name,
  last_name,
  exam_date
FROM exam_result;

En este caso, simplemente estamos utilizando la columna exam_date para ordenar. Hay muy poca diferencia entre ordenar por fecha y por cualquier otra columna. Eche un vistazo a los resultados:

rankingfirst_namelast_nameexam_date
1EmyrDownes2018-12-18
2DinaMorin2019-01-17
3Evie-MayBoyer2019-01-23
4NoraParkinson2019-02-16
5TrystanOconnor2019-02-28
6ErykMyers2019-06-07
7MarleneDuncan2019-06-13
8MariusPowell2019-11-13
9JoanneGoddard2019-12-18
10MariusWilliamson2020-01-02

Lo importante es recordar que ASC (ascendente) en el caso de las fechas significa que la más antigua se colocará primero. En el orden DESC (descendente), la fecha más reciente se colocará en primer lugar.

Ordenación por meses

También puede querer ordenar las filas utilizando sólo una parte de la fecha (por ejemplo, el mes). No es tan difícil si conoce las funciones de fecha. Eche un vistazo a la siguiente consulta:

SELECT
  RANK() OVER(ORDER BY EXTRACT('year' FROM exam_date) ASC,
    EXTRACT('month' FROM exam_date) ASC) AS ranking,
  first_name,
  last_name,
  exam_date
FROM exam_result;

Estamos utilizando la función EXTRACT() para extraer una parte determinada de la fecha. Primero, queremos ordenar las filas por año y luego por mes. Por lo tanto, estamos usando primero EXTRACT('year' FROM exam_date) y luego EXTRACT('month' FROM exam_date). Echa un vistazo a los resultados:

rankingfirst_namelast_nameexam_date
1EmyrDownes2018-12-18
2Evie-MayBoyer2019-01-23
2DinaMorin2019-01-17
4NoraParkinson2019-02-16
4TrystanOconnor2019-02-28
6MarleneDuncan2019-06-13
6ErykMyers2019-06-07
8MariusPowell2019-11-13
9JoanneGoddard2019-12-18
10MariusWilliamson2020-01-02

EXTRACT() es una función bastante simple. Le dices qué parte de la fecha quieres extraer de la fecha. Hay muchas partes de fecha diferentes que puedes usar. Lo mejor es consultar la documentación de tu sistema de gestión de bases de datos (SGBD). Las partes más comunes son:

  • 'year'
  • 'month'
  • 'day'
  • 'hour'
  • 'minute'
  • 'second'

También cabe mencionar que no todos los SGBD soportan esta función. Los que sí lo hacen son PostgreSQL y MySQL. En SQL Server, esta función se llama DATEPART().

Las operaciones de fecha y hora son un tema bastante difícil, que está fuera del alcance de este artículo. Si quieres saber más sobre la función EXTRACT() y muchas más, consulta el Funciones estándar de SQL curso. Tiene toda una sección dedicada a las operaciones de fecha y hora.

Clasificación con GROUP BY

Ahora, veremos cómo utilizar los rankings con funciones de agregación. Aunque suene aterrador, con la comprensión adecuada, es un concepto bastante lógico. Su base de datos calcula primero las funciones agregadas y luego crea una clasificación basada en los valores calculados. Observe este ejemplo con AVG():

SELECT
  RANK() OVER(ORDER BY AVG(points) DESC) AS ranking,
  city,
  AVG(points) AS average_points
FROM exam_result
GROUP BY city;

Como puede ver, esta consulta no es tan diferente de las otras consultas que hemos visto hasta ahora. Simplemente puede utilizar funciones de agregación dentro de funciones de clasificación. Lo importante es recordar el uso de la cláusula GROUP BY. Como se ha mencionado anteriormente, las funciones agregadas se calculan primero. Esto significa que con GROUP BY, sólo puede utilizar funciones agregadas o las expresiones por las que está agrupando dentro de la función de clasificación.

Por ejemplo, si quiere utilizar otra columna para ordenar, de modo que las filas se ordenen por esta otra columna si el número medio de puntos es el mismo, tendría que incluir esta otra columna en la cláusula GROUP BY.

La consulta anterior devuelve el número medio de puntos obtenidos por las personas de cada ciudad.

rankingcityaverage_points
1San Francisco80
2San Diego76
3Los Angeles55

Clasificación con COUNT()

Ahora veremos una función agregada muy utilizada:COUNT():

SELECT
  RANK() OVER(ORDER BY COUNT(*) DESC) AS ranking,
  city,
  COUNT(*) AS exam_takers
FROM exam_result
GROUP BY city;

Como puede ver, esta consulta no es muy diferente de la anterior. Todas las funciones agregadas se utilizan con funciones de clasificación de la misma manera.

Los resultados son los siguientes:

rankingcityexam_takers
1San Diego5
2San Francisco3
3Los Angeles2

La consulta calcula el número de personas que hicieron el examen en cada ciudad y luego crea un ranking basado en este valor.

Clasificación con SUM()

Otra función agregada muy popular es SUM(). También es bastante sencilla de utilizar:

SELECT
  RANK() OVER(ORDER BY SUM(points) DESC) AS ranking,
  city,
  SUM(points) AS total_points
FROM exam_result
GROUP BY city;

Una vez más, simplemente utilizamos la función SUM() con la función RANK() de SQL.

rankingcitytotal_points
1San Diego380
2San Francisco240
3Los Angeles110

Esta vez, calculamos el número total de puntos adquiridos por las personas de cada ciudad.

RANK() OVER(PARTITION BY ...)-Columna única

Espero que esté preparado para abordar un concepto de clasificación más avanzado: PARTITION BY. Le permite crear clasificaciones en grupos separados. Eche un vistazo a esta consulta:

SELECT
  RANK() OVER(PARTITION BY city ORDER BY points DESC) AS ranking,
  city,
  first_name,
  last_name,
  points
FROM exam_result;

En esta consulta, estamos utilizando PARTITION BY con una sola columna para crear un ranking de personas en cada ciudad. De este modo, podemos ver las personas con las puntuaciones más altas en cada ciudad. Echa un vistazo a los resultados:

rankingcityfirst_namelast_namepoints
1San FranciscoErykMyers90
2San FranciscoEvie-MayBoyer80
3San FranciscoEmyrDowes70
1Los AngelesDinaMorin70
2Los AngelesTrystanOconnor40
1San DiegoMarleneDuncan90
1San DiegoMariusPowell90
1San DiegoMariusWilliamson90
4San DiegoNoraParkinson60
5San DiegoJoanneGoddard50

Como puedes ver, las clasificaciones se calculan por separado para cada ciudad. De este modo, Eryk y Dina obtienen la misma clasificación, aunque hayan obtenido puntuaciones diferentes. Asimismo, Dina y Emyr tienen las mismas puntuaciones, pero Dina está mejor clasificada porque hizo el examen en una ciudad diferente.

En algunos casos, la PARTICIÓN POR es un concepto importante y, por tanto, vale la pena recordarlo. Lo bueno es que también es bastante sencillo de utilizar. Sin embargo, si quieres más información, quizás quieras leer Funciones de Ventana SQL Comunes: Uso de Particiones con Funciones de Clasificación en el blog LearnSQL.es .

RANK() OVER(PARTITION BY ...)-Múltiples Columnas

Finalmente, veamos la cláusula PARTITION BY con múltiples columnas. No es tan diferente de usar PARTITION BY con una sola columna. Eche un vistazo:

SELECT
  RANK() OVER(PARTITION BY city, first_name
    ORDER BY exam_date ASC) AS ranking,
  city,
  first_name,
  last_name,
  exam_date
FROM exam_result;

En la consulta anterior, estamos utilizando PARTITION BY con dos columnas: city y first_name. Esto significa que dentro de cada par distinto de ciudad y nombre, tendremos clasificaciones separadas. Echa un vistazo a los resultados:

rankingcityfirst_namelast_nameexam_date
1San FranciscoErykMyers2019-06-07
1San FranciscoEvie-MayBoyer2019-01-23
1San FranciscoEmyrDowes2018-12-18
1Los AngelesDinaMorin2019-01-17
1Los AngelesTrystanOconnor2019-02-28
1San DiegoMarleneDuncan2019-06-13
1San DiegoMariusPowell2019-11-13
2San DiegoMariusWilliamson2020-01-02
1San DiegoNoraParkinson2019-02-16
1San DiegoJoanneGoddard2019-12-18

Como puedes ver arriba, la mayoría de las personas están clasificadas en primer lugar. Esto se debe a que la mayoría de los pares (ciudad y nombre) son únicos. Sin embargo, hay un par que no es único. Hay dos personas de San Diego que se llaman Marius. Marius Powell es el primero porque hizo el examen antes que Marius Williamson.

Recuerda, la práctica hace la perfección

Como puedes ver, hay numerosos casos de uso para las funciones de clasificación en SQL. Por lo tanto, es importante conocerlas bien: probablemente tendrás que escribir una consulta de clasificación SQL tarde o temprano.

La mejor manera de aprender sobre las funciones de clasificación (y las funciones de ventana en general) es a través de la práctica. Te recomiendo este Funciones de ventana curso. Tiene 218 ejercicios interactivos, lo que equivale a unas 20 horas de codificación. Eso es bastante, especialmente si decides hacerlo de una sola vez. No lo recomendamos. Es mejor repartir los estudios en varios días. Aquí encontrarás más consejos sobre cómo mantenerte sano mientras aprendes SQL. Cuida tu cuerpo mientras desarrollas tu carrera, y empieza a aprender SQL hoy mismo.

Tags:

También te pueden interesar