ROW_NUMBER Función SQL: Cómo mostrar los números de fila

En SQL, es habitual que los conjuntos de datos no estén ordenados, lo que puede dificultar el análisis. Para comprender cómo se relacionan las filas dentro de un conjunto de datos, podemos utilizar la función ROW_NUMBER().

Esta función asigna números secuenciales a las filas de un conjunto de resultados, proporcionando un orden claro para su posterior manipulación y análisis. Esto puede hacerse para el conjunto de datos en su totalidad o para distintos grupos de datos dentro del conjunto de datos. 

Este artículo presupone un conocimiento previo de los fundamentos de SQL. Cubriremos los aspectos básicos de la función ROW_NUMBER(), de uso común, y daremos ejemplos de dificultad creciente.

ROW_NUMBER() Syntax

Ésta es la sintaxis básica de la función ROW_NUMBER():

ROW_NUMBER() OVER([PARTITION BY value expression, ... ] [ORDER BY order_by_clause])

Vamos a desglosar los componentes clave:

• ROW_NUMBER(): Es la propia función, que genera números de fila secuenciales.
• OVER (...): Esta cláusula es obligatoria para funciones de ventana como ROW_NUMBER(). Define el contexto en el que se calculan los números de fila.
• PARTITION BY value_expression: Esta cláusula opcional divide el conjunto de resultados en particiones basadas en la(s) columna(s) o expresión(es) especificada(s). A continuación, los números de fila se calculan independientemente dentro de cada partición.
• ORDER BY order_by_clause: Esta cláusula opcional especifica el orden en que se asignan los números de fila dentro de cada partición (o de todo el conjunto de resultados si no se utiliza PARTITION BY ).

Para ilustrarlo, he aquí cómo podríamos utilizar ROW_NUMBER() dentro de una consulta SQL más amplia:

SELECT Val_1, 
    ROW_NUMBER() OVER(PARTITION BY group_1, ORDER BY number DESC) AS rn
FROM Data;

ROW_NUMBER() Ejemplos

En los tres ejemplos siguientes, utilizaremos el IDE gratuito DataLab. Utilizaremos el conjunto de datos de muestra Empleados (ya incorporado a DataLab), que tiene las cuatro columnas siguientes:

• first_name: campo cadena
• last_name: campo cadena
• gender: campo de cadena con dos valores ("M" o "F")
• hire_datela fecha de contratación del empleado

Podemos consultar el conjunto de datos utilizando el siguiente código SQL:

SELECT e.first_name, e.last_name, e.gender, e.hire_date
    FROM employees.employees e
LIMIT 100; -- Optionally reduce the size of the output

Numerar a los empleados por orden alfabético

Antes de utilizar ROW_NUMBER(), es importante definir nuestro objetivo: esto nos aclarará si queremos particionar y ordenar, y cómo. En este ejemplo, nos gustaría ordenar a todos los empleados alfabéticamente. No necesitamos una cláusula PARTITION BY porque ordenamos a todos los empleados del conjunto de datos. Ordenaremos a los clientes por su apellido (last_name). Llamaremos a nuestra numeración name_row_number.

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(ORDER BY e.last_name) AS name_row_number    
FROM employees.employees e;

Para tratar los empates (empleados con el mismo apellido), podemos refinar la ordenación añadiendo más columnas. En el ejemplo siguiente, ordenamos primero por last_name, y luego, en los casos en que el apellido de un empleado sea el mismo que el de otra persona, ordenaremos por su nombre (first_name).

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(ORDER BY e.last_name, e.first_name) AS name_row_number    
FROM employees.employees e;

Numeración de los empleados por fecha de contratación más reciente dentro de los géneros

Ahora, ordenemos a los empleados de la fecha de contratación más reciente a la más antigua dentro de sus respectivos géneros. Volveremos a utilizar la cláusula ORDER BY para ordenar por hire_date, pero esta vez en orden descendente (utilizando DESC) para dar prioridad a las contrataciones más recientes.

Para conseguir una numeración separada para cada género, introduciremos la cláusula PARTITION BY gender. Esto significa que los números de fila se reiniciarán desde 1 para cada sexo distinto.

Aquí tienes la consulta completa:

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(PARTITION BY gender ORDER BY hire_date DESC) AS hire_row_number    
FROM employees.employees e;

Luego podríamos consultar estos datos utilizando una cláusula WHERE para encontrar al empleado con más experiencia en cada género:

WITH RankedEmployees AS (
    SELECT e.first_name, e.last_name, e.gender, e.hire_date,
        ROW_NUMBER() OVER(PARTITION BY gender ORDER BY hire_date DESC) AS hire_row_number
    FROM employees.employees e
)
SELECT first_name, last_name, gender, hire_date
FROM RankedEmployees
WHERE hire_row_number = 1;

Ordenar los empleados por salario dentro de los géneros mediante un JOIN

En nuestro último ejemplo, clasificaremos a los empleados por su salario, teniendo en cuenta su sexo. Para conseguirlo, uniremos la tabla employees con la tabla salaries basándonos en la columna emp_no:

SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary
    FROM employees.employees e
JOIN employees.salaries s ON e.emp_no = s.emp_no
LIMIT 100;

Ahora, utilizaremos tanto PARTITION BY como ORDER BY. Particionaremos por gender para tener clasificaciones separadas para cada sexo y ordenaremos por salary en orden descendente para clasificar primero a los que más ganan.

Aquí tienes la consulta completa:

SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary,
    ROW_NUMBER () OVER(PARTITION BY e.gender ORDER BY s.salary DESC) AS salary_row_number
    FROM employees.employees e
JOIN employees.salaries s ON e.emp_no = s.emp_no
LIMIT 100;

Para comparar los salarios más altos para cada sexo, podemos filtrar los resultados utilizando una cláusula WHERE. La consulta siguiente te devolverá los 5 que más ganan de cada sexo, ordenados por su rango dentro de su grupo de sexo. Estas consultas pueden proporcionar información sobre la igualdad salarial dentro del conjunto de datos.

WITH RankedSalaries AS (
    SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary,
        ROW_NUMBER() OVER(PARTITION BY e.gender ORDER BY s.salary DESC) AS salary_row_number
    FROM employees.employees e
    JOIN employees.salaries s ON e.emp_no = s.emp_no
)
SELECT first_name, last_name, gender, hire_date, salary
FROM RankedSalaries
WHERE salary_row_number <= 5
ORDER BY salary_row_number, gender;

Conclusión

La función ROW_NUMBER() es útil cuando tenemos un conjunto de datos sin ordenar y queremos asignar una numeración secuencial clara a las filas para su posterior análisis. Definimos el orden específico de estos números utilizando ORDER BY y definimos secuencias de numeración separadas para grupos distintos dentro de los datos utilizando PARTITION BY.

Si este artículo te ha resultado útil y quieres aprender más sobre SQL, consulta nuestros otros cursos de SQL.