Uso de la función Row_Number() en MySQL

1. Uso de ROW_NUMBER() en MySQL usando la cláusula ORDER BY
2. Uso de ROW_NUMBER() en MySQL usando la cláusula PARTITION BY
3. Uso de ROW_NUMBER() en MySQL usando la cláusula PARTITION BY y ORDER BY
4. Replicación de ROW_NUMBER() en MySQL usando la variable de sesión
5. Conclusión

En este tutorial, presentaremos cómo puede usar la función ROW_NUMBER() en MySQL. Es un método de clasificación que asigna números consecutivos dentro de la partición a partir de 1. Es importante tener en cuenta que dos filas dentro de la partición no tienen el mismo número.

También veremos cómo PARTITION BY y ORDER BY afectan los resultados de MySQL. Tienes que usar la cláusula ORDER BY para usar ROW_NUMBER() ya que es obligatorio. Pero la cláusula PARTITION BY es opcional.

Los resultados serán indeterminados si utiliza ambas cláusulas, PARTITION BY y ORDER BY. Aquí, veremos cómo emular la función ROW_NUMBER() utilizando la variable de sesión para obtener los resultados deseados.

Tenga en cuenta que ROW_NUMBER() no estaba disponible antes de la versión 8.0 de MySQL. Puede ver las novedades de la versión 8.0 de MySQL aquí.

Uso de ROW_NUMBER() en MySQL usando la cláusula ORDER BY

Solo usaremos la función ROW_NUMBER() con la cláusula ORDER BY y observaremos los resultados. Primero creemos la tabla y completemos algunos datos en ella.

Código de ejemplo:

# SQL Programming Using MySQL Version 8.27
CREATE TABLE `test_db`.`tb_student` (
 STUDENT_ID	INTEGER NOT NULL,
 FIRST_NAME	VARCHAR(30) NOT NULL,
 LAST_NAME	VARCHAR(30) NOT NULL,
 GENDER	VARCHAR(30) NOT NULL,
 CITY_NAME	VARCHAR(64) NOT NULL,
 EMAIL_ADDRESS	VARCHAR(64) NOT NULL,
 REGISTRATION_YEAR INTEGER NOT NULL,
 PRIMARY KEY	(STUDENT_ID)
);

Esta consulta creará una tabla llamada tb_student, que puede confirmar en la base de datos MySQL.

Inserte los seis registros en la tabla denominada tb_student usando la siguiente sintaxis de la consulta INSERT.

# SQL Programming Using MySQL Version 8.27
INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(1,'Ayush','Kumar', 'Male', 'Washington', 'akuman@yahoo.com', 2010);

Luego seleccione todos los datos de la tabla para verlos usando la siguiente consulta.

# SQL Programming Using MySQL Version 8.27
SELECT * FROM test_db.tb_student

Su tabla tendrá los siguientes datos. También puede verificar en su extremo y comparar.

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(ORDER BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Después de ejecutar la consulta anterior, obtendrá el siguiente resultado.

Observe el resultado anterior y verá que se muestran todos los registros, que están ordenados por año de registro (consulte la columna dentro del cuadro verde). Y el row_number también es el mismo que se esperaba, comenzando desde 1 y aumentando secuencialmente hasta el final de la tabla a medida que leemos todos los datos de tb_student.

Uso de ROW_NUMBER() en MySQL usando la cláusula PARTITION BY

Solo usaremos la función ROW_NUMBER() con la cláusula PARTITION BY y observaremos los resultados. También compararemos esta salida con los resultados que obtuvimos usando ROW_NUMBER() con la cláusula ORDER BY.

Código de ejemplo:

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Ahora, obtendrás los siguientes resultados.

Fíjese en la columna REGISTRATION_YEAR; tiene 5 particiones (2010, 2011, 2012, 2013 y 2014). Hay dos filas en la tabla para partition 2010, y los números de fila se asignan correctamente (vea la captura de pantalla anterior nuevamente). Solo hay una fila para partition 2011, 2012, 2013, 2014; por eso puedes ver 1 en la columna row_numb.

Si estamos usando la cláusula PARTITION BY, ¿por qué la columna se llama REGISTRATION_YEAR en orden ascendente? Porque la cláusula PARTITION BY ordena los datos dentro de esas particiones. Insertemos otro registro para el cual el valor de REGISTRATION_YEAR sea 2009 y observemos los resultados.

# SQL Programming Using MySQL Version 8.27

INSERT INTO test_db.tb_student
(STUDENT_ID, FIRST_NAME, LAST_NAME, GENDER, CITY_NAME, EMAIL_ADDRESS, REGISTRATION_YEAR)
VALUES
(7,'Mashal','Naaz', 'Female', 'Florida', 'mashalnaaz@gmail.com', 2009);

SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_Year) AS row_numb
FROM test_db.tb_student;

Ahora, verá que el registro reciente está en la parte superior.

Uso de ROW_NUMBER() en MySQL usando la cláusula PARTITION BY y ORDER BY

Ahora, solo usaremos la función ROW_NUMBER() con las cláusulas PARTITION BY y ORDER BY y veremos si todavía proporciona los números de fila correctos.

Código de ejemplo:

# SQL Programming Using MySQL Version 8.27
SELECT *,
    ROW_NUMBER() OVER(PARTITION BY REGISTRATION_YEAR ORDER BY REGISTRATION_YEAR) AS row_numb
FROM test_db.tb_student;

Después de ejecutar la consulta anterior, verá el mismo resultado que obtuvimos al usar ROW_NUMBER() con la cláusula PARTITION BY. Vea la siguiente captura de pantalla:

Ver la columna con fondo amarillo, esto es lo que esperábamos. Aquí, usaremos la variable de sesión para asignar números de fila correctamente.

Replicación de ROW_NUMBER() en MySQL usando la variable de sesión

MySQL no proporciona la funcionalidad de clasificación correcta cuando usamos simultáneamente las cláusulas PARTITION BY y ORDER BY. En este escenario, emulamos esto usando Variable de sesión. Las variables de sesión están definidas por el usuario; puede verlo aquí para obtener información detallada.

Código de ejemplo:

# SQL Programming Using MySQL Version 8.27
SET @row_numb = 0;
SELECT *,
    (@row_numb:=@row_numb + 1) AS row_numb
FROM test_db.tb_student ORDER BY REGISTRATION_YEAR;

Como puede ver a continuación, row_numb comienza desde 1 y aumenta consecutivamente.

¿Cómo está funcionando? Primero configuramos una variable de sesión row_numb usando el prefijo @ e inicializamos con el 0. Luego seleccionamos los datos de la tabla, los ordenamos y los imprimimos. (@row_numb:=@row_numb + 1) es como incrementar y actualizar el valor de la variable.

Conclusión

A la luz de la discusión anterior, hemos llegado a la conclusión de que, aunque también podemos usar la función ROW_NUMBER() en MySQL si tenemos la versión 8.0 o superior, todavía hay algunas situaciones en las que tenemos que usar Variables de sesión para fines de clasificación.