¿Cuándo debo utilizar las funciones de ventana de SQL?

¿Ha oído a alguno de sus colegas presumir de utilizar las funciones de ventana de SQL? ¿Conoce el SQL básico pero no sabe mucho, o nada, sobre las funciones de ventana? Si quieres aprender sobre las funciones de ventana y cómo utilizarlas en un contexto empresarial, estás en el lugar adecuado.

Esto sucede a menudo. Alcanzas un cierto nivel de conocimientos y te sientes el rey (o la reina) del mundo. Entonces, oyes a tus colegas hablar de algo de lo que nunca has oído hablar (en este caso, las funciones de ventana). Inmediatamente te sientes un poco avergonzado por no conocer ya las funciones de ventana. ¿Le resulta familiar?

Este sentimiento no es agradable. Pero puede ser bueno si te lleva a preguntar qué son las funciones de ventana. Entonces, lo buscas en Google y encuentras algunos artículos útiles (como éste). De repente, la vergüenza se convertirá en empoderamiento cuando te des cuenta de que las funciones de ventana no son misteriosas ni inaprensibles. De nuevo, te sientes el rey del mundo. O la reina. Quizá incluso las dos cosas.

¡Avancemos hacia esta sensación de realeza! Intentaré no bombardearte con SQL. En su lugar, haré el camino inverso. Daré varios ejemplos empresariales del mundo real que te mostrarán el uso práctico de las funciones de ventana.

¿Qué es Funciones de ventana?

Una función ventana es, en pocas palabras, una función que realiza cálculos en un conjunto de filas de la tabla. El nombre proviene del hecho de que el conjunto de filas se llama ventana o marco de ventana.

Este es un ejemplo del aspecto de una ventana. En la tabla de abajo, las ventanas están marcadas con diferentes colores. La suma acumulada se calcula para cada región, por lo que en este caso, las ventanas se definen por región.

La sintaxis de la función ventana es:

window_function ([ALL] expression)
OVER ([PARTITION BY partition_list] [ORDER BY order_list] [window_frame_clause])

La parte de la sintaxis de la función ventana puede ser una función agregada, como SUM() o AVG(). También puede ser otro tipo de función, como una función analítica o de clasificación.

La ventana, o el número de filas sobre las que se realizarán ciertos cálculos, se define mediante la cláusula OVER().

PARTITION BY es una cláusula opcional en las funciones de ventana. Define una partición sobre la que la función ventana realizará la operación. Si no se define PARTITION BY, la función ventana realizará la operación en toda la tabla. Puede obtener más información en este artículo.

ORDER BY ordenará las filas dentro de cada partición en el orden deseado. Si no se define esta cláusula, entonces la cláusula utilizará toda la tabla.

La cláusula de marco de ventana define el número de filas sobre las que operará la función de ventana utilizando dos palabras clave. Una es ROWS, utilizada para limitar el número de filas especificando el número de filas que preceden o siguen a la fila actual. La otra es RANGE, utilizada para limitar el número de filas especificando un rango de valores con respecto al valor de la fila actual. Por eso la cláusula de marco de ventana también se llama cláusula ROW o RANGE.

Para aprender a utilizar esta sintaxis y practicarla, el curso LearnSQL.es sobre funciones de ventana es el lugar adecuado. Además, si te interesa una mayor explicación de las funciones de ventana, puedes encontrarla aquí, junto con algunos ejemplos.

Pasemos ahora a la parte divertida, ¡la resolución de problemas!

Ejemplo 1: Calcular el salario medio y comparar el salario de Individual con la media

Este es un ejemplo sencillo de un problema común en el mundo de los negocios. Digamos que hay una tabla employeeque contiene datos sobre los salarios de los empleados. Consta de las siguientes filas:

• id - id único
• first_name - nombre del empleado
• last_name - apellido del empleado
• department - departamento del empleado
• salary - salario mensual del empleado

Con estos datos, primero hay que calcular el salario medio de toda la empresa. A continuación, tienes que calcular cuánto está por encima o por debajo del salario medio de cada empleado. El siguiente código lo hará rápidamente:

SELECT	first_name,
		last_name,
		department,
		salary,
		AVG(salary) OVER() AS avg_salary,
		salary - AVG(salary) OVER() AS diff_salary
FROM employee;

Como estás familiarizado con SQL, probablemente reconozcas al menos parte de este código. Selecciona first_name, last_name, department, y salary de la tabla employee. No hay nada nuevo aquí.

La línea AVG(salary) OVER() AS avg_salary calcula el salario medio en la ventana definida por OVER(). El resultado se mostrará en la columna avg_salary.

La línea salary - AVG(salary) OVER() AS diff_salary calcula la diferencia entre el salario de cada empleado y el salario medio, El resultado se mostrará en la columna diff_salary.

Al ejecutar este código se obtendrá una tabla útil. Vea un extracto de la misma a continuación:

Ejemplo 2: Calcular el número medio de productos vendidos por fecha y región

Imagine que trabaja en una empresa que opera en tres regiones. La dirección quiere saber el número medio de productos vendidos en cada región. Además, quieren saber el número medio de productos vendidos en general para cada fecha. Las funciones de ventana le permitirán hacerlo fácilmente.

En este ejemplo, los datos se almacenan en la tabla sales que tiene tres columnas:

• date - fecha de la venta del producto
• region - nombre de la región
• products_sold - número de productos vendidos

Aquí está el código necesario para crear ese informe:

SELECT	date,
		region,
		products_sold,
		AVG(products_sold) OVER(PARTITION BY date) AS avg_date,
		AVG(products_sold) OVER(PARTITION BY region) AS avg_region
FROM sales
ORDER BY region, date;

Este código selecciona las columnas date, region, y products_sold. Luego, calcula el promedio de productos vendidos en cada fecha. Esto está definido por la cláusula PARTITION BY(). El resultado se mostrará en la columna avg_date.

La siguiente línea también calcula el número medio de productos vendidos, esta vez en cada región. El resultado se mostrará en la columna avg_region. A continuación, el resultado se ordena por la región y la fecha utilizando la cláusula ORDER BY.

Vea los resultados a continuación:

Ejemplo 3: Calcular la suma acumulada de productos vendidos por región

La gerencia estaba contenta con su informe anterior. Ahora, quieren que calcule la suma acumulativa (o el total acumulado) de los productos vendidos en cada región. Las funciones de ventana son útiles para realizar estos cálculos.

El cálculo se realizará en la tabla sales utilizada en el ejemplo 2. Este código le permitirá entregar rápidamente los números requeridos:

SELECT	date,
		region,
		products_sold,
		SUM(products_sold) OVER(PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_region
FROM sales
ORDER BY region, date;

La primera parte del código selecciona las mismas columnas que el código del Ejemplo 2. Luego, calcula la suma de los productos por región. Esto está definido por la cláusula PARTITION BY(). Por supuesto, se necesita la suma acumulada, no la suma total. Por eso se ordena la ventana por la fecha utilizando el comando ORDER BY.

Ahora que todo está definido, hay que decirle a SQL que sume el valor de la fila actual a la suma de las filas anteriores dentro de la ventana. Esto se hace mediante ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

La función de ventana sumará todas las filas anteriores a la fila actual (por eso UNBOUNDED PRECEDING) y ninguna fila posterior a la fila actual (por eso CURRENT ROW) dentro de la ventana especificada. El resultado se mostrará en la columna cumulative_region.

El resultado se ordenará por la región y la fecha, como en el ejemplo anterior.

Después de ejecutar el código, obtendrá la siguiente tabla:

Ejemplo 4: Comparar los precios de las acciones con los precios mínimos y máximos del mercado

Su empresa está monitoreando los precios de las acciones en NASDAQ y NYSE. La gerencia quiere que usted calcule los precios mínimos y máximos de las acciones en ambos mercados en los últimos 365 días. También quieren ver cómo el precio de cada acción negociada en 2020 difiere del precio mínimo y máximo en el mercado.

La tabla stockprice contiene datos del 15/3/2019 al 14/3/2020 y consta de las siguientes columnas:

• date - fecha de la negociación
• stock_name - nombre de la acción
• stock_price - precio de la acción
• stock_market - mercado en el que se negocia la acción
• max_price - precio máximo en el mercado en los últimos 365 días
• min_price - precio mínimo en el mercado en los últimos 365 días
• diff_max - precio de la acción menos el precio máximo en el mercado en los últimos 365 días
• diff_min - precio de la acción menos el precio mínimo en el mercado en los últimos 365 días

El código que creará rápidamente el informe solicitado tiene el siguiente aspecto:

SELECT	date,
		stock_name,
		stock_price,
		stock_market,
		MAX(stock_price) OVER(PARTITION BY stock_market) as max_price,
		MIN(stock_price) OVER(PARTITION BY stock_market) as min_price,
		stock_price - MAX(stock_price) OVER(PARTITION BY stock_market) AS diff_max,
		stock_price - MIN(stock_price) OVER(PARTITION BY stock_market) AS diff_min
FROM stockprice
WHERE date > '2019-12-31'
ORDER BY date;

La primera parte del código selecciona las columnas originales de la tabla: date, stock_name, stock_price, y stock_market. Luego, la función de ventana MAX() con la cláusula PARTITION BY calcula el precio máximo para cada mercado, NASDAQ y NYSE, por separado. El resultado se mostrará en la columna max_price.

La siguiente línea de código funciona de la misma manera, excepto que ahora calcula el precio mínimo. El resultado se mostrará en la columna min_price.

Las siguientes líneas de código calculan la diferencia entre el precio de la acción y el precio máximo y mínimo, respectivamente, para cada mercado. Los resultados se mostrarán en las columnas diff_max y diff_min.

Como el informe debe mostrar sólo los datos de 2020, he utilizado la cláusula WHERE. Por último, la tabla resultante está ordenada por la fecha, lo cual es lógico para un informe de este tipo.

Este es el aspecto de las primeras filas del informe:

Ejemplo 5: Calcular el porcentaje de cambio de precio y las medias móviles

La dirección sabe ahora que puede entregar informes con gran precisión y rapidez. Están impresionados. No se dan cuenta de que usted ha estado aprendiendo las funciones de la ventana SQL y que lo que le pidieron es fácil para usted.

A continuación, le piden algo que debería llevarle mucho más tiempo. Pero no lo hará. Tienes una tabla de precios que contiene todos los price cambios de una acción en 2020. A veces sólo hay un cambio diario, a veces hay más. La tabla consta de las siguientes columnas:

• date - fecha de la cotización
• stock_price - precio de la acción

La dirección te ha pedido que les envíes un informe que tome cada precio y lo compare con el precio anterior. Además, te han pedido que calcules la media móvil del precio de las acciones. Así es como lo harás:

SELECT	date,
		stock_price,
		(stock_price/LAG(stock_price) OVER(ORDER BY date)) - 1 AS percent_change,
AVG(stock_price) OVER(ORDER BY date ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING) AS moving_avg
FROM price;

¿Qué hace este código? Primero, selecciona las columnas originales de la tabla: date y stock_price.

La siguiente línea introduce una nueva función de ventana, LAG(). Esta función accede a los datos de la fila anterior, lo que es ideal para esta tarea. El stock_price se divide por el precio anterior (de ahí la función LAG() ). A continuación, se resta 1 al resultado para obtener un porcentaje. El resultado se mostrará en la columna percent_change.

La siguiente línea calcula la media móvil. Utiliza la función de ventana AVG(), que ya conoces. En la cláusula OVER(), los datos se ordenan por la fecha.

La dirección no ha especificado cómo quiere que se calcule la media móvil. Así que he decidido calcularla utilizando cinco cambios de precio, lo que se especifica en ROWS BETWEEN 0 PRECEDING AND 4 FOLLOWING. Al calcular la media móvil, el código tendrá en cuenta la fila actual y las cuatro filas siguientes, es decir, cinco en total.

Puede jugar con esa parte del código, ya que no hay un número definido de datos que deban tenerse en cuenta al calcular las medias móviles. Depende de la cantidad y el tipo de datos, así como de las preferencias individuales. Cambiando los números que vienen antes de PRECEDING y FOLLOWING, puede cambiar fácil y rápidamente el cálculo, dependiendo de la metodología que decida utilizar.

Vea los resultados a continuación:

¿Crees que aprender Funciones de ventana puede ayudarte en tu trabajo?

He dado ejemplos de negocios del mundo real como punto de partida en lugar de un puro análisis de código de funciones de ventana. Los cinco ejemplos son algo que he tratado en mi carrera.

Esta es la forma en que aprendí SQL. Primero, tuve un problema que resolver. Luego, traté de averiguar cómo hacerlo usando SQL. Si crees que las funciones de ventana serán útiles para tu trabajo, este curso de LearnSQL.es es una buena manera de aprender más.

Si has encontrado estos ejemplos interesantes o quieres compartir algunos de tus ejemplos del mundo real, ¡siéntete libre de comentar abajo!