¿Qué es la cláusula OVER de MySQL?

Si quiere aprender las funciones de ventana en MySQL, necesita entender la cláusula OVER. En este artículo, utilizamos ejemplos del mundo real para explicar qué es la cláusula OVER de MySQL, cómo funciona y por qué es tan impresionante.

En 2018, MySQL introdujo una nueva característica: las funciones de ventana, a las que se accede a través de la cláusula OVER. Las funciones de ventana son un recurso súper potente disponible en casi todas las bases de datos SQL. Realizan un cálculo específico (por ejemplo, suma, recuento, promedio, etc.) en un conjunto de filas; este conjunto de filas se llama "ventana" y se define mediante la cláusula MySQL OVER.

En este artículo, explicaremos cómo utilizar la cláusula OVER de MySQL en diferentes escenarios. También le presentaremos varias funciones de ventana. Espero que, después de leer este artículo, te convenzas de que vale la pena aprender las funciones de ventana. ¡También conocerás los fundamentos de cómo aplicarlas en tus consultas!

Funciones de ventana: Una función muy solicitada

Las funciones de ventana han estado disponibles en la mayoría de las principales bases de datos desde hace bastante tiempo, pero hasta 2018 no estaban disponibles en MySQL. Para mantener MySQL al día, las funciones de ventana se introdujeron en MySQL 8.02. Si planeas trabajar con la versión 8 de MySQL, vale la pena aprender las funciones de ventana y la cláusula OVER, ya que son muy potentes.

¿Cuándo utilizaría las funciones ventana? Hay muchas ocasiones en las que resultan útiles, por ejemplo, para calcular el salario medio de un determinado grupo de empleados. En este caso, la definición del grupo es el punto central; no quieres la media de los salarios de todos los empleados, y si te equivocas de grupo el resultado será erróneo. La definición de un grupo de registros es la razón de ser de la cláusula OVER: dicta dónde trabajará la función ventana.

A lo largo de este artículo, crearemos algunos ejemplos de consulta basados en una base de datos que contiene registros de un grupo de agricultores que producen naranjas. Los agricultores comparten sus datos de producción, que se almacenan en la tabla orange_production tabla que se ve a continuación:

El caso de uso más sencillo es una cláusula OVER vacía; esto significa que la ventana de registros es el conjunto completo de registros devueltos por la consulta. Por ejemplo, si nuestros agricultores quieren tener un informe de cada registro de agricultores junto con el total de la producción de naranjas en 2017, escribiríamos esta consulta:

SELECT farmer_name, 
       kilos_produced, 
       SUM(kilos_produced) OVER() total_produced
 FROM  orange_production 
WHERE  crop_year = 2017

Aquí, la cláusula OVER construye una ventana que incluye todos los registros devueltos por la consulta, es decir, todos los registros del año 2017. El resultado es:

Cómo funciona un marco de ventana deslizante

Ese fue un ejemplo muy simple de la cláusula MySQL OVER. La ventana de registros era estática (la ventana era la misma para todas las filas devueltas por la consulta). Sin embargo, uno de los puntos fuertes de la cláusula OVER es poder crear una ventana dinámica de registros (también llamada marco de ventana deslizante). Los marcos de ventana deslizantes o dinámicos significan que la ventana de registros puede ser diferente para cada fila devuelta por la consulta. Además, la ventana se crea en función de la fila actual de la consulta, por lo que las filas de la ventana pueden cambiar cuando cambia la fila actual.

Veamos un ejemplo de ventana deslizante. Supongamos que nuestros agricultores quieren ver su propia producción junto con la producción total de la misma variedad de naranja.

SELECT farmer_name, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety) AS total_same_variety
FROM orange_production 

La cláusula OVER(PARTITION BY orange_variety) crea ventanas agrupando todos los registros con el mismo valor en la columna orange_variety. Esto nos da dos ventanas: 'Golden' y 'SuperSun'. En la tabla siguiente, cada ventana se muestra con un color diferente:

Ahora puede ver el resultado de la consulta:

Observe que la columna Total Same Variety (en el extremo derecho) incluye la producción de todos los años. Quizá cada agricultor prefiera comparar su producción con la producción total de la misma variedad en el mismo año. Esta comparación les permite ver su parte de la tasa de producción. Para ello, tenemos que añadir la columna crop_year a la cláusula PARTITION BY. La consulta será la siguiente:

SELECT farmer, 
       orange_variety, 
	 crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY orange_variety, crop_year)                                                                                                                    AS total_same_variety_year
   FROM orange_production 

La cláusula OVER(PARTITION BY orange_variety, crop_year) crea ventanas agrupando todos los registros con el mismo valor en las columnas orange_variety y crop_year. A continuación, hemos vuelto a utilizar diferentes colores para mostrar las ventanas de filas creadas por esta cláusula OVER:

Y los resultados de la consulta son:

Si estás interesado en profundizar en las funciones ventana de MySQL, te sugiero el curso LearnSQL.es Funciones de ventana, donde puedes encontrar una descripción completa de este tema y varios ejemplos. Si vas a utilizar las funciones ventana con frecuencia, esta hoja de trucos - una guía rápida súper completa de las funciones ventana - es muy útil.

Pero prosigamos con nuestra propia exploración de OVER y las funciones ventana en MySQL primero.

Aprendiendo la Cláusula OVER de MySQL con un ejemplo

En esta sección, exploraremos varias consultas de ejemplo que muestran diferentes usos de la cláusula OVER en MySQL.

En primer lugar, utilizaremos la subcláusula ORDER BY en la cláusula OVER. ORDER BY generará una ventana con los registros ordenados por un criterio definido. Algunas funciones (como SUM(), LAG(), LEAD() y NTH_VALUE()) pueden devolver diferentes resultados dependiendo del orden de las filas dentro de la ventana. Supongamos que el agricultor Pierre quiere conocer su producción acumulada a lo largo de los años:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(ORDER BY crop_year) cumulative_previous_years
 FROM  orange_production
WHERE  farmer = ‘Pierre’

La función de ventana SUM(kilos_produced) OVER(ORDER BY crop_year) trabaja sobre una ventana ordenada. Y sólo tiene en cuenta las filas actuales y anteriores (es decir, los valores actuales y anteriores de crop_year ). Podemos ver el resultado de este acumulado SUM() en la tabla de resultados:

En el siguiente ejemplo, combinaremos dos subcláusulas (PARTITION BY y ORDER BY) en la cláusula OVER. Supongamos que los agricultores quieren un informe que muestre el total producido por cada agricultor cada año y el total de los años anteriores. Entonces tenemos que particionar por la columna farmer y ordenar por crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced, 
       SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) cumulative_kilos_produced
   FROM orange_production

La siguiente imagen muestra las ventanas particionadas por farmer_name en diferentes colores; observe que dentro de cada ventana, las filas están ordenadas por crop_year.

La expresión SUM(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) ordena las filas en la partición (que se basa en el valor farmer ) utilizando los valores de crop_year. Puede ver esto en la tabla de resultados:

Para más información sobre la combinación de las cláusulas PARTITION BY y ORDER BY, consulte el artículo SQL Funciones de ventana por explicación.

Uso de Funciones Posicionales en Ventanas Ordenadas

Así que, ahora puede ver la importancia de poder ordenar las filas dentro de una ventana. De hecho, algunas funciones de ventana de MySQL sólo funcionan en ventanas ordenadas. Por ejemplo, la función LAG() nos permite obtener un valor de columna de la fila anterior (relacionada con la fila actual) en una ventana ordenada.

Digamos que queremos un informe que muestre la producción del año anterior comparada con la producción del año actual. Para ello, utilizamos la función LAG() en una ventana ordenada por crop_year:

SELECT farmer, 
       crop_year,
       kilos_produced AS current_year_production, 
       LAG(kilos_produced) OVER(PARTITION BY farmer 
ORDER BY crop_year)AS previous_year_production
  FROM orange_production

La función LAG() puede devolver cualquier valor de columna de la fila anterior a la actual, como muestra el siguiente resultado de la consulta:

La función LEAD() devuelve cualquier valor de columna de la fila posterior a la fila actual. Además, la función NTH_VALUE() devuelve la fila en cualquier posición especificada en una ventana ordenada (por ejemplo, primera, segunda, última). Estas funciones de ventana de MySQL le ayudan a crear fácilmente informes complejos.

Probemos una operación matemática utilizando las funciones de ventana de MySQL. Supongamos que los agricultores quieren un informe con el delta de producción relacionado con el año anterior. Podemos utilizar la función LAG() para calcular la diferencia entre las producciones del año anterior y del actual:

SELECT farmer, 
       crop_year,
       kilos_produced current_year_production, 
       kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer 
                         ORDER BY crop_year) AS production_delta
   FROM orange_production

La expresión kilos_produced - LAG(kilos_produced) OVER(PARTITION BY farmer ORDER BY crop_year) calcula el delta de producción entre el año anterior y el actual, como podemos ver en los resultados:

Para aquellos lectores que quieran profundizar en las funciones posicionales, sugiero el artículo Funciones de ventana comunes de SQL: Funciones Posicionales, que tiene varios ejemplos de este interesante tipo de función de ventana.

Más sobre MySQL OVER y Funciones de ventana

En este artículo, cubrimos varios ejemplos de uso de la cláusula OVER de MySQL, desde usos muy simples hasta bastante complejos. Saber cómo funciona OVER y qué filas incluye en la ventana es fundamental para utilizar las funciones de ventana.

Hay muchas funciones de ventana de MySQL con las que puede experimentar: AVG(), MIN(), MAX(), LAG(), LEAD() y NTH_VALUE(). Todas ellas utilizan la cláusula OVER de la misma manera que acabamos de explicar.

Finalmente, para aquellos lectores que quieran aprender más sobre las funciones ventana de MySQL, recomiendo el curso interactivo LearnSQL Funciones de ventana. Puedes leer más sobre él en nuestro post Curso SQL del Mes - Funciones de ventana. En el curso encontrarás explicaciones detalladas y muchos ejemplos de uso de diferentes funciones de ventana. Y recuerde: ¡cuando aumenta sus conocimientos de MySQL, aumenta su patrimonio!