¿Por qué debería aprender las funciones de ventana de SQL?

SQL existe desde hace más de 25 años. Su sintaxis básica -como SELECT, WHERE, GROUP BY, HAVING y ORDER BY- es bien conocida. Pero, ¿hay algo más que el SQL tradicional? ¿Puede ir más allá de lo básico y mejorar sus habilidades?

La respuesta a esta pregunta es sí. Existe una versión moderna de SQL. En este artículo, vamos a sumergirnos en la versión moderna y a aprender sobre las funciones de ventana de SQL. Si eres un principiante avanzado que quiere analizar datos con la ayuda de las funciones de ventana de SQL (también llamadas funciones analíticas), este artículo es para ti.

Breve historia de SQL

Historia del desarrollo de SQL - estandarización

SQL es un lenguaje famoso pero muy antiguo. Fue introducido en los años 70 por IBM. En 1986, los grupos de normalización ANSI e ISO adoptaron oficialmente una definición estándar de "Lenguaje de Bases de Datos SQL" (SQL-1986).

En 1992, se realizó una importante revisión (SQL- 92) de la norma inicial. Es el SQL estándar que utilizamos hoy en día. Así es, han pasado 28 años desde que se adoptó oficialmente el SQL-92. Sus conceptos básicos (SELECT, WHERE, etc.) son familiares, no importa si estás usando Oracle, DB2, MySQL, PostgreSQL, o alguna otra base de datos. En cada DBMS, escribirás sentencias SQL similares debido a esa misma estandarización.

Los conceptos básicos de SQL son antiguos, pero son muy útiles. Yo diría que son universales, ya que funcionan independientemente del sector en el que se apliquen. Para aprender SQL, o incluso para crecer como analista, vas a tener que aprender estos conceptos.

Aun así, el mundo de la informática ha cambiado mucho desde 1992. ¿Seguro que SQL ha evolucionado desde entonces? Sí, SQL ha evolucionado y se han hecho nuevas revisiones de los estándares. En la década de 2000 comenzó el SQL moderno, un concepto que va más allá de las sentencias básicas. En 2003, se introdujeron las funciones de ventana de SQL. Hablemos de las ventajas de las funciones de ventana de SQL y de por qué es bueno conocerlas.

Primero, sin embargo, revisemos algo que los novatos suelen confundir con las funciones de ventana: las funciones agregadas de SQL.

Un repaso a las funciones agregadas

Si está familiarizado con el SQL tradicional, probablemente haya utilizado las funciones de agregación , que le permiten realizar cálculos en conjuntos de filas y obtener una única fila de salida o resultado. Por ejemplo, quizás haya calculado algunos totales o promedios sobre un conjunto de filas o quizás haya contado el número de filas por categoría. En ese caso, ha utilizado al menos algunas de las funciones de agregación de SQL: SUM() AVG() , MIN(), MAX(), y COUNT(). A menudo se utilizan con las cláusulas GROUP BY y HAVING dentro de las sentencias SELECT.

Veamos un ejemplo: cómo podemos calcular el precio medio por grupo de filas utilizando GROUP BY.

Vamos a utilizar datos de Forex sobre los tipos de cambio. Esta es nuestra tabla de entrada:

Tipos de cambio - tabla CURRENCYTRADE

La tabla contiene los precios de cierre de los cambios GBP-USD y EUR-USD. En este ejemplo, vamos a utilizar seis precios de cierre para cada par de divisas. A partir de estos datos, vamos a calcular el precio medio de cierre de cada par de divisas (GBPUSD, EURUSD) por separado.

Esta es la sentencia SELECT que calcula el precio medio de cierre para cada par de divisas:

select ticker,avg(close) as average_price from CURRENCYTRADE group by ticker;

La imagen siguiente muestra el resultado a la derecha:

El resultado se presenta como una salida de una sola fila para cada par de divisas. Los cálculos (el valor medio de cada par) se realizaron en seis filas para cada par. Este es un ejemplo sencillo de una función agregada.

Ahora, vamos a sumergirnos en las funciones de ventana.

¿Qué es una función ventana SQL?

¿Son las funciones ventana de SQL similares a la sintaxis del agregado GROUP BY? Me gusta pensar que sí. Al igual que una función agregada utilizada con una cláusula GROUP BY, una función ventana también realiza cálculos en un conjunto de filas. Sin embargo, el resultado de una función ventana no se presenta como una única fila de salida por cada grupo; es decir, las filas no se contraen en la tabla resultante. En su lugar, se devuelve cada fila de la tabla de entrada.

La diferencia entre una función agregada y una función ventana en SQL es sencilla. Una función de agregación contrae todas las filas en un único resultado, lo que significa que se pierde el acceso a las filas individuales. Una función de ventana permite el acceso a cada fila en la ventana definida. Esto se muestra en la siguiente imagen:

Diferencia entre las funciones de agregación y de ventana de SQL

Para aprender las funciones ventana de SQL, recomiendo el curso interactivo Funciones de ventana curso en LearnSQL.es.

Volvamos a nuestro ejemplo. Si ejecuta esta parte del código...

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

... el precio medio de cierre de cada par de divisas se asignará a cada fila de la CURRENCYTRADE tabla.

El resultado será algo así:

Como puede ver, la función de ventana no agrupó la salida en una sola fila de salida por grupo de pares de divisas. En su lugar, cada fila contiene ahora información adicional: el precio medio de cierre para el par de divisas correspondiente. Esto puede ser muy útil, ya que muchos análisis requerirán alguna información adicional para cada fila mientras se mantienen todas las columnas del conjunto de datos inicial.

Sintaxis de la función ventana

En nuestro último ejemplo de función ventana, utilizamos algunas palabras clave especiales como OVER() y PARTITION BY:

select *,avg(close) OVER(PARTITION BY ticker) AS average_closing from CURRENCYTRADE ;

Estas son las principales palabras clave que definen una función ventana. He aquí una breve explicación:

1. OVER indica que se trata de una función ventana. Dentro de la cláusula OVER, podemos tener cláusulas PARTITION, ORDER BY y otras cláusulas de marco de ventana que nos indican cómo se enmarca la ventana (es decir, los grupos y cómo se ordenan). Aunque no hemos utilizado la cláusula de marco de ventana (ROW o RANGE) en nuestro ejemplo, tenga en cuenta que puede utilizar cualquiera de ellas dentro de la cláusula OVER para especificar marcos dentro de las particiones.
2. La cláusulaPARTITION nos indica cómo se agrupan o enmarcan los datos. Se coloca dentro de la cláusula OVER. En nuestro ejemplo, calculamos el precio promedio para cada par de divisas; por lo tanto, particionamos nuestra ventana por la columna ticker.
3. ORDER BY (que no usamos en nuestro ejemplo) se usa a menudo para determinar el orden de las filas dentro de cada marco.
4. ROW o RANGE se utiliza si queremos limitar aún más las filas dentro de la partición. Lo hacemos especificando los puntos inicial y final dentro de la partición. No importa cuál de estas dos cláusulas se emplee, debe usarse con ORDER BY. La sintaxis es la siguiente:

[ROWS | RANGE] BETWEEN  AND 

La <fila_inicial> se denota con una de las siguientes:

• PRECEDENTE NO LIMITADO: La ventana comienza en la primera fila de la partición.
• FILA ACTUAL: La ventana comienza en la fila actual.
• <literal entero sin signo> PRECEDENTE o SIGUIENTE.

Y el <fila_final> utiliza uno de los siguientes:

• UNBOUNDED FOLLOWING: La ventana termina en la última fila de la partición.
• FILA ACTUAL: La ventana termina en la fila actual.
• <literal entero sin signo> PRECEDENTE o SIGUIENTE.

Puedes encontrar más ejemplos de cláusulas de marco de ventana aquí.

No te preocupes si no te sientes cómodo con esta sintaxis. La práctica ayudará; puedo recomendar LearnSQL.es's Funciones de ventana que tiene mucha y buena información.

Ahora que ha aprendido cómo puede utilizar las funciones analíticas de SQL en sus consultas, el siguiente paso es echar un vistazo a los tipos de funciones de ventana disponibles en SQL. Hasta ahora, sólo hemos mostrado cómo AVG() ( una función agregada) puede ser utilizada como una función ventana. Veamos qué pueden hacer las demás funciones.

Tipos de funciones ventana

Hay tres tipos principales de funciones ventana:

• Funciones de ventana agregadas: AVG(), MIN(), MAX(), COUNT(), SUM(). Estas funciones sirven para calcular valores medios, mínimos o máximos, el número total de filas o la suma total dentro de cada marco definido. Las funciones de ventana agregada devuelven un único valor para cada fila de la consulta subyacente.

• Funciones de ventana de clasificación: RANK(), ROW_NUMBER(), y similares. Las funciones de ventana de clasificación se utilizan para clasificar las filas dentro de cada marco. Por ejemplo, RANK() clasificará un valor en un grupo de valores. La expresión ORDER BY en la cláusula OVER determina el valor del rango. Cada valor se clasifica dentro de su partición. Las filas con valores iguales para los criterios de clasificación reciben el mismo rango.

  Veamos otro ejemplo de función de ventana de clasificación. ROW_NUMBER() determina el número ordinal de la fila actual dentro de su partición. Una vez más, el ORDER BY en la cláusula OVER determina este número. Cada valor se ordena dentro de su partición.

• Funciones de ventana de valores: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(). Estas funciones son realmente útiles para informar y resumir los datos. Las funciones de ventana LAG() y LEAD() devuelven el valor de la fila anterior (LEAD()) o posterior (LAG()) a la fila actual en una partición. Si no existe ninguna fila, se devuelve un null. Del mismo modo, la función de ventana FIRST_VALUE() / LAST_VALUE() devuelve el valor de la expresión especificada para la primera (o última) fila del marco de la ventana.

Ya hemos visto un ejemplo de cómo utilizar las funciones de ventana agregadas, así que puede entender por qué son útiles en el análisis de datos. En realidad, los tres tipos de funciones de ventana de SQL se utilizan con frecuencia en análisis complejos. Son una gran característica de SQL.

¿Necesita un ejemplo de la vida real? Bien, suponga que está en un negocio de comercio de divisas. A menudo, cuando vende o compra posiciones, examina el precio de cierre del minuto anterior o de la hora anterior; para ello, utilizaría la función LAG(). También podría clasificar los precios de cierre utilizando funciones de ventana de clasificación dentro de una ventana de marco de tiempo específico. O podría encontrar el precio de cierre inicial o final utilizando funciones de ventana de valor.

Utilizando funciones de ventana SQL: LAG()

Profundicemos en el uso de las funciones analíticas de SQL en la vida real. Para cada fila, veamos el precio de cierre de la fila anterior. Utilizaremos una función ventana de clasificación:

select *,LAG(close) OVER(PARTITION BY ticker ORDER BY datetime) AS previous_close from CURRENCYTRADE;

Vamos a utilizar LAG(), que devuelve el valor de la fila anterior. Aquí, el OVER denota que se trata de una función de ventana en la que estamos agrupando filas por par de divisas. Como estamos utilizando LAG(), también necesitamos una cláusula ORDER BY para ordenar los datos dentro de cada marco antes de asignar los precios de cierre de la fila anterior. Estamos ordenando los datos por la columna fecha-hora, lo que significa que tendremos el precio de cierre del minuto anterior en cada fila actual. La imagen siguiente muestra el aspecto de la salida:

Función Window lag

Este código es más sencillo y fácil de mantener. Esa es una de las principales ventajas de utilizar funciones de ventana. De hecho, tienen muchos beneficios, como veremos.

Ventajas del uso de SQL Funciones de ventana

Las funciones ventana son útiles cuando no es necesario contraer filas en el conjunto de resultados, es decir, agrupar los datos de los resultados en una única fila de salida. En lugar de una única fila de salida, se devuelve un único valor para cada fila de la consulta subyacente. Esta es la principal ventaja, en mi opinión.

Algunas de las otras ventajas de las funciones analíticas de SQL son

• Las funciones de ventana le permiten reunir valores agregados y no agregados a la vez. Esto se debe a que para cada valor de fila devuelto, no hay agrupación o colapso de esa fila. Puede mantener todas las columnas de cada fila y añadir valores adicionales calculados por la función de ventana. Esto es una gran ventaja cuando se necesitan valores agregados y no agregados en una tabla.
• Su sintaxis es sencilla y es más fácil mantener el código en producción. Imagine cuánto tiempo le llevaría implementar el equivalente a la función LEAD(), LAG(), o RANK() utilizando el SQL tradicional. ¡O simplemente asignar promedios a cada fila sin funciones de ventana! Tendría que utilizar primero la función agregada GROUP BY, seguida de un LEFT JOIN con la tabla de datos de entrada original. Su código sería más complicado y difícil de mantener. Aquí hay un artículo interesante que muestra cómo un ejemplo puede ser resuelto tanto con cursores como con funciones analíticas SQL. El código de la función analítica es más limpio y sencillo, ¿no crees?
• Se puede asignar fácilmente a una fila actual un valor de una fila anterior o de una fila sucesiva. En algunas bases de datos, esta opción es mucho más rápida que utilizar una solución con el cursor o una subconsulta correlativa. Estos códigos son más complejos y difíciles de mantener. Aquí hay un buen artículo que compara el rendimiento (funciones de ventana vs. cursor vs. subconsulta) en una base de datos MS SQL. Genial, ¿verdad?

Aprenda más sobre SQL Funciones de ventana

La mayoría de los usuarios de SQL son principiantes avanzados (según el modelo Dreyfus) y puede que no conozcan realmente el SQL moderno. Saber cómo utilizar las funciones de ventana es una técnica más avanzada, pero es una que realmente vale la pena aprender. Si decide aprender las funciones de ventana, busque un curso que tenga muchos ejemplos de funciones analíticas de SQL utilizadas en casos empresariales concretos. Y, sobre todo, busca uno con muchos ejercicios para practicar. No olvides que cuanto más practiques, más rápido y mejor aprenderás.

Además de los cursos online, siempre puedes consultar el blog de LearnSQL. Está lleno de artículos interesantes que explican las funciones de ventana, como por ejemplo

• Ejemplo de función de ventana SQL con explicaciones
• SQL Común Funciones de ventana: Funciones Posicionales
• Cómo Usar Funciones de Rango en SQL
• Common SQL Funciones de ventana: Uso de Particiones con Funciones de Rango