Funciones de ventana SQL vs. GROUP BY: ¿Cuál es la diferencia?

Un concepto erróneo muy común entre los usuarios de SQL es que no hay mucha diferencia entre las funciones de ventana de SQL y las funciones agregadas o la cláusula GROUP BY. Sin embargo, las diferencias son muy significativas.

Quizás el único punto similar entre GROUP BY y las funciones ventana es que ambas permiten ejecutar una función (como AVG, MAX, MIN o COUNT) sobre un grupo de registros. Yo diría que el poder especial de las funciones de ventana es que nos permiten obtener resultados que de otro modo serían casi imposibles de conseguir.

En este artículo, repasaremos el uso de las funciones ventana frente a GROUP BY y las funciones ventana frente a las funciones agregadas.

Revisión rápida de GROUP BY

La cláusula GROUP BY nos permite agrupar un conjunto de registros en base a algún criterio y aplicar una función (por ejemplo, AVG o MAX) a cada grupo, obteniendo un resultado por cada grupo de registros. Veamos un ejemplo.

Tenemos una tabla llamada employee con un total de cinco empleados y tres departamentos:

fig1: la tabla de empleados

Supongamos que queremos obtener el salario medio por departamento y el salario máximo de cada departamento. Debemos utilizar la siguiente consulta:

SELECT Department, 
       avg(salary) as average,
       max(salary) as top_salary
FROM employee
GROUP BY department

La imagen siguiente muestra el resultado:

GROUP BY vs Funciones de ventana

Al comparar las funciones de ventana y GROUP BY, es esencial recordar que GROUP BY colapsa los registros individuales en grupos; después de usar GROUP BY, no se puede hacer referencia a ningún campo individual porque está colapsado. Más adelante, hablaremos en profundidad de este tema. Por ahora, sólo mencionaremos que las funciones de ventana no colapsan los registros individuales.

Por lo tanto, si quiere crear un informe con el nombre de un empleado, el salario y el salario más alto del departamento del empleado, no puede hacerlo con GROUP BY. Los registros individuales de cada empleado se contraen con la cláusula GROUP BY department. Para este tipo de informe, es necesario utilizar las funciones de ventana, que es el tema de la siguiente sección.

Si quieres profundizar en los matices de SQL GROUP BY y los informes, te recomendamos nuestro curso interactivo Cómo crear informes básicos con SQL.

El poder de Funciones de ventana

Las funciones de ventana son una poderosa característica de SQL. Nos permiten aplicar funciones como AVG, COUNT, MAX, y MIN en un grupo de registros mientras que siguen dejando los registros individuales accesibles. Dado que los registros individuales no se colapsan, podemos crear consultas que muestren los datos del registro individual junto con el resultado de la función ventana. Esto es lo que hace que las funciones ventana sean tan potentes.

Supongamos que queremos obtener una lista de los nombres de los empleados, los salarios y el salario más alto de sus departamentos.

SELECT 	employee_name,
department,
salary,
max(salary) OVER (PARTITION BY department) as top_salary
FROM		employee

La siguiente imagen muestra el resultado:

En la consulta anterior, hemos utilizado una función ventana:

max(salary) OVER (PARTITION BY department) as top_salary

La función ventana es MAX() y la aplicamos al conjunto de registros definidos por la cláusula OVER (PARTITION BY department), que son los registros con el mismo valor en el campo departamento. Por último, renombramos la columna top_salary. En el resultado de la consulta, tenemos filas para empleados individuales. Si utilizáramos GROUP BY en lugar de las funciones de ventana, tendríamos filas para cada departamento.

Las funciones ventana tienen una sintaxis bastante verbosa; si quieres entrar en detalles, te sugiero el curso "Funciones de ventana", que es un tutorial paso a paso que te lleva a través de las funciones ventana de SQL usando ejemplos y ejercicios.

Funciones de ventana frente a las funciones agregadas

Cuando comparamos las funciones ventana y las funciones agregadas, notamos una característica súper poderosa del lado de las funciones ventana: las funciones posicionales. Nos permiten obtener el valor de una columna a partir de otros registros en la misma ventana. Se trata de una capacidad realmente asombrosa, que permite a los usuarios de SQL crear informes complejos en unas pocas líneas. Vamos a discutir brevemente dos de estas funciones: LEAD() y LAG().

La función LAG() devuelve el valor de la columna del registro anterior en la ventana, mientras que LEAD() devuelve el valor de la columna del siguiente registro en la ventana. Es muy importante tener la ventana ordenada por la columna de la derecha si se quieren utilizar estas funciones.

Veamos un ejemplo de cómo podemos utilizar estas funciones. Supongamos que tenemos una tabla que almacena las acciones de una empresa con sus valores de mercado en un momento dado. La tabla podría tener el siguiente aspecto:

fig2: la tabla de acciones

Supongamos que queremos un informe que muestre el valor de cada acción con su valor anterior y el porcentaje de variación respecto al valor anterior. Podemos hacerlo utilizando la función LEAD() para obtener el valor anterior de la acción. Tenga en cuenta que utilizamos ORDER BY timestamp al definir la partición (es decir, la ventana de registros). Volveremos a este punto más adelante.

SELECT 	
  share_symbol,
  timestamp,
  value,
  LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp ) AS previous_value, 
  TRUNC(((value - (LAG(value) OVER (PARTITION BY share_symbol ORDER BY timestamp )))*100)/value,2) AS percentage_variation
FROM share

Observe que las columnas previous_value y percentage_variation son columnas calculadas. Utilizan valores de diferentes registros de la misma tabla.

Cuando utilizamos funciones posicionales, es muy importante poner una cláusula ORDER BY junto con la cláusula PARTITION (como hicimos en la consulta anterior). Si no utilizamos la cláusula ORDER BY correcta, los resultados pueden ser erróneos. ¿Por qué? Porque las funciones posicionales trabajan basándose en el orden de los registros en la ventana.

Examinemos esto un poco más. La función FIRST_VALUE() devuelve un valor de columna del primer registro de la ventana. LAG() Como sabemos, la función devuelve el valor de la columna del registro anterior de la ventana. Tener el orden correcto de la ventana es crucial; ¡imagina lo que obtendrías de estas funciones si no fuera así! En nuestro ejemplo, queremos el valor de mercado cronológico anterior para una acción específica. Por lo tanto, utilizamos ORDER BY timestamp. Si omitimos el ORDER BY u ordenamos por otra columna, el resultado sería erróneo.

En algunos casos concretos, las funciones posicionales pueden devolver valores erróneos debido a una ventana parcialmente poblada. Y hay más funciones de ventana, como RANK(), NTH_VALUE() y LAST_VALUE(). No tenemos espacio para cubrir todo esto aquí, pero sugiero revisar este artículo que explica las funciones de ventana y estos ejemplos de funciones de ventana para aprender más.

Cerrar la ventana

En este artículo, exploramos las diferencias entre las funciones ventana y GROUP BY. Vimos ejemplos con varios agregados y funciones ventana. También hablamos de una importante limitación de la cláusula GROUP BY, a saber, el "colapso de registros". Esta limitación no está presente en las funciones ventana, lo que permite a los desarrolladores de SQL combinar datos a nivel de registro con resultados de funciones ventana en la misma consulta.

Otra ventaja de las funciones ventana es su capacidad para combinar valores de consulta de diferentes registros (de la misma ventana) en la misma fila del conjunto de resultados.

Si estás interesado en aprender más sobre las funciones de ventana, te sugiero el Funciones de ventana donde podrás aprender las funciones de ventana de SQL mediante ejercicios interactivos y explicaciones detalladas.