Una visión general de las funciones de ventana de MySQL

Las funciones de ventana de MySQL son muy útiles cuando desea crear informes significativos con SQL. En este artículo, mostraremos las funciones de ventana más comunes en MySQL y explicaremos cómo usarlas.

MySQL 8.0 introdujo una nueva característica: las funciones ventana. Estas funciones son muy útiles para los analistas de datos y para cualquiera que cree informes utilizando MySQL. Usándolas, puede calcular fácilmente medias móviles, sumas acumulativas y otros cálculos sobre subconjuntos específicos de sus datos. Y puede hacerlo sin crear complejas consultas SQL o tablas temporales.

Si es un usuario habitual de SQL, es posible que se haya encontrado con funciones de ventana en su trabajo. Mientras que muchas bases de datos han tenido estas funciones durante algún tiempo, MySQL se quedó atrás hasta 2018. Con MySQL 8, ¡la plataforma ya está al día!

En este artículo, exploraremos los beneficios de las funciones de ventana en MySQL y cómo pueden mejorar su análisis de datos. Si desea practicar el uso de las funciones de ventana, consulte nuestro curso interactivo Funciones de ventana en MySQL 8. Ofrece más de 200 ejercicios interactivos sobre las funciones de ventana de MySQL.

¿Qué es Funciones de ventana en MySQL?

Una función ventana SQL realiza cálculos a través de un conjunto de filas de la tabla que están relacionadas con la fila actual. Este conjunto de filas se llama ventana o marco de ventana - de ahí viene el término "funciones ventana".

Empecemos con un ejemplo sencillo. Imagine que desea calcular la suma de los valores de todas las filas, pero quiere que el resultado se muestre en cada fila. Podría necesitar esta información para comparar valores individuales con el total durante el análisis de datos.

Esto es muy sencillo si sabe utilizar las funciones de ventana. El resultado de su consulta tendría este aspecto:

Se habrá dado cuenta de que las funciones ventana son similares a las funciones agregadas. Ambas calculan un valor agregado para un determinado grupo de filas. Sin embargo - a diferencia de la cláusula GROUP BY - las funciones ventana en SQL no contraen las filas. En su lugar, la tabla resultante muestra tanto los valores individuales como los agregados. Esto puede ser útil en informes en los que se necesita trabajar con valores agregados y no agregados al mismo tiempo.

Cómo definir una función ventana: La cláusula OVER()

Las funciones ventana se definen utilizando la cláusula OVER():

SELECT …,
   <window_function> OVER(...),
   …
FROM …

La cláusula OVER() indica a la base de datos que utilice una función ventana. La forma más sencilla del marco de ventana es cuando los corchetes se dejan vacíos, como en este caso: OVER(). Esto significa que la ventana está formada por todas las filas de la tabla.

Se pueden incluir cláusulas adicionales dentro de la cláusula OVER() para definir aún más la ventana. En este artículo, nos centraremos en las cláusulas PARTITION BY y ORDER BY. Existen otras cláusulas que se pueden utilizar dentro de OVER(), pero no las trataremos en este artículo. Si desea profundizar aún más, consulte nuestro curso Funciones de ventana en MySQL 8. O puede visitar este artículo sobre las funciones ventana de MySQL que proporciona algunos grandes ejemplos de cómo usarlas en sus consultas.

Ejemplos de consultas con funciones ventana en MySQL

Veamos algunos ejemplos de consultas para entender mejor dónde y cómo puede hacer uso de las funciones ventana.

En nuestro ejemplo, tenemos un sitio web que permite a los usuarios participar en concursos. Hay varias categorías de cuestionarios y el número máximo de puntos que pueden obtener los participantes es 100.

Para almacenar las puntuaciones de los participantes, este sitio web utiliza la tabla participant tabla. Tiene las siguientes columnas

• id - El ID del participante, que es también la clave primaria (PK) de la tabla.
• name - El nombre del participante.
• quiz_score - Puntuación del participante.
• quiz_date - La fecha en la que se realizó el cuestionario.
• quiz_category - La categoría del cuestionario.

Aquí puedes ver un puñado de filas de la tabla:

Ahora que ya conoces los datos, vamos a utilizar las funciones de ventana. Puede que encuentres útil esta hoja de trucos de SQL Funciones de ventana como guía de referencia rápida mientras repasamos los ejemplos.

Ejemplo 1: Cláusula OVER() vacía - Cálculo de una estadística para todas las filas

Supongamos que queremos obtener la puntuación de cada participante, la categoría del cuestionario que han intentado y la puntuación más alta alcanzada en todos los cuestionarios. Podemos hacerlo utilizando una cláusula vacía OVER(). De esta forma, nuestra ventana incluirá todas las filas de la consulta.

Esta es la consulta que ejecutaríamos:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER() AS max_score
FROM participant;

Y el resultado será el siguiente:

La función MAX() se aplicó a todas las filas de nuestra consulta. Puede ver que la puntuación más alta fue 100 y se muestra para todas las filas junto con las puntuaciones individuales.

Puede utilizar la cláusula vacía OVER() con otras funciones, como COUNT(), SUM(), AVG(), y otras. Esto le permite calcular una estadística global para todas las filas de la consulta; puede comparar esta estadística global con el valor de cada fila individual.

Puede leer más sobre el uso de la cláusula OVER() en MySQL en nuestro artículo ¿Qué es la cláusula OVER de MySQL?

Ejemplo 2: OVER() con ORDER BY - Cálculo de una clasificación

Cuando se utiliza dentro de la cláusula OVER(), ORDER BY determina el orden en el que se ordenan las filas en el marco de la ventana. Veamos un ejemplo:

Podemos utilizar esta consulta para crear una clasificación de las puntuaciones de los cuestionarios:

SELECT 
  name, 
  quiz_score,
  quiz_category,
  RANK() OVER(ORDER BY quiz_score DESC) AS rank
FROM participant;

La función de ventana RANK() asigna un rango a cada fila dentro de una partición; este rango se basa en el valor de una expresión especificada. La primera fila obtiene el rango 1, la segunda fila obtiene el rango 2, etc.

Más concretamente, la función RANK() asigna un rango único a cada valor distinto de la expresión dentro de la partición. Las filas con el mismo valor tendrán el mismo rango, y se omitirá el rango siguiente. Por ejemplo, si dos filas tienen el mismo valor y reciben un rango de 1, el siguiente rango asignado será 3, saltándose el rango 2. Puede leer más sobre las funciones de ventana de clasificación en SQL en nuestro blog.

Aquí, utilizamos la función RANK() para calcular la clasificación de la puntuación de cada participante en el cuestionario. La cláusula OVER() con la cláusula ORDER BY determina el orden en el que se aplica la función RANK(). En este caso, la cláusula ORDER BY se establece en quiz_score DESC, lo que significa que las puntuaciones del cuestionario se ordenan en orden descendente (de mayor a menor) antes de calcular la clasificación. La primera fila (con el valor más alto) obtiene el rango 1, la segunda fila obtiene el rango 2, etc.

Esto es lo que devuelve el código:

¡Bien hecho! Hemos podido asignar un rango a cada participante.

Utilice la cláusula OVER (ORDER BY) en MySQL cuando desee aplicar una función a las filas en un orden específico. Esto puede ser útil para calcular totales, medias móviles y crear varias clasificaciones.

Ejemplo 3: OVER() con PARTITION BY - Cálculo de una estadística para cada partición

Vamos a hacer más uso de la columna categoría. ¿Recuerda el primer ejemplo de consulta? Para cada participante, mostramos su puntuación, la categoría del cuestionario que intentó y la puntuación más alta alcanzada en todos los cuestionarios. Esta vez queremos hacer algo parecido. Sin embargo, en lugar de mostrar la puntuación más alta alcanzada en todos los cuestionarios, mostraremos la puntuación más alta alcanzada en la categoría de ese cuestionario.

Para ello, necesitaremos la cláusula OVER() con PARTITION BY. Particionar datos en SQL significa dividir un conjunto de filas en grupos más pequeños basados en una columna o columnas especificadas. Es un poco similar a la cláusula GROUP BY, pero las funciones de ventana no contraen las filas.

Podemos utilizar esta consulta:

SELECT 
  name,
  quiz_score,
  quiz_category,
  MAX(quiz_score) OVER(PARTITION BY quiz_category) 
    AS max_score_in_category
FROM participant;

La cláusula PARTITION BY con la cláusula OVER() determina la partición de datos sobre la que se aplica la función MAX(). En este caso, la cláusula PARTITION BY se establece en quiz_category, lo que significa que los datos se dividen en particiones basadas en la categoría del cuestionario. Eso es exactamente lo que queríamos.

He aquí el resultado:

Para cada participante, mostramos tanto su puntuación individual como la puntuación más alta de su categoría. Parece justo, ya que el cuestionario de música podría haber sido más difícil que el de ciencias.

OVER (PARTITION BY) en MySQL es útil para calcular sumas acumulativas o valores medios, crear clasificaciones dentro de grupos, identificar los mejores o peores resultados, y mucho más. Esta cláusula proporciona flexibilidad y funcionalidad avanzada a las consultas SQL, permitiendo un potente análisis y manipulación de datos dentro de subconjuntos de datos. Puede obtener más información sobre el uso de SQL PARTITION BY con OVER en otro lugar de nuestro blog.

Ha sido fácil, ¿verdad? Intentemos algo más complicado.

Ejemplo 4: OVER() con PARTITION BY y ORDER BY - Cálculo de una estadística para cada partición en un orden específico

¿Podemos utilizar PARTITION BY y ORDER BY al mismo tiempo? Por supuesto que sí. Esta combinación es útil en muchas situaciones.

En MySQL, el uso de la cláusula OVER() con PARTITION BY y ORDER BY permite realizar cálculos y análisis en particiones específicas de datos mientras se controla el orden en que se aplican los cálculos dentro de cada partición.

La cláusula PARTITION BY divide el conjunto de resultados en particiones distintas basadas en columnas o expresiones especificadas. Cada partición se trata por separado para el cálculo o análisis.

La cláusula ORDER BY, cuando se utiliza con OVER(), determina el orden en que se procesan los datos dentro de cada partición. Especifica la columna o expresión por la que deben ordenarse los datos.

Veamos este dúo en acción. En este ejemplo, calcularemos la media acumulada de las puntuaciones de los cuestionarios por categoría. Una media acumulada es la media de un conjunto de valores hasta un cierto punto.

Esta es la consulta que utilizaremos:

SELECT
  name,
  quiz_date,
  quiz_score,
  quiz_category,
  ROUND(
    AVG(quiz_score) 
OVER(PARTITION BY quiz_category ORDER BY quiz_date)
  ) AS cumulative_avg
FROM participant;

Queremos ver la media acumulada de las puntuaciones de los cuestionarios por categoría. Para conseguirlo, utilizamos PARTITION BY quiz_category, igual que hicimos la última vez. Además, tiene sentido que la media acumulada se calcule desde la fecha más antigua a la más reciente, así que usamos ORDER BY quiz_date. Esto significa que los datos dentro de cada partición se ordenan por la fecha del cuestionario en orden ascendente (de mayor a menor) antes de aplicar la función AVG().

Este es el aspecto de la consulta:

Al utilizar PARTITION BY y ORDER BY juntos dentro de la cláusula OVER(), el cálculo de la media se aplica por separado para cada categoría de cuestionario. Dentro de cada categoría, se calcula en el orden de las fechas de los cuestionarios. Esto significa que para cada fila del conjunto de resultados, el cálculo de la media considera sólo las filas dentro de la misma categoría de cuestionario y las ordena por fecha. La columna cumulative_avg reflejará la puntuación media hasta la fila actual para cada categoría de cuestionario, teniendo en cuenta el orden de las fechas de los cuestionarios.

¿Listo para Practicar MySQL Funciones de ventana?

Como puede ver, las funciones ventana en MySQL son una herramienta muy poderosa que puede ayudarle a crear informes complejos. Puede utilizar las funciones de ventana en MySQL para crear rankings y calcular métricas interanuales, medias móviles, ¡y mucho más!

En este artículo, sólo hemos arañado la superficie de todas las formas en que puede utilizar las funciones ventana. Si desea profundizar y explorar más ejemplos de cómo utilizarlas, consulte estos artículos sobre las funciones ventana de SQL y la cláusula OVER() de MySQL.

Y para más práctica, recuerde consultar nuestro curso Funciones de ventana en MySQL 8.