ROW_NUMBER() de MySQL explicado (MySQL 8.0): clasificación, consultas Top‑N y deduplicación

1. Introducción

La versión 8.0 de MySQL introdujo muchas funciones nuevas, y una de las más notables es el soporte para funciones de ventana. En este artículo nos centraremos en una de las funciones más utilizadas: ROW_NUMBER().

La función ROW_NUMBER() ofrece capacidades potentes para el análisis y la generación de informes, facilitando la ordenación y clasificación de datos según condiciones específicas. Este artículo explica todo, desde el uso básico y ejemplos prácticos hasta enfoques alternativos para versiones anteriores de MySQL.

Lectores objetivo

• Usuarios principiantes o intermedios con conocimientos básicos de SQL
• Ingenieros y analistas de datos que procesan y analizan información con MySQL
• Cualquier persona que esté considerando migrar a la última versión de MySQL

Beneficios de ROW_NUMBER()

Esta función permite asignar un número único a cada fila según condiciones específicas. Por ejemplo, puedes escribir consultas como “crear una clasificación en orden descendente de ventas” o “extraer y organizar datos duplicados” de forma concisa.

En versiones anteriores, a menudo era necesario escribir consultas complejas usando variables definidas por el usuario. Con ROW_NUMBER(), tu SQL se vuelve más simple y legible.

En este artículo utilizaremos ejemplos concretos de consultas y los explicaremos de forma amigable para principiantes. En la siguiente sección analizaremos más de cerca la sintaxis básica y el comportamiento de esta función.

2. ¿Qué es la función ROW_NUMBER()?

La función ROW_NUMBER(), añadida en MySQL 8.0, es un tipo de función de ventana que asigna números secuenciales a las filas. Puede numerar filas por un orden específico y/o dentro de cada grupo, lo que resulta extremadamente útil para el análisis y la generación de informes. Aquí explicaremos la sintaxis básica en detalle con ejemplos prácticos.

Sintaxis básica de ROW_NUMBER()

Primero, el formato básico de ROW_NUMBER() es el siguiente.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Significado de cada elemento

• ROW_NUMBER() : Asigna un número secuencial a cada fila.
• OVER : Palabra clave utilizada para definir la ventana de una función de ventana.
• PARTITION BY : Agrupa los datos por la columna especificada. Opcional. Si se omite, la numeración se aplica a todas las filas.
• ORDER BY : Define el orden utilizado para asignar los números, es decir, el criterio de clasificación.

Ejemplo básico

Por ejemplo, supongamos que tienes una tabla llamada “sales” con los siguientes datos.

Para asignar números secuenciales dentro de cada departamento en orden descendente de ventas, usa la siguiente consulta.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Resultado

A partir de este resultado, puedes observar que se muestran las clasificaciones por ventas dentro de cada departamento.

Cómo usar PARTITION BY

En el ejemplo anterior, los datos se agrupan por la columna “department”. Esto asigna una secuencia independiente para cada departamento.

Si omites PARTITION BY, la numeración se asigna a todas las filas como una única secuencia.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Resultado

Características y advertencias de ROW_NUMBER()

• Numeración única : Incluso si los valores son iguales, los números asignados son únicos.
• Manejo de NULLs : Si ORDER BY incluye NULLs, aparecen primero en orden ascendente y últimos en orden descendente.
• Impacto en el rendimiento : Para conjuntos de datos grandes, ORDER BY puede ser costoso, por lo que es importante contar con índices adecuados.

3. Casos de uso prácticos

A continuación se presentan escenarios prácticos que utilizan la función ROW_NUMBER() de MySQL. Esta función es útil en muchos casos reales, como la clasificación de datos y el manejo de duplicados.

3-1. Clasificación dentro de cada grupo

Por ejemplo, considere el caso en que desea “clasificar a los empleados por ventas dentro de cada departamento” utilizando datos de ventas. Use el siguiente conjunto de datos como ejemplo.

Ejemplo de consulta: Clasificación de ventas por departamento

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Resultado:

De esta manera, cada departamento obtiene su propia secuencia en orden descendente de ventas, lo que facilita la generación de rankings.

3-2. Extracción de las N filas superiores

A continuación, veamos un caso en el que desea “extraer los 3 mejores empleados por ventas dentro de cada departamento”.

Ejemplo de consulta: Extraer N filas superiores

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Resultado:

Este ejemplo recupera solo las 3 filas superiores por ventas dentro de cada departamento. Como puede observar, ROW_NUMBER() es adecuado no solo para clasificar sino también para filtrar los resultados principales.

3-3. Encontrar y eliminar datos duplicados

Las bases de datos a veces contienen registros duplicados. En esos casos, también puede manejarlos fácilmente usando ROW_NUMBER().

Ejemplo de consulta: Detectar duplicados

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

Esta consulta detecta duplicados cuando existen varios registros para el mismo nombre de empleado.

Ejemplo de consulta: Eliminar duplicados

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Resumen

ROW_NUMBER() es útil en una variedad de escenarios, como:

1. Clasificación dentro de cada grupo
2. Extracción de N filas superiores
3. Detección y eliminación de duplicados

Esto hace que el procesamiento y análisis de datos complejos sea más sencillo y eficiente.

4. Comparación con otras funciones de ventana

En MySQL 8.0, además de ROW_NUMBER(), existen funciones de ventana como RANK() y DENSE_RANK() que pueden usarse para cálculos de clasificación y posición. Aunque tienen roles similares, su comportamiento y resultados difieren. Aquí compararemos cada función y explicaremos cuándo utilizarlas.

4-1. Función RANK()

La función RANK() asigna rangos, otorgando el mismo rango a valores iguales y omitiendo el número de rango siguiente.

Sintaxis básica

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Ejemplo

Utilizando los datos siguientes, calcule los rangos de ventas.

Ejemplo de consulta: Uso de RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Resultado:

Puntos clave:

• A y B con la misma cantidad de ventas (800) reciben ambos el rango “1”.
• El siguiente rango “2” se omite, por lo que C pasa al rango “3”.

4-2. Función DENSE_RANK()

La función DENSE_RANK() también asigna el mismo rango a valores iguales, pero no omite el número de rango siguiente.

Sintaxis básica

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Ejemplo

Usando los mismos datos anteriores, pruebe la función DENSE_RANK().

Ejemplo de consulta: Uso de DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Resultado:

Puntos clave:

• A y B con la misma cantidad de ventas (800) se tratan ambas como rango “1”.
• A diferencia de RANK(), el siguiente rango comienza en “2”, por lo que se preserva la continuidad del ranking.

4-3. Cómo se diferencia ROW_NUMBER()

La función ROW_NUMBER() se diferencia de las otras dos en que asigna un número único incluso cuando los valores son iguales.

Ejemplo

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Resultado:

Puntos clave:

• Incluso si los valores son iguales, cada fila obtiene un número único, por lo que no hay rangos duplicados.
• Esto es útil cuando necesitas un control estricto del ordenamiento o unicidad por fila.

4-4. Resumen rápido de casos de uso

Resumen

ROW_NUMBER(), RANK() y DENSE_RANK() deben usarse de manera apropiada dependiendo del escenario.

1. ROW_NUMBER() es lo mejor cuando necesitas números únicos por fila.
2. RANK() es útil cuando quieres que los empates compartan un rango y quieres enfatizar las brechas en el rango.
3. DENSE_RANK() es adecuado cuando quieres rangos continuos sin brechas.

5. Alternativas para versiones de MySQL inferiores a 8.0

En versiones anteriores a MySQL 8.0, ROW_NUMBER() y otras funciones de ventana no están soportadas. Sin embargo, puedes lograr un comportamiento similar usando variables definidas por el usuario. Esta sección explica alternativas prácticas para versiones de MySQL inferiores a 8.0.

5-1. Numeración secuencial usando variables definidas por el usuario

En MySQL 5.7 y anteriores, puedes usar variables definidas por el usuario para asignar números secuenciales por fila. Veamos el siguiente ejemplo.

Ejemplo: Ranking de ventas por departamento

Datos de muestra:

Consulta:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Resultado:

5-2. Extracción de las primeras N filas

Para recuperar las primeras N filas, puedes usar variables definidas por el usuario de manera similar.

Consulta:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Resultado:

Esta consulta asigna rangos por departamento y luego extrae solo las filas dentro de las primeras 3.

5-3. Detección y eliminación de duplicados

También puedes manejar datos duplicados usando variables definidas por el usuario.

Ejemplo de consulta: Detectar duplicados

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Ejemplo de consulta: Eliminar duplicados

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Advertencias al usar variables definidas por el usuario

1. Dependencia de sesión

• Las variables definidas por el usuario son válidas solo dentro de la sesión actual. No se pueden reutilizar en diferentes consultas o sesiones.

1. Dependencia del orden de procesamiento

• Las variables definidas por el usuario dependen del orden de ejecución, por lo que establecer ORDER BY correctamente es crítico.

1. Legibilidad y mantenibilidad de SQL

• Las consultas pueden volverse complejas, por lo que en MySQL 8.0 y posteriores, se recomienda usar funciones de ventana.

Resumen

En versiones de MySQL anteriores a la 8.0, puedes usar variables definidas por el usuario para implementar numeración secuencial y ranking en lugar de funciones de ventana. Sin embargo, como las consultas tienden a volverse más complejas, lo mejor es considerar migrar a una versión más reciente siempre que sea posible.

6. Advertencias y Mejores Prácticas

La función ROW_NUMBER() de MySQL y las alternativas basadas en variables son muy convenientes, pero hay puntos importantes que debes tener en cuenta para ejecutarlas con precisión y eficiencia. Esta sección explica advertencias prácticas y mejores prácticas para la optimización del rendimiento.

6-1. Consideraciones de rendimiento

1. Costo de ORDER BY

ROW_NUMBER() siempre se usa con ORDER BY. Dado que requiere ordenar, el tiempo de procesamiento puede incrementarse significativamente para conjuntos de datos grandes.

Mitigación:

• Usar índices: Añade índices a las columnas usadas en ORDER BY para acelerar la ordenación.
• Usar LIMIT: Recupera solo el número de filas que realmente necesitas para reducir la cantidad de datos procesados.

Ejemplo:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Aumento del uso de memoria y E/S de disco

Las funciones de ventana se procesan usando tablas temporales y memoria. A medida que el volumen de datos crece, el consumo de memoria y la E/S de disco pueden incrementarse.

Mitigación:

• Dividir consultas: Divide el procesamiento en consultas más pequeñas y extrae los datos paso a paso para reducir la carga.
• Usar tablas temporales: Almacena los datos extraídos en una tabla temporal y ejecuta la agregación desde allí para distribuir la carga de trabajo.

6-2. Consejos para afinar consultas

1. Verificar el plan de ejecución

En MySQL, puedes usar EXPLAIN para comprobar el plan de ejecución de la consulta. Esto te ayuda a verificar si los índices se están usando correctamente.

Ejemplo:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Salida de ejemplo:

Si ves Using index, indica que el índice se está usando de manera adecuada.

2. Optimizar índices

Asegúrate de añadir índices a las columnas usadas en ORDER BY y WHERE. Presta especial atención a lo siguiente.

• Índices de una sola columna: Útiles para condiciones de ordenación simples
• Índices compuestos: Efectivos cuando varias columnas están involucradas en las condiciones

Ejemplo:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Usar procesamiento por lotes

En lugar de procesar un conjunto de datos enorme de una sola vez, puedes reducir la carga procesando los datos en lotes.

Ejemplo:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Mantener la consistencia de los datos

1. Actualizaciones y recálculo

Cuando se insertan o eliminan filas, la numeración puede cambiar. Construye un mecanismo para recalcular los números según sea necesario.

Ejemplo:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Usar una vista te ayuda a mantener los rankings actualizados según los datos más recientes.

6-4. Ejemplo de consulta con mejores prácticas

A continuación se muestra un ejemplo de mejores prácticas que consideran el rendimiento y la mantenibilidad.

Ejemplo: Extraer las N filas superiores

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Esta estructura usa una expresión de tabla común (CTE) para mejorar la legibilidad y reutilización.

Resumen

Al usar ROW_NUMBER() o sus alternativas, ten en cuenta los siguientes puntos:

1. Mejora la velocidad mediante la optimización de índices.
2. Identifica cuellos de botella revisando el plan de ejecución.
3. Planifica actualizaciones de datos y mantén la consistencia.
4. Utiliza procesamiento por lotes y CTEs para distribuir la carga.

Aplicar estas mejores prácticas permitirá un procesamiento eficiente para el análisis y la generación de informes a gran escala.

7. Conclusión

En este artículo, nos centramos en la función ROW_NUMBER() de MySQL, explicando todo desde su uso básico y ejemplos prácticos hasta alternativas para versiones anteriores, además de advertencias y buenas prácticas. En esta sección, recapitularemos los puntos clave y resumiremos las conclusiones prácticas.

7-1. Por qué ROW_NUMBER() es útil

La función ROW_NUMBER() es especialmente conveniente para el análisis y la generación de informes de datos de las siguientes maneras:

1. Numeración secuencial dentro de grupos: Crea fácilmente rankings de ventas por departamento o rankings basados en categorías.
2. Extracción de las N filas superiores: Filtra y extrae datos de manera eficiente según condiciones específicas.
3. Detección y eliminación de duplicados: Útil para la limpieza y organización de datos.

Debido a que simplifica consultas complejas, mejora significativamente la legibilidad y mantenibilidad del SQL.

7-2. Comparación con otras funciones de ventana

En comparación con funciones de ventana como RANK() y DENSE_RANK(), ROW_NUMBER() difiere en que asigna un número único incluso para valores idénticos.

Elegir la función adecuada:
Seleccionar la mejor función para tu propósito permite un procesamiento de datos eficiente.

7-3. Manejo de versiones antiguas de MySQL

Para entornos anteriores a MySQL 8.0, también presentamos enfoques usando variables definidas por el usuario. Sin embargo, debes considerar estas advertencias:

• Legibilidad reducida debido a un SQL más complejo
• La optimización de consultas puede ser más difícil en algunos casos
• Puede ser necesario un manejo adicional para mantener la consistencia de los datos

Si es posible, considera seriamente migrar a MySQL 8.0 o superior y usar funciones de ventana.

7-4. Puntos clave para la optimización del rendimiento

1. Usar índices: Añade índices a las columnas usadas en ORDER BY para mejorar la velocidad.
2. Revisar planes de ejecución: Valida el rendimiento con anticipación usando EXPLAIN.
3. Adoptar procesamiento por lotes: Procesa grandes conjuntos de datos en fragmentos más pequeños para distribuir la carga.
4. Usar vistas y CTEs: Mejora la reutilización y simplifica consultas complejas.

Aplicando estas técnicas, puedes lograr un procesamiento de datos eficiente y estable.

7-5. Notas finales

ROW_NUMBER() es una herramienta poderosa que puede mejorar significativamente la eficiencia del análisis de datos.
En este artículo, cubrimos todo, desde la sintaxis básica y ejemplos prácticos hasta advertencias y alternativas.

Te animamos a ejecutar las consultas tú mismo mientras sigues este artículo. Mejorar tus habilidades en SQL te ayudará a abordar análisis y reportes de datos más complejos con confianza.

Apéndice: Recursos de referencia

• Documentación oficial: MySQL Window Functions
• Entorno SQL en línea: SQL Fiddle (una herramienta que te permite ejecutar y probar SQL en línea)