Funciones de ventana SQL vs. Funciones agregadas SQL: Similitudes y diferencias

Si no está familiarizado con las funciones ventana de SQL, puede preguntarse en qué se diferencian de las funciones agregadas. ¿Cuándo debe utilizar las funciones ventana? En este artículo, revisaremos las funciones ventana y las funciones agregadas, examinaremos sus similitudes y diferencias, y veremos cuál elegir dependiendo de lo que necesite hacer.

Después de haber abordado el SQL básico, probablemente querrás adentrarte en algunas de sus funciones más avanzadas. Eso está muy bien; estas funciones facilitan la elaboración de informes y el análisis.

Sin embargo, muy pronto se encontrará con dos misteriosos grupos de funciones: las funciones de ventana y las funciones de agregación. ¿Qué hacen? ¿En qué se diferencian?

Está a punto de descubrirlo.

¿Qué son las funciones agregadas de SQL?

Las funciones agregadas operan sobre un conjunto de valores para devolver un único valor escalar. Estas son las funciones agregadas de SQL:

• AVG() devuelve la media de los valores especificados.
• SUM() calcula la suma de todos los valores del conjunto.
• MAX() y MIN() devuelven el valor máximo y mínimo, respectivamente.
• COUNT() devuelve el número total de valores del conjunto.

Utilizando la cláusula GROUP BY, se puede calcular un valor agregado para varios grupos en una sola consulta.

Por ejemplo, supongamos que tenemos datos de transacciones de dos ciudades, San Francisco y Nueva York:

Puede utilizar las funciones de agregación de SQL para calcular el importe medio de las transacciones diarias de cada ciudad. Tendrá que agrupar los datos tanto por fecha como por ciudad:

SELECT date, city, AVG(amount) AS avg_transaction_amount_for_city
FROM transactions
GROUP BY date, city;

Este es el resultado de esta consulta:

Utilizando la función agregada AVG() y GROUP BY, obtenemos resultados agrupados por fecha y ciudad. Tuvimos dos transacciones en Nueva York el 2 de noviembre y dos transacciones en San Francisco el 3 de noviembre, pero el conjunto de resultados no incluye estas transacciones individuales; las funciones de agregado contraen las filas individuales y presentan el valor agregado (aquí, el promedio) de todas las filas del grupo.

¿Qué es SQL Funciones de ventana?

En SQL, las funciones de ventana operan sobre un conjunto de filas llamado marco de ventana. Devuelven un único valor para cada fila de la consulta subyacente.

El marco de ventana (o simplemente ventana) se define mediante la cláusula OVER(). Esta cláusula también permite definir una ventana basada en una columna específica (similar a GROUP BY).

Para calcular los valores devueltos, las funciones de ventana pueden utilizar funciones agregadas, pero lo harán con la cláusula OVER().

Volvamos a nuestros datos para San Francisco y Nueva York. Aquí está la tabla de nuevo:

Queremos añadir otra columna a esta tabla con el valor medio de las transacciones diarias para cada ciudad. La siguiente consulta SQL utiliza una función de ventana para obtener el resultado que necesitamos:

SELECT id, date, city, amount,
       AVG(amount) OVER (PARTITION BY date, city) AS  avg_daily_transaction_amount_for_city
FROM transactions
ORDER BY id;

Este es el resultado:

Obsérvese que las filas no están contraídas; seguimos teniendo una fila para cada una de nuestras transacciones. Todos los promedios calculados se presentan en la columna avg_daily_transaction_amount_for_city.

Puede aprender más sobre las funciones de ventana en esta guía detallada. Ofrece varios ejemplos, incluyendo aplicaciones simples y más avanzadas. Además, el equipo de LearnSQL.es ha preparado una estupenda hoja de trucos de SQL Funciones de ventana . Imprímela y pégala en tu escritorio, especialmente si eres nuevo en las funciones de ventana.

Similitudes y diferencias entre las funciones ventana y las funciones agregadas

Ahora que hemos visto ambos tipos de funciones, podemos resumir las similitudes y diferencias entre ellas.

Tanto las funciones ventana como las funciones agregadas

• Operan sobre un conjunto de valores (filas).
• Pueden calcular cantidades agregadas (por ejemplo, AVG(), SUM(), MAX(), MIN(), o COUNT()) sobre el conjunto.
• Pueden agrupar o dividir los datos en una o más columnas.

Las funciones agregadas con GROUP BY se diferencian de las funciones de ventana en que:

• Utilizan GROUP BY() para definir un conjunto de filas para la agregación.
• Agrupan filas en base a los valores de las columnas.
• Agrupan filas en función de los grupos definidos.

Las funciones de ventanadifieren de las funciones de agregación utilizadas con GROUP BY en que:

• Utilizan OVER() en lugar de GROUP BY() para definir un conjunto de filas.
• Pueden utilizar muchas funciones distintas de los agregados (por ejemplo, RANK(), LAG(), o LEAD()).
• Agrupa las filas en función del rango, percentil, etc. de la fila, así como de su valor de columna.
• No colapsa las filas.
• Puede utilizar un marco de ventana deslizante (que depende de la fila actual).

Demostremos esta última diferencia con un ejemplo más. En este ejercicio, queremos calcular el promedio de ventas de los días anteriores y actuales para cada fecha (es decir, un promedio móvil de 2 días).

Sugiero comenzar con una expresión de tabla común (CTE) para definir la daily_sales tabla, donde tenemos el total de ventas de cada día. Luego, utilizamos una función de ventana con un marco de ventana deslizante para calcular el promedio de las ventas totales para el día actual y el anterior. La consulta es la siguiente:

WITH daily_sales AS (
    SELECT date, SUM(amount) AS sales_per_day
    FROM transactions
    GROUP BY date)
SELECT date, 
   AVG(sales_per_day) OVER (ORDER BY date ROWS 1 PRECEDING) 
AS avg_2days_sales
FROM daily_sales
ORDER BY date; 

Aquí está el conjunto de resultados:

En la primera fila, la tabla muestra el total de ventas del 1 de noviembre, ya que no hay fila anterior para esta fecha. Luego, en la segunda fila, tenemos la media de las ventas del 1 y 2 de noviembre; en la tercera fila, la tabla incluye la media de las ventas del 2 y 3 de noviembre, y así sucesivamente.

Las funciones de ventana son excelentes para calcular promedios móviles - algo que no se puede hacer usando sólo funciones de agregación y GROUP BY().

¡Practiquemos Funciones de ventana!

La siguiente tabla resume todas las similitudes y diferencias entre las funciones agregadas de SQL y las funciones ventana:

Aunque las funciones de ventana de SQL son un tema avanzado, puedes practicarlas por tu cuenta. Esta guía especial sobre la práctica de las funciones de ventana de SQL ofrece algunos consejos útiles para aprender la sintaxis de las funciones de ventana y escribir las consultas correspondientes.

LearnSQL ha preparado un completo curso sobre Funciones de ventana; en él, podrás practicar la creación de sofisticados marcos de ventana con 218 ejercicios interactivos. Aprenderás a aprovechar las funciones de ventana para calcular totales corridos y medias móviles, construir clasificaciones, encontrar los mejores y peores resultados e investigar las tendencias a lo largo del tiempo. Puede obtener más información sobre este curso en nuestra entrevista con Agnieszka Kozubek-Krycuń, directora de contenidos de LearnSQL.es .

¡Gracias por leer y feliz aprendizaje!