Cómo Numerar Filas en un Conjunto de Resultados SQL

¿Alguna vez ha necesitado añadir un número secuencial a los registros del resultado de una consulta SQL? No es tan sencillo como parece. Descubre cómo hacerlo correctamente en este artículo.

Para numerar las filas de un conjunto de resultados, hay que utilizar una función de ventana SQL llamada ROW_NUMBER(). Esta función asigna un número entero secuencial a cada fila de resultados. Sin embargo, también se puede utilizar para numerar registros de diferentes maneras, como por subconjuntos. Incluso se puede utilizar para numerar registros con otros fines interesantes, como veremos.

Una petición frecuente: ¿Podría numerar los registros?

Supongamos que trabaja en una empresa de venta de coches y quiere elaborar el siguiente informe. Observe que la primera columna (row_num), no es una columna de la tabla; la generamos utilizando ROW_NUMBER() en la consulta.

Tabla de resultados

La consulta para obtener el informe es:

SELECT ROW_NUMBER() OVER () AS row_num,
       article_code,
       article_name,
       branch,
       units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

En la consulta anterior, la sintaxis de la función ROW_NUMBER() es muy sencilla: utilizamos una cláusula vacía OVER. Esto significa que queremos numerar todos los registros del conjunto de resultados utilizando sólo una secuencia de números, asignando números a los registros sin ningún orden. Esta es la forma más sencilla de utilizar la función ROW_NUMBER():

ROW_NUMBER() OVER () AS row_num

Sin embargo, hay otras formas de utilizar ROW_NUMBER(). Podemos añadir una cláusula PARTITION BY y/o una cláusula ORDER BY a OVER, como veremos en la siguiente sección. La cláusula PARTITION BY nos permite numerar varios grupos de registros de forma independiente, mientras que la cláusula ORDER BY nos permite numerar los registros en un orden específico. En la próxima sección, veremos algunos ejemplos.

Antes de continuar con ROW_NUMBER(), debemos decir unas palabras sobre las funciones de ventana de SQL. Como hemos mencionado anteriormente, ROW_NUMBER() es una función ventana. Hay muchas otras funciones ventana que puedes utilizar en tus consultas, como AVG(), MAX(), LEAD(), LAG() y FIRST_VALUE(). Si quieres entrar en detalles, te sugiero el curso Funciones de ventana de LearnSQL. Es un tutorial paso a paso que te lleva a través de las funciones de ventana de SQL usando ejemplos y ejercicios.

Profundizando: Las cláusulas Partition By y Order By

En la sección anterior, cubrimos la forma más simple de utilizar la función de ventana ROW_NUMBER(), es decir, simplemente numerar todos los registros en el conjunto de resultados sin ningún orden en particular. En los siguientes párrafos, veremos tres ejemplos con algunas cláusulas adicionales, como PARTITION BY y ORDER BY.

En nuestro primer ejemplo, numeraremos los registros utilizando una secuencia diferente para cada sucursal de la empresa, que se ordenará por las unidades vendidas en esa sucursal. En la siguiente consulta, la cláusula de rama PARTITION BY agrupa los registros que tienen el mismo valor en rama, asignando una secuencia ROW_NUMBER diferente a cada grupo/sucursal. (Cada grupo tiene un color diferente en la imagen de abajo.) La cláusula ORDER BY units_sold define el orden en el que procesamos las filas dentro de la partición. En este caso, las filas pertenecientes a cada partición se ordenarán por unit_sold en orden descendente.

SELECT 
   ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC) AS row_num,
   article_code,
   article_name,
   branch,
   units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Observe que en esta consulta, las secuencias se asignan por rama -en la imagen inferior, cada grupo de registros tiene un color diferente- y se ordenan por units_sold. La cláusula que utilizamos es:

ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC)

Esta cláusula nos permite numerar los grupos de registros que tienen el mismo valor en la columna rama. En nuestro ejemplo, hay dos grupos de registros: Nueva York (rojo) y San Francisco (azul). Ahora cada grupo de registros se numerará (cláusula ORDER BY ) en función de la columna units_sold. Los valores se muestran en orden descendente.

Tabla de resultados

Las cláusulas OVER, PARTITION BY, y ORDER BY son muy comunes en las funciones ventana; si quiere entrar en detalles, le sugiero que lea el artículo Ejemplos de funciones ventana, donde encontrará varios ejemplos de funciones ventana explicados en detalle.

Uso de ROW_NUMBER para eliminar duplicados

Otro caso de uso interesante para la función ROW_NUMBER() es cuando tenemos registros completamente duplicados en una tabla. Los registros duplicados completos ocurren cuando la tabla tiene más de un registro con los mismos valores en todas sus columnas (normalmente debido a un fallo anterior). Mostraremos un código SQL para arreglar esta situación; además, este código se puede adaptar a cualquier caso de registros duplicados completos.

En primer lugar, vamos a insertar algunos registros duplicados completos en la tabla Sales tabla. Supongamos que no tenemos una clave primaria en la tabla Sales tabla y un desarrollador de SQL ejecuta por error la siguiente sentencia INSERT:

INSERT INTO sales 
SELECT * FROM sales WHERE branch = 'San Francisco';

Después de la ejecución de INSERT, la tabla Sales tiene el siguiente aspecto. Las dos últimas filas son duplicados completos:

Tabla: Sales

Para eliminar los registros duplicados, añadiremos una nueva columna llamada row_num y la poblaremos con la siguiente INSERT que utiliza la función ROW_NUMBER(). Tenga en cuenta que PARTITION BY todas las columnas de la tabla. Aquí está el código SQL:

ALTER TABLE sales ADD COLUMN row_num INTEGER;
INSERT INTO sales 
SELECT 
 article_code, article_name, branch, units_sold, period, 
 ROW_NUMBER() OVER (PARTITION BY article_code,article_name,branch, units_sold,period) 
FROM sales ;

Entonces, después de añadir la nueva columna y rellenarla con ROW_NUMBER(), nuestra tabla tiene este aspecto:

Tabla: Sales

Es fácil ver que necesitamos eliminar todos los registros con un NULL o un 2 en la columna row_num. Vamos a hacerlo con el comando DELETE. Después de eso, necesitamos eliminar la columna row_num. Aquí está el código:

DELETE FROM sales WHERE row_rank IS NULL OR row_rank = 2;
ALTER TABLE sales DROP COLUMN row_rank;

Después de ejecutar las sentencias DELETE y ALTER, la tabla Sales está arreglada, sin registros duplicados.

Hay una desventaja con este enfoque que debemos aclarar. En algún momento del proceso, el número de registros de la tabla Sales se duplicará. Esto puede hacer que este método sea ineficiente, especialmente con tablas grandes. Por ello, recomendamos utilizarlo sólo en tablas pequeñas y medianas.

Crear un informe de clasificación con ROW_NUMBER

En esta sección, usaremos la función ROW_NUMBER() para crear un ranking. Veremos que hay mejores funciones para la clasificación, como RANK y DENSE_RANK; sin embargo, podemos construir un informe de clasificación bastante bueno utilizando ROW_NUMBER().

Supongamos que una vez al año nuestra empresa de venta de coches da tres bonificaciones a sus vendedores: una bonificación es para la persona que ha vendido más unidades, otra bonificación es para la persona que ha obtenido más ingresos y la tercera bonificación es para la persona que ha obtenido más beneficios. Si cualquier categoría de bonificación la ganan dos vendedores, ambos reciben el 50% de la bonificación. Utilizaremos la tabla Sellers_2019 para obtener las clasificaciones y definir el ganador de cada bonificación.

Tabla: Sellers_2019

La siguiente consulta nos devolverá las clasificaciones que necesitamos para definir los ganadores de las primas. Estos rankings estarán en las columnas units_ranking, revenue_ranking y profit_ranking.

SELECT  seller_name,
	  ROW_NUMBER() OVER (ORDER BY units_sold desc) units_ranking,
	  ROW_NUMBER() OVER (ORDER BY revenue desc) revenue_ranking,
	  ROW_NUMBER() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

La siguiente imagen muestra los resultados de la consulta anterior con la función ROW_NUMBER(). Inicialmente, los valores del ranking parecen ser correctos. Pero si los revisamos en detalle, podemos encontrar un error con Susan Graue en Units_ranking. Susan (y John Doyle) ganó la bonificación de unidades vendidas con 123 unidades. Sin embargo, la función ROW_NUMBER asigna la posición 1 a John y la posición 2 a Susan, lo que no es correcto.

Tabla de resultados

Así pues, en este caso la función ROW_NUMBER() no es la mejor opción para los cálculos de clasificación. Afortunadamente, SQL proporciona dos funciones específicas para la clasificación: RANK() y DENSE_RANK(). La siguiente consulta utiliza la función RANK() en lugar de la ROW_NUMBER():

SELECT  seller_name,
	  RANK() OVER (ORDER BY units_sold desc) units_ranking,
	  RANK() OVER (ORDER BY revenue desc) revenue_ranking,
	  RANK() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

En la siguiente imagen, podemos ver los resultados de la consulta RANK(). Podemos comprobar que el problema con el rango de Susan está resuelto.

Tabla de resultados

Si estás interesado en las funciones de ventana RANK() y DENSE_RANK(), te sugiero que leas el artículo Cómo utilizar las funciones RANK. Te dará varios ejemplos y consultas.