Guía de funciones de ventana SQL

Todos los analistas de datos pueden beneficiarse del conocimiento de las funciones ventana de SQL. Mejoran el análisis de datos y abren las puertas a un nivel completamente nuevo. Este artículo es tu punto de partida en el mundo de las funciones de ventana SQL.

Las funciones de ventana SQL le permiten mostrar todas las filas de datos y sus valores agregados al mismo tiempo. Suena un poco a GROUP BY con esteroides, ¿verdad? Pero la cosa no acaba ahí. Como las funciones de ventana realizan varios cálculos en el conjunto de filas, también permiten dividir los conjuntos de datos en subconjuntos. Te ayudan a clasificar datos fácilmente, agregarlos, calcular diferencias entre distintos periodos (por ejemplo, aumento/disminución de beneficios entre dos meses/trimestres/años) y encontrar sumas acumulativas, totales corridos, medias móviles, etc.

Las funciones de ventana son un tema complejo, pero aprenderlas es mucho más fácil con nuestro Funciones de ventana curso. Es un curso interactivo con 218 ejercicios que proporcionan un aprendizaje sistemático con mucha codificación. Aprenderá todo sobre las cláusulas esenciales de las funciones ventana - por ejemplo, OVER(), ORDER BY, y PARTITION BY - y qué es un marco de ventana. A continuación, aprenderás a utilizar todo eso en el contexto de diferentes funciones de ventana.

Después de aprender, es hora de practicar (que también es aprender). Nuestro Funciones de ventana Practice Set le ofrece 100 ejercicios interactivos adicionales para que pueda poner en práctica sus conocimientos.

La sintaxis de SQL Funciones de ventana

Las funciones de ventana reciben su nombre de un marco de ventana, que es un conjunto de filas relacionadas con la fila actual.

Para realizar una operación de función de ventana en el marco de ventana, necesita conocer la sintaxis general de la función de ventana:

SELECT column_1,
       column_2,
	<window_function> OVER(PARTITION BY … ORDER BY … <window_frame>) AS column_alias
FROM table;

Hay varias partes esenciales de esta sintaxis que requieren explicación:

• Cláusula OVER: Es la cláusula obligatoria para definir un marco de ventana. Por ejemplo, reconocería la función de ventana SUM(order_value) OVER() as a SUM(). Sin OVER(), no es más que una función agregada SUM() normal.
• CLÁUSULAPARTITION BY: Es una cláusula opcional para particionar el conjunto de datos, es decir, dividirlo en subconjuntos. Permite aplicar una función de ventana a cada partición por separado. Si se omite esta cláusula, todo el conjunto de resultados es una partición.
• ORDER BY: Esta cláusula opcional (para algunas funciones de ventana) se utiliza para especificar el orden de las filas en un marco de ventana. Si omite esta cláusula, el orden de las filas en el marco de la ventana será arbitrario.
• <window_frame>: Define los límites superior e inferior de un marco de ventana. Dos cláusulas importantes utilizadas para ello son ROWS y RANGE. ROWS define el número de filas que preceden y siguen a la fila actual. La cláusula RANGE define el rango de filas basándose en su valor comparado con la fila actual. Puede obtener más información en nuestro artículo sobre las diferencias entre FILAS y RANGO. Esta parte de la sintaxis suele omitirse, ya que el marco de ventana por defecto es lo que los usuarios suelen necesitar.

El marco de ventana por defecto, en este caso, depende de si utiliza la cláusula ORDER BY en OVER(). Si lo hace, entonces el marco es toda la fila actual y todas las filas anteriores a ella en la partición actual. Si no especifica ORDER BY, el marco de la ventana es la fila actual y todas las filas que la preceden y la siguen en la partición actual. En el segundo caso, el marco de la ventana es básicamente todo el conjunto de datos - o toda la partición, si también utiliza PARTITION BY.

SQL común Funciones de ventana

Existen muchas funciones de ventana diferentes. He aquí un resumen de algunas de las más comunes:

SQL Funciones de ventana Ejemplos

Ahora que ya hemos repasado los conceptos básicos, es hora de mostrar varios ejemplos prácticos de funciones ventana.

En todos los ejemplos, utilizaré la misma tabla. Se titula exchange_rates y contiene los tipos de cambio de abril de 2024 del Banco Central Europeo (BCE) para tres pares de divisas: EUR frente a USD, EUR frente a CHF y EUR frente a JPY.

He aquí una instantánea parcial de los datos.

Ejemplo de función de ventana de clasificación

Este ejemplo le mostrará cómo funciona DENSE_RANK(). Las otras dos funciones de ventana de clasificación se pueden utilizar de la misma manera; podrían (dependiendo de los datos) devolver resultados ligeramente diferentes.

El siguiente código ordena los datos de la tabla del tipo de cambio más alto al más bajo:

SELECT date,
	 currency_pair,
	 ecb_rate, 
	 DENSE_RANK() OVER (ORDER BY ecb_rate DESC) AS rank_ecb_rate
FROM exchange_rates;

Selecciono la fecha, el par de divisas y el tipo de cambio. Ahora, he elegido DENSE_RANK() para clasificar los datos. Esto es sólo en caso de que haya los mismos tipos de cambio (muy poco probable, pero aún así...); quiero que se clasifiquen igual, y no quiero huecos en la clasificación.

La función DENSE_RANK() va seguida de la cláusula OVER() que define la función ventana. En el paréntesis de la cláusula, utilizo otra cláusula de función ventana:ORDER BY. De esta forma, le estoy diciendo a la función de ventana DENSE_RANK() que ordene los datos por el tipo del BCE en orden descendente.

Esto es lo que obtengo como resultado:

Como puede ver, cada fila se ha ordenado según el valor de su tipo. En el caso de los mismos tipos, DENSE_RANK() asignaría la misma clasificación y no omitiría la clasificación. RANK() haría lo mismo, sólo que omitiría la secuencia de clasificación. ROW_NUMBER() asignaría una clasificación consecutiva, incluso si algunas filas tienen el mismo tipo de cambio.

Ejemplo de Función de Ventana Agregada

He aquí un buen ejemplo de cómo puede utilizar la función de ventana AVG() para calcular el tipo medio de cada par de divisas:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 AVG(ecb_rate) OVER (PARTITION BY currency_pair) AS avg_rate_by_currency
FROM exchange_rates
ORDER BY currency_pair, date;

Al igual que en la función agregada normal AVG(), debe escribir la columna de la que desea obtener el promedio. Para convertirla en una función ventana, utilice la cláusula OVER(). Esta vez, utilizo PARTITION BY en la columna currency_pair. Al hacer esto, estoy dividiendo los datos en subconjuntos según el par de divisas.

En pocas palabras, estoy calculando el tipo medio de cada par de divisas por separado.

Eche un vistazo al código de salida. Se ha ordenado por pares de divisas y fecha:

El tipo medio EUR vs. CHF es 0,9793, y este valor se repite para cada fila EUR vs. CHF. Cuando la función de ventana llega al siguiente par de divisas, la media se reinicia y se calcula de nuevo; para EUR frente a JPY, es 164,1211. Por último, la media de EUR frente a USD es 1,0795.

Esta función de ventana me ha permitido calcular las medias por separado y mostrar los valores medios sin colapsar las filas individuales. En otras palabras, puedo ver cada cotización diaria junto con la media de ese par de divisas.

Ejemplos de la Función de Ventana Analítica

En esta sección, mostraré tres ejemplos de diferentes funciones analíticas de ventana SQL.

LAG()

El ejemplo LAG() demostrará cómo calcular el cambio diario. Esta función se utiliza para acceder al valor de las filas anteriores. Otra función de ventana analítica es LEAD(), que hace exactamente lo contrario: obtiene los datos de las filas siguientes. Ambas funciones tienen básicamente la misma sintaxis; sólo hay que cambiar el nombre de la función.

En este ejemplo, quiero calcular la variación diaria de los tipos de cambio:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - LAG(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS daily_rate_change
FROM exchange_rates;

LAG() es una función que permite acceder a los valores de la(s) fila(s) anterior(es) a la fila actual. En el ejemplo anterior, especifico la columna ecb_rate en LAG(). Esto significa que quiero acceder al valor del tipo de cambio del BCE. No especifico explícitamente el número de filas a las que quiero volver, por lo que será el valor por defecto de una fila.

OVER() viene después del nombre de la función. En este caso, divido el conjunto de datos por pares de divisas, ya que quiero calcular la variación diaria del tipo de cada par de divisas por separado.

También utilizo ORDER BY para ordenar los datos dentro de las particiones. Como la lógica es retroceder un día, los datos deben ordenarse ascendentemente por fecha.

Así, la parte LAG() del código representa el tipo de cambio del día anterior. Para obtener la diferencia diaria, simplemente resto este valor del tipo de cambio actual (ecb_rate - LAG(ecb_rate)).

Este es el resultado:

La primera fila es NULL porque no hay fecha anterior, por lo que no se puede calcular la diferencia. En la fila siguiente, la variación diaria del tipo de cambio es 0,9792-0,9765 = 0,0027. El mismo principio de tomar el valor de la fila anterior y restarlo del actual se continúa con cada fila.

Como el conjunto de datos está dividido por pares de divisas, el cálculo se reinicia cuando se llega a otros pares de divisas, es decir, EUR frente a JPY y EUR frente a USD.

FIRST_VALUE()

La función de ventana FIRST_VALUE() se puede utilizar en nuestros datos para calcular las diferencias entre el tipo de cambio actual y el primero del mes.

Puedo hacerlo porque FIRST_VALUE() devuelve el primer valor dentro de la partición. Su opuesto es LAST_VALUE(), que devuelve el último valor de la partición. Ambas funciones tienen básicamente la misma sintaxis; sólo el nombre de la función es diferente (¡y el resultado, por supuesto!).

SELECT date,
	 currency_pair,
	 ecb_rate,
	 ecb_rate - FIRST_VALUE(ecb_rate) OVER (PARTITION BY currency_pair ORDER BY date) AS difference_current_first
	  FROM exchange_rates
ORDER BY currency_pair, date;

Quiero el valor de la tasa; por eso está la columna ecb_rate en FIRST_VALUE(). Los datos vuelven a estar divididos por pares de divisas, ya que quiero un cálculo para cada par.

Bien, entonces FIRST_VALUE() se utiliza para obtener el valor de la primera fila de la partición. Pero, ¿qué ocurre si ordeno los datos de la partición ascendentemente por fecha? Así es; la primera fila es la que tiene la tasa del primer día del mes. En nuestro caso, es el primer tipo de cambio de abril.

Ahora, réstalo del tipo de cambio actual. Como sabemos que nuestros datos son sólo de abril, obtenemos la diferencia entre el tipo de cambio actual y el primero de ese mes.

Cuando la diferencia es 0, los tipos actual y anterior son iguales. En el caso del EUR frente al CHF, el primer tipo es 0,9765. Comprobemos las dos primeras filas: 0. 9765 - 0 .9765 = 0.0000; 0.9792 - 0.9765 = 0.0027.

El mismo principio se aplica a los otros dos pares de divisas.

NTILE()

El último ejemplo que mostraré es la función NTILE(), que divide la ventana (o partición) en grupos. El argumento entre paréntesis de la función NTILE() especifica el número de grupos en que desea dividir el conjunto de datos.

La división se hará cronológicamente ordenando los datos ascendentemente por fecha:

SELECT date,
	 currency_pair,
	 ecb_rate,
	 NTILE(3) OVER (ORDER BY date) AS group_number
FROM exchange_rates;

Y éste es el resultado:

Los datos se dividen en tres grupos. Como hay 27 filas de datos en total, se podrían dividir en grupos iguales de nueve filas.

Funciones de ventana vs. Funciones GROUP BY y Agregadas

Las funciones agregadas de ventana, como puedes suponer por su nombre, se utilizan para agregar datos. Pero, ¿qué pasa con las funciones agregadas "normales" y GROUP BY? También se utilizan para agregar datos. ¿En qué se diferencian las funciones de ventana?

La principal diferencia es que las funciones de ventana de agregación (y las funciones de ventana en general) no contraen las filas individuales mientras muestran el valor agregado. Por otro lado, GROUP BY y las funciones de agregación sólo pueden mostrar los valores agregados; contraen las filas individuales.

En pocas palabras, las funciones de ventana permiten mostrar los datos analíticos y agregados al mismo tiempo.

SQL avanzado Uso de las Funciones de Ventana

Las funciones de ventana tienen un amplio uso en el análisis de datos, ya que pueden resolver muchos requisitos de informes empresariales.

A continuación se ofrece un resumen de algunos de los usos más complejos de las funciones ventana.

Clasificación

Sí, ya hablé de la clasificación y le mostré un ejemplo de cómo hacerlo. Existen tres funciones de ventana para clasificar datos: ROW_NUMBER(), RANK(), y DENSE_RANK(). No necesariamente devuelven el mismo resultado, ya que todas tienen formas ligeramente diferentes de clasificar los datos. La que utilices dependerá de tus datos y de lo que quieras conseguir.

Ejecución de Totales y Medias Móviles

Ambos cálculos se utilizan normalmente en el análisis de series temporales. Las series temporales son datos que muestran valores en determinados momentos del tiempo. El análisis de dichos datos es, bueno, análisis de series temporales. Su objetivo es desvelar tendencias en los datos y encontrar posibles causas de desviaciones significativas de la tendencia.

El total acum ulado (o suma acumulada) es la suma de la fila actual y todos los valores de las filas anteriores. A medida que se avanza hacia el futuro, el tamaño del marco temporal aumenta en una fila/punto de datos y el valor se añade al total acumulado de la fila anterior.

Las medias móviles son el valor medio de los últimos n periodos. A medida que se avanza hacia el futuro, el marco temporal se desplaza, pero su tamaño permanece invariable. Esto se utiliza mucho en el sector financiero, por ejemplo, una media móvil de 5 días en el análisis del precio de las acciones. De esta forma, el precio medio se actualiza continuamente y se neutraliza el impacto de los cambios significativos a corto plazo en el precio de las acciones.

Diferencia entre dos filas o periodos de tiempo

La función de ventana utilizada para calcular la diferencia entre dos filas es LAG(), que permite acceder a los valores de las filas anteriores. La diferencia entre dos periodos de tiempo es básicamente lo mismo; sólo se refiere a encontrar diferencias cuando se trabaja con series temporales. En la sección de ejemplos, le mostré cómo hacerlo.

Análisis de series temporales

Las funciones de ventana funcionan a las mil maravillas cuando se necesita analizar series temporales. No sólo existe la función LAG() para hacerlo, sino muchas otras.

Problemas Comunes con SQL Funciones de ventana

Hay varios problemas con los que todo el mundo que utiliza funciones ventana se encuentra tarde o temprano:

1. Confundir las funciones ventana con funciones agregadas y GROUP BY, de las que ya hemos hablado.
2. Intentar usar funciones ventana en WHERE. Esto no puede hacerse porque SQL procesa las condiciones WHERE antes que las funciones ventana.
3. Intentar usar funciones ventana en GROUP BY, lo cual tampoco está permitido debido al orden de las operaciones de SQL: las funciones ventana se ejecutan después de GROUP BY.

Recursos Adicionales para Practicar SQL Funciones de ventana

Las funciones ventana de SQL se encuentran entre las herramientas SQL más útiles de las que disponen los analistas de datos. Esto es especialmente cierto cuando se va más allá de los informes básicos y se requieren cálculos sofisticados y la capacidad de mostrar datos analíticos y agregados simultáneamente.

Todos los temas que he tratado en este artículo requieren más práctica en ejemplos prácticos, que puedes encontrar en los siguientes artículos y cursos:

1. 11 Ejercicios de SQL Funciones de ventana con Soluciones
2. Top 10 Preguntas de la Entrevista SQL Funciones de ventana
3. SQL Funciones de ventana Hoja de trucos
4. Funciones de ventana Curso
5. Funciones de ventana Conjunto de prácticas

Recuerda: ¡la práctica hace al maestro! Así que no se limite a leer los artículos; asegúrese también de realizar algunas prácticas de codificación. ¡Feliz aprendizaje!