Cómo utilizar la función SQL RANK OVER (PARTITION BY)

Clasificar datos en SQL es pan comido si sabe cómo utilizar RANK() para clasificar sobre una partición. Este artículo muestra cómo hacerlo y en qué se diferencia RANK() de DENSE_RANK() y ROW_NUMBER().

Si trabaja con SQL en un entorno profesional, habrá tenido que clasificar datos al menos una vez. Piense en clasificar meses/años/trimestres por ingresos o costes, los productos más vendidos, los posts más vistos o las canciones más escuchadas, los empleados por salario, las sucursales más rentables, etc. O clasificar libros por sus ventas.

O clasificar libros por sus ventas.

El caso es que a menudo hay que clasificar los datos dentro de una categoría determinada, o partición, como la llamamos nosotros. El SQL RANK OVER (PARTITION BY) entra en juego.

Este es un ejemplo típico de funciones ventana en SQL. Para una explicación más detallada de las funciones ventana, nuestro curso de funciones ventana es el mejor. A través de 218 ejercicios interactivos, aprenderá más sobre PARTITION BY y otras cláusulas de funciones ventana, como ORDER BY, ROWS y RANGE. Estas funciones se utilizan no sólo en la clasificación, sino también en las funciones ventana de agregación y análisis.

Volvamos a las ventas de libros. Eche un vistazo a esta tabla, con el código para crearla aquí. ¿Cómo clasificamos las ventas de libros dentro de cada idioma?

La solución es sencilla si sabemos que el idioma, en este ejemplo, actúa como algo llamado partición de datos.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

Y aquí está.

Ahora, vamos a diseccionar este ejemplo.

¿Qué es RANK()?

En términos más simples, RANK() es una función ventana.

Las funciones ventana de SQL son similares a las funciones agregadas en el sentido de que se aplican a un grupo de filas. Una gran diferencia: las funciones ventana mantienen los detalles de las filas individuales, a diferencia de las funciones agregadas con GROUP BY.

¿Qué hace RANK()?

Como su nombre indica, clasifica los datos. Esto la convierte en una función ventana de clasificación, junto con DENSE_RANK() y ROW_NUMBER().

Al utilizar cualquiera de estas funciones de ventana, debe ir acompañada de una cláusula OVER (ORDER BY). La cláusula OVER() es obligatoria para cualquier función ventana. Es lo que convierte una función "normal" en una función ventana.

Para estas funciones de clasificación, el ORDER BY entre paréntesis define el orden en que se realiza la clasificación. Este orden puede ser ascendente o descendente. Tenga en cuenta que no afecta al orden de las filas en el resultado; eso se hace con un ORDER BY al final de la consulta.

Obtenga más información en nuestro artículo sobre la función de ventana RANK().

Cómo funciona RANK() con OVER (ORDER BY)

Utilicemos el conjunto de datos anterior. Tome toda la tabla y ordene los libros por ventas. Veamos qué ocurre.

SELECT
  title,
  author,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM books;

Es igual que la consulta anterior, excepto que en esta no se selecciona el idioma y no se utiliza PARTITION BY. Así, la función RANK() va seguida de OVER(). La cláusula ORDER BY indica a la función que ordene los datos por ventas en orden descendente, es decir, de los libros más vendidos a los menos vendidos. Como se omite la cláusula PARTITION BY, la función ordena toda la tabla.

A continuación se muestran las diez primeras filas del resultado.

Lo más importante: hay cuatro libros con 50 millones de ejemplares vendidos y todos ocupan el quinto lugar.

Así es como RANK() difiere de las otras dos funciones de ventana de clasificación: da el mismo rango a los valores empatados. Cuando la función alcanza el siguiente valor de ventas (en este caso, 36 millones de ejemplares vendidos), no asigna el siguiente valor de rango consecutivo (6), sino que se lo salta para ajustar el recuento de valores de ventas empatados. Como se ha mencionado, el rango "5" aparece cuatro veces; por lo tanto, el siguiente rango asignado es el nueve.

DENSE_RANK() también clasifica los empates con el mismo rango. Sin embargo, a diferencia de RANK(), no omite valores de clasificación basados en empates. Los mismos datos clasificados con DENSE_RANK() son los siguientes.

Después de varios libros clasificados en quinto lugar, el siguiente lugar es el sexto, no el noveno como en RANK().

¿Qué ocurre con ROW_NUMBER()? No le importan los empates ni los saltos. Sólo ordena las filas secuencialmente. A continuación se muestran las diez primeras filas con ROW_NUMBER().

Las diferencias entre estas funciones se explican en la descripción general de las funciones de clasificación.

Cómo funciona RANK() con OVER (PARTITION BY)

La cláusula PARTITION BY divide los datos en particiones o subconjuntos. Cuando se utiliza con RANK(), significa que los datos se clasifican dentro de la partición. Cuando llega a la segunda partición, la clasificación se restablece para empezar desde uno.

Veamos de nuevo la consulta del principio de este artículo para aclarar esto.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

En esta consulta, RANK() se utiliza con PARTITION BY. La partición, en este caso, es original_language. La clasificación se realiza por ventas en orden descendente, tal y como se especifica en la cláusula ORDER BY.

La forma en que hemos escrito PARTITION BY y ORDER BY significa que los libros se clasifican por ventas pero dentro de cada categoría lingüística. Una vez que la función clasifica todos los libros en un idioma, vuelve a empezar cuando llega al segundo idioma, y así sucesivamente.

Esto se ve en el resultado de la consulta.

Hemos marcado cada partición con un color diferente para ver fácilmente las distintas particiones. El libro más vendido en inglés es Harry Potter y la piedra filosofal, de J.K. Rowling. A continuación se sitúa El Hobbit. Lolita y Watership Down ocupan el tercer puesto, ya que RANK() asigna el mismo rango a libros con el mismo valor de ventas. A continuación, se salta un valor del ranking y Diecinueve Ochenta y Cuatro ocupa el quinto lugar.

La siguiente partición es la lengua francesa, y se reinicia la clasificación. El Principito es el libro más vendido en francés.

Vemos que la misma lógica funciona para los libros en italiano, ruso y español.

Ejemplo adicional

Hemos estudiado estas consultas para mostrar cómo funciona RANK() OVER (PARTITION BY). Ahora, ¡a practicar!

Lo que sigue no es muy diferente de la primera consulta. No deberías tener problemas para aplicar lo que has aprendido.

Hay una columna en la tabla books llamada classify_under. En ella se especifica la categoría en la que se va a colocar cada libro en la librería.

Vamos a ordenar los libros por ventas para cada categoría.

SELECT
  classify_under,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY classify_under ORDER BY sales DESC)
    AS sales_rank
FROM books;

La consulta selecciona la columna classify_under en lugar de original_language en la primera consulta. Las demás columnas seleccionadas son las mismas.

También hay una diferencia en RANK(). Puesto que estamos clasificando por classify_under, esta es la columna que debe estar en la cláusula PARTITION BY.

Una vez más, queremos clasificar los libros por ventas en orden descendente. Esta es la clasificación:

Por pura coincidencia, de nuevo hay cinco particiones. En la categoría "Clásicos", Don Quijote es el libro más vendido. A continuación, hay tres libros clasificados en segundo lugar. La secuencia de clasificación se salta hasta llegar a Guerra y Paz en quinto lugar. El resto de los clásicos se clasifican secuencialmente, ya que no hay más empates.

En otras categorías, no hay empates. "Poesía" y "Ciencia ficción" sólo tienen un libro en cada categoría. Por tanto, sólo existe el primer rango.

Para ver más ejemplos con otras funciones de ventana, vaya a nuestro artículo que explica cómo utilizar PARTITION BY.

Añada RANK() a su vocabulario SQL

Hemos visto los usos más típicos de la función de ventana RANK(). Aunque requiere un OVER (ORDER BY), la cláusula PARTITION BY desbloquea sus posibilidades. Convierte RANK() en una sofisticada herramienta para clasificar datos en una o más particiones con facilidad en su trabajo diario.

Para obtener más información y practicar RANK() y otras funciones de ventana (de clasificación), utilice nuestro Funciones de ventana curso. Obtendrás una explicación aún más detallada de la clasificación y la oportunidad de escribir mucho código en nuestros ejercicios.

¡Feliz clasificación!