Guía de ROW_NUMBER en MySQL: básico, ejemplos y alternativas

1. Introducción

Audiencia objetivo

• Principiantes a intermedios con conocimientos básicos de SQL
• Ingenieros y analistas de datos que utilizan MySQL para procesamiento y análisis de datos
• Personas que están considerando migrar a la última versión de MySQL

Ventajas de la función ROW_NUMBER()

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

Sintaxis básica de la función ROW_NUMBER

SELECT
    columna,
    ROW_NUMBER() OVER (PARTITION BY columna_grupo ORDER BY columna_orden) AS numero_fila
FROM
    nombre_tabla;

Significado de cada elemento

• ROW_NUMBER(): Función que asigna un número secuencial a cada fila.
• OVER: Palabra clave que define la función ventana.
• PARTITION BY: Agrupa los datos por la columna especificada. Es opcional; si no se indica, se asigna una secuencia a todas las filas.
• ORDER BY: Especifica el orden en que se asignan los números, permitiendo definir el criterio de clasificación.

Ejemplo básico de uso

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

Resultado de la ejecución

Cómo usar PARTITION BY

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

Resultado de la ejecución

Características y consideraciones de la función ROW_NUMBER()

• Numeración única: Incluso con valores idénticos, los números secuenciales son únicos.
• Manejo de valores NULL: Si ORDER BY incluye NULL, se colocan al inicio en orden ascendente y al final en orden descendente.
• Impacto en el rendimiento: En conjuntos de datos grandes, la carga de procesamiento de ORDER BY puede ser alta, por lo que es importante configurar índices adecuados.

3. Ejemplos prácticos de uso

3-1. Clasificación por grupos

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

3-2. Extracción de los N mejores registros

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;

3-3. Extracción y eliminación de datos duplicados

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

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

1. Clasificación por grupos
2. Extracción de los N mejores registros
3. Detección y eliminación de datos duplicados

4. Comparación con otras funciones de ventana

4-1. Función RANK()

Sintaxis básica

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

Ejemplo de uso

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

• Los empleados A y B, que tienen el mismo monto de ventas (800), se consideran en el «1.º» lugar.
• El siguiente rango, «2.º», se omite, y C ocupa el «3.º» lugar.

4-2. Función DENSE_RANK()

Sintaxis básica

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY order_column) AS rank
FROM
    table_name;

Ejemplo de uso

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

• Los empleados A y B, con el mismo monto de ventas (800), se consideran en el «1.º» lugar.
• A diferencia de RANK(), el siguiente rango comienza en «2.º», manteniendo la continuidad de los rangos.

4-3. Diferencias con la función ROW_NUMBER()

Ejemplo de uso

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

• Incluso si los valores son iguales, se asigna un número único, por lo que no hay duplicación de rangos.
• Es adecuado cuando se necesita controlar estrictamente el orden de los datos o cuando se requiere unicidad por fila.

4-4. Resumen de usos de cada función

Resumen

1. ROW_NUMBER() es adecuado cuando se necesita un número único.
2. RANK() es útil cuando se desea asignar el mismo rango a datos con valores iguales y resaltar los huecos en el ranking.
3. DENSE_RANK() es apropiado para un ranking continuo, sin huecos entre rangos.

5. Métodos alternativos para versiones anteriores a MySQL 8.0

5-1. Numeración secuencial usando variables de usuario

Ejemplo de uso: ranking de ventas por departamento

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;

5-2. Extracción de los N registros superiores

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;

5-3. Detección y eliminación de datos 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;

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. Consideraciones sobre variables de usuario

1. Dependencia de sesión

• Las variables de usuario solo son válidas dentro de la sesión. No pueden usarse en otras consultas o sesiones.

1. Dependencia del orden de procesamiento

• Las variables de usuario dependen del orden de ejecución de la consulta, por lo que la configuración de la cláusula ORDER BY es importante.

1. Legibilidad y mantenibilidad del SQL

• El código tiende a volverse complejo, por lo que se recomienda usar funciones de ventana en MySQL 8.0 o superior.

Resumen

6. Consideraciones y mejores prácticas

6-1. Consideraciones de rendimiento

1. Carga del ORDER BY

• Uso de índices: Establezca índices en las columnas usadas en ORDER BY para acelerar el proceso de ordenación.
• Uso de LIMIT: Obtenga solo la cantidad de datos necesaria y reduzca el volumen de datos a procesar.

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 de I/O de disco

• División de consultas: Divida el procesamiento en consultas más pequeñas y extraiga los datos de forma incremental para reducir la carga.
• Uso de tablas temporales: Almacene los datos extraídos en una tabla temporal y realice la agregación desde allí para distribuir la carga.

6-2. Puntos clave para el afinado de consultas

1. Verificación del plan de ejecución

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

2. Optimización de índices

• Índice de columna única: Aplicable cuando la condición de ordenación es simple
• Índice compuesto: Útil cuando la condición incluye varias columnas

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

3. Uso de procesamiento por lotes

SELECT * FROM sales WHERE department = '営業部' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = '営業部' LIMIT 1000 OFFSET 1000;

6-3. Mantenimiento de la integridad de los datos

1. Actualización y recálculo de datos

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

6-4. Mejores prácticas de ejemplos de consultas

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;

Resumen

1. Optimización de índices para mejorar la velocidad de procesamiento.
2. Verificación del plan de ejecución para identificar cuellos de botella de rendimiento.
3. Adaptación a actualizaciones de datos considerando la implementación de mecanismos para mantener la integridad.
4. Procesamiento por lotes y CTE para distribuir la carga.

7. Resumen

7-1. Ventajas de la función ROW_NUMBER()

1. Asignación de números secuenciales por grupo: Permite realizar fácilmente rankings de ventas por departamento y clasificación por categoría.
2. Extracción de los N primeros registros: Permite extraer y filtrar datos basados en condiciones específicas de manera eficiente.
3. Detección y eliminación de datos duplicados: Es útil para la organización y limpieza de datos.

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

7-3. Estrategias para versiones anteriores

• Reducción de la legibilidad debido a la mayor complejidad del código
• Puede ser difícil optimizar el procesamiento de consultas
• Se requieren procesos adicionales para mantener la integridad de los datos

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

1. Uso de índices: Configurar índices en las columnas utilizadas en ORDER BY para mejorar la velocidad de procesamiento.
2. Revisión del plan de ejecución: Utilizar la sentencia EXPLAIN para validar el rendimiento anticipadamente.
3. Adopción de procesamiento por lotes: Dividir el procesamiento de conjuntos de datos grandes en partes más pequeñas para distribuir la carga.
4. Uso de vistas y CTE: Aumentar la reutilización del código y simplificar la organización de consultas complejas.

7-5. Conclusión

Apéndice: Recursos de referencia

• Documentación oficial: Funciones de ventana de MySQL
• Entorno de ejecución SQL: SQL Fiddle (herramienta en línea para ejecutar y probar SQL)