Como Utilizar ROW_NUMBER OVER() en SQL para Ordenar Datos

A veces es necesario conocer la posición de las filas en un conjunto de resultados. Aprendacomo usar ROW_NUMBER y OVER en SQL puede hacerlo posible.

¿Alguna vez ha necesitado añadir un número secuencial a los registros devueltos por una consulta SQL? O tal vez necesite crear un informe 'top n' basado en una clasificación específica. En cualquiera de estos casos, necesita calcular la posición de la fila en la clasificación. Para ello, necesita la función ROW_NUMBER(). Esta función asigna un número entero secuencial a cualquier fila del conjunto de resultados.

En este artículo, exploraremos cómo utilizar la función ROW_NUMBER() en SQL.

¿Qué es la Función ROW_NUMBER()?

ROW_NUMBER es una función de ventana en SQL. Se utiliza para añadir números secuenciales a las filas de un conjunto de resultados. Como cualquier otra función ventana, necesitas usarla con la cláusula OVER(). Esta es la sintaxis:

SELECT
  ROW_NUMBER() OVER (...) as athlete_num
  …
FROM athletes;

La cláusula OVER() tiene dos subcláusulas opcionales: PARTITION BY y ORDER BY. Mostraremos ejemplos utilizando varias cláusulas OVER diferentes.

Antes de empezar, digamos unas palabras sobre las funciones ventana en general. Las funciones ventana son una parte muy potente de SQL, pero no son muy conocidas por el usuario medio de SQL. Por eso recomiendo nuestro curso interactivo sobre Funciones de ventana. En este curso paso a paso, le guiaremos a través de las funciones ventana utilizando más de 200 ejercicios prácticos. Al final del curso, se sentirá cómodo utilizando funciones de ventana en bases de datos SQL.

Uso de ROW_NUMBER() con OVER(): Un Ejemplo Introductorio

Vamos a mostrar una consulta SQL sencilla utilizando la función de ventana ROW_NUMBER. No hay nada mejor que los deportes para ilustrar las clasificaciones, así que supongamos que trabajamos para una empresa que organiza competiciones deportivas en muchos países.

En primer lugar, queremos asignar un número secuencial a cada deportista; este número se utilizará como ID del deportista en nuestra empresa. Para evitar conflictos, no queremos que haya ningún criterio para determinar el orden de la numeración secuencial. Queremos que los números secuenciales se asignen a cada atleta de forma aleatoria, no alfabéticamente por nombre, país o deporte.

Tenemos una tabla llamada athlete con las columnas firstname, lastname, sport, y country. La consulta para generar un informe que incluya un número secuencial para cada atleta es:

SELECT
  ROW_NUMBER() OVER () as athlete_id,
  firstname
  lastname,
  sport, 
  country
FROM athletes;

La expresión ROW_NUMBER() OVER () asigna un valor entero secuencial empezando por 1 a cada fila del conjunto de resultados de la consulta. El orden de los números asignados a las filas del resultado no es determinista si se utiliza la cláusula simple OVER(). (Observe que no hay cláusulas adicionales como ORDER BY o PARTITION BY en OVER()) El primer registro puede ser cualquier registro de la tabla; para este registro, ROW_NUMBER devolverá 1. Luego lo mismo para el segundo registro que será el número 2, y así sucesivamente. A continuación se muestra un resultado parcial de la consulta:

Antes de terminar esta sección, me gustaría sugerirle el artículo Qué es la cláusula OVER en SQL, donde podrá encontrar varios ejemplos de funciones ventana utilizando diferentes combinaciones de la cláusula OVER.

Creando Rankings con ROW_NUMBER() y ORDER BY

Supongamos ahora que la empresa necesita crear una etiqueta con el número de participante para todos los atletas que participan en una maratón. Los atletas deben ordenarse por apellido, y la empresa quiere asignar un número secuencial a cada atleta; los atletas llevarán estos números como etiquetas en sus camisetas durante el maratón. Las etiquetas deben empezar por 1001. La consulta es:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon';

Esta consulta es similar a la del ejemplo anterior. Una diferencia es la cláusula WHERE, que devuelve sólo los atletas que participan en el maratón. La otra diferencia (que es la principal) es la cláusula OVER(ORDER BY lastname). Ésta indica a ROW_NUMBER() que el número secuencial debe asignarse en el orden de lastname- por ejemplo, 1 al primer lastname, 2 al segundo, y así sucesivamente.

En el conjunto de resultados anterior, los participantes se ordenaban por lastname. Sin embargo, si dos participantes tienen el mismo apellido (por ejemplo, Smith), el orden de estas dos filas no es determinista; las filas pueden estar en cualquier orden. Si queremos ordenar tanto por lastname como por firstname, debemos utilizar la expresión

ROW_NUMBER() OVER (ORDER BY lastname, firstname)

Uso de ORDER BY dos veces en una consulta

En la consulta anterior, utilizamos la cláusula ORDER BY en la función ROW_NUMBER(). Sin embargo, el resultado de la consulta no sigue ningún orden, es decir, las filas se ordenan aleatoriamente. Si quisiéramos, podríamos añadir una segunda cláusula ORDER BY al final de la consulta para definir el orden en que se muestran los registros del resultado.

Vamos a modificar la consulta anterior añadiendo un único cambio: Pondremos un ORDER BY country:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname ASC) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon'
ORDER BY country;

Las filas del resultado que aparece a continuación son las mismas que en la consulta anterior, pero se muestran en distinto orden. Ahora se ordenan en función del país del atleta. Sin embargo, si dos o más atletas son del mismo país, se muestran en cualquier orden. Podemos ver esto abajo en los dos atletas de Canadá:

En esta consulta, hemos utilizado la cláusula ORDER BY dos veces. La primera vez se utilizó en la función ROW_NUMBER para asignar el número secuencial siguiendo el orden del apellido. La segunda vez se utilizó para definir el orden en que se muestran las filas de resultados, que se basa en el nombre del país.

Utilización de ROW_NUMBER() con PARTITION BY y ORDER BY

En el siguiente ejemplo de consulta, utilizaremos ROW_NUMBER() combinado con las cláusulas PARTITION BY y ORDER BY. Mostraremos una consulta para asignar números de habitación a los atletas. Supongamos que la empresa desea alojar a los atletas de un mismo país en habitaciones de hotel contiguas. La idea es crear una etiqueta con el país y un número secuencial para cada atleta y colocar esta etiqueta en la puerta de cada habitación de hotel. Por ejemplo, si el país es Canadá y tiene 3 atletas, queremos que las etiquetas de las habitaciones sean "Canadá_1", "Canadá_2" y "Canadá_3".

La consulta para generar las etiquetas de las habitaciones con el nombre del atleta asignado a esa habitación es:

SELECT
  country || '_' || 
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY lastname ASC) 
                                                            as room_label,
  firstname,
  lastname, 
  country
FROM athletes;

El nuevo elemento introducido en la consulta es OVER(PARTITION BY country). Agrupa las filas de la misma country y genera una serie secuencial diferente de números (empezando por 1) para cada país.

En el siguiente resultado de consulta, puede ver que las filas agrupadas por la cláusula PARTITION BY tienen el mismo color. Un grupo de filas corresponde a Canadá (azul claro), otro a Francia (morado), y así sucesivamente.

Dentro de cada grupo de filas, la cláusula ORDER BY lastname se utiliza para asignar números secuenciales a los atletas por apellido. Para "Irlanda", tenemos tres filas; la primera es para "Barry", la segunda para "Fox", y así sucesivamente.

Te recomiendo el artículo Cómo usar SQL PARTITION BY con OVER, donde encontrarás más ejemplos de las cláusulas OVER y PARTITION BY.

Otros Rankings Funciones de ventana: RANK y DENSE_RANK

Aparte de ROW_NUMBER, SQL proporciona otras dos funciones de ventana para calcular rankings: RANK y DENSE_RANK. La función RANK funciona de forma diferente a ROW_NUMBER cuando hay empates entre filas. Cuando hay un empate, RANK asigna el mismo valor a ambas filas y se salta el siguiente rango (por ejemplo, 1, 2, 2, 2, 5 - se omiten los rangos 3 y 4). La función DENSE_RANK no omite el rango o rangos siguientes.

Veamos un ejemplo sencillo para ver las diferencias entre estas tres funciones:

SELECT 
  lastname AS athlete_name, 
  time, 
  ROW_NUMBER() OVER (ORDER BY time) AS position_using_row_number, 
  RANK OVER() (ORDER BY time) AS position_using_rank,
  DENSE_RANK() OVER (ORDER BY time) AS position_using_dense_rank
 FROM competition_results
 WHERE sport = ‘Marathon men’; 

Los resultados son:

Si está interesado en las funciones de ventana RANK y DENSE_RANK, le sugiero estos artículos para obtener más detalles y ejemplos:

• Visión general de las funciones de clasificación en SQL
• Cómo Utilizar RANK Funciones
• Qué es la Función RANK en SQL y Cómo se Utiliza

Uso de ROW_NUMBER() en la Cláusula WHERE

En SQL, no puede usar funciones de ventana en la cláusula WHERE. Sin embargo, en algunos casos, puede ser necesario. En un informe Top 10, por ejemplo, sería muy útil poder utilizar una condición como WHERE ROW_NUMBER OVER() <= 10.

Aunque no puede utilizar ROW_NUMBER() directamente en WHERE, puede hacerlo indirectamente a través de una expresión común de tabla, o CTE. Por ejemplo, supongamos que queremos obtener las 3 primeras posiciones en el maratón y en la carrera de 100 metros. En primer lugar, escribimos la CTE, que comienza por WITH:

-- CTE starts
WITH positions AS (
  SELECT 
    lastname AS athlete_name,
    sport,
    country,
    time, 
    ROW_NUMBER OVER (PARTITION BY sport ORDER BY time) AS position
  FROM competition_results
  WHERE sport IN (‘Marathon men’, ‘Marathon women’)
)
	--CTE ends

	--main query starts
SELECT 
  sport, 
  athlete_name, 
  time, 
  country, 
  position
FROM positions
WHERE position <= 3
ORDER BY sport, position;

En la consulta anterior, creamos un CTE llamado positions. Tiene una columna llamada posición que se rellena con el resultado de la función ROW_NUMBER().

En la consulta principal (es decir, la segunda sentencia SELECT ), podemos utilizar la columna position en la cláusula WHERE para filtrar los atletas que terminan la competición en las tres primeras posiciones.

Nota: Si tenemos empates entre dos competidores, podría ser más apropiado utilizar la función RANK() que la función ROW_NUMBER() en este informe.

A continuación se muestran los resultados de la consulta:

Si desea practicar las funciones de ventana de SQL, le recomiendo nuestro conjunto de prácticas interactivas Funciones de ventana. Proporciona 100 ejercicios prácticos sobre funciones ventana, incluyendo la creación de rankings utilizando diferentes funciones ventana de ranking.

La Pseudocolumna Oracle ROWNUM

Oracle SQL nos permite poner una pseudocolumna llamada ROWNUM en cualquier consulta. Una pseudocolumna se comporta como una columna de tabla, pero no se almacena realmente en la tabla. Se puede seleccionar desde una pseudocolumna como si fuera una columna de la tabla.

La pseudocolumna ROWNUM devuelve la posición de la fila en el conjunto de resultados. Comienza con 1 para la primera fila y cada uno de los registros siguientes se incrementa en 1.

Sin embargo, Oracle ROWNUM no tiene la potencia de la función de ventana ROW_NUMBER. Por ejemplo, no se puede utilizar la subclausa PARTITION BY para crear varias secuencias diferentes como hicimos en la consulta de habitaciones de hotel. Otra limitación es que no se puede utilizar la cláusula ORDER BY para especificar un orden diferente para la secuencia que el orden del conjunto de resultados. La razón de estas limitaciones es sencilla: ROWNUM no es una función de ventana; es una simple pseudocolumna.

¿Listo para practicar ROW_NUMBER() y OVER() en SQL?

Hemos cubierto varias formas de añadir una secuencia numérica al resultado de una consulta utilizando la función ROW_NUMBER. Y hemos mostrado diferentes maneras de utilizar la cláusula OVER(). También hemos presentado otras dos funciones de clasificación SQL: RANK y DENSE_RANK.

Las funciones de Windows son un poderoso recurso en SQL. Si desea profundizar más, le sugiero que tome nuestro curso interactivo en línea Funciones de ventana interactivo en línea. Es un tutorial paso a paso que te lleva a través de las funciones de ventana de SQL utilizando ejemplos y ejercicios. También recomiendo nuestra hoja de trucos gratuita sobre funciones de ventana SQL, que es mi hoja de trucos preferida. La tengo pegada en la pared de mi oficina para usarla como ayuda rápida para la sintaxis de las funciones de ventana.