Funciones de ventana en SQL

Las funciones de ventana son una herramienta potente del lenguaje SQL que permite hacer cálculos complejos sobre grupos de filas relacionadas con la fila actual.

Cómo funcionan

Quizá te preguntes: «¿Qué significa eso de ventana?».

En una consulta SQL estándar, todos los conjuntos de filas se tratan como un único bloque continuo de datos sobre el que se calculan los valores agregados.

En cambio, cuando se usan funciones de ventana, para cada fila se determina un grupo de filas relacionadas con ella (o «ventana»), y sobre ese grupo se calcula un valor agregado propio.

Este conjunto de filas puede ser:

• toda la tabla;
• una partición de la tabla, es decir, un grupo de filas con los mismos valores en una o varias columnas;
• o un marco de ventana, es decir, un rango concreto de filas dentro de la tabla o de la partición.

Por ejemplo, podemos definir el marco de ventana que se pasará a la función de ventana como la fila anterior + la fila actual de la tabla. Entonces, para cada fila, el valor de la función de agregación se calculará de forma distinta, porque los datos que llegan a la función cambian dinámicamente de una fila a otra. Es como si el marco «se deslizara» por la tabla.

Visualización

Las funciones de ventana siempre reciben como entrada una ventana de datos que indica el usuario y devuelven el resultado en una columna aparte.

Veamos cómo puede quedar. Para ello, tomamos la función de ventana AVG, que calcula la media, y esta pequeña tabla:

Y ahora veamos cómo funciona la función de ventana con distintas ventanas:

• Si como ventana se indica toda la tabla, la ventana será la misma para todas las filas, a la función AVG le llegará siempre el mismo conjunto de datos y, por tanto, el resultado será el mismo.

  

• Si como ventana se indica una partición por la columna home_type, a la función AVG le llegará el conjunto de viviendas del mismo tipo y, por tanto, en la nueva columna se mostrará el precio medio de las viviendas cuyo tipo coincide con el de la fila actual de la tabla.

  

• Como marco de ventana también se puede indicar un conjunto de filas más específico. Por ejemplo, el marco se puede definir como «fila anterior + fila actual» de la tabla. Entonces quedará así:

  

  Ten en cuenta que, para la primera fila, el marco solo contiene esa misma fila, porque no hay fila anterior.

Sintaxis de una función de ventana

SELECT funcion_ventana(campo_tabla)
OVER (
    [PARTITION BY columna_particion [, ...]]
    [ORDER BY columna_orden [, ...]]
    [{ROWS | RANGE} marco_ventana]
)

OVER define la ventana (el grupo de filas) que se pasará a la función de ventana. Si dejas la cláusula OVER () sin parámetros, la ventana estará formada por todas las filas del resultado de la consulta.

Dentro de OVER van 3 parámetros opcionales con los que se puede configurar la ventana de forma flexible. Nos detendremos en cada uno de ellos con más detalle en los próximos artículos.

Ejemplo de uso de una función de ventana

Vamos a intentar obtener, con funciones de ventana, la lista de nombres de los alumnos y cuántas personas hay en su clase.

Para empezar, obtengamos la lista de alumnos y el identificador de la clase en la que estudian:

MySQL 8.4
SELECT
    Student.first_name,
    Student.last_name,
    Student_in_class.class
FROM
    Student_in_class
JOIN
    Student ON Student_in_class.student = Student.id;

Y ahora, para calcular cuántos alumnos estudian en cada clase y mostrar esa información en una nueva columna, podemos usar una función de ventana:

MySQL 8.4
SELECT
    Student.first_name,
    Student.last_name,
    Student_in_class.class,
    COUNT(*) OVER (PARTITION BY Student_in_class.class) AS student_count_in_class
FROM
    Student_in_class
JOIN
    Student ON Student_in_class.student = Student.id;

Qué hace nuestra función de ventana

La expresión PARTITION BY Student_in_class.class divide todas las filas de la tabla en particiones según la columna class. Así, para cada fila, a la función de ventana solo le llegarán las filas de la tabla en las que el valor de class coincide con el de la fila actual.

La función COUNT, por su parte, devuelve el número de filas que recibe, y así obtenemos cuántos alumnos estudian en cada clase.

Orden de ejecución de las funciones de ventana en SELECT

Al usar funciones de ventana es importante entender en qué orden se ejecutan. Como puedes ver en el esquema de abajo, las funciones de ventana se ejecutan en el penúltimo paso, después del filtrado y la agrupación, pero antes de la ordenación final de los resultados.

Conclusión

En este artículo hemos visto brevemente el concepto de funciones de ventana, sus posibilidades y su utilidad práctica. En los próximos artículos veremos con más detalle cada aspecto de las funciones de ventana.

Y, para terminar, comprobemos si lo hemos entendido todo:

¿Cuál es la diferencia clave entre las funciones de ventana y las funciones de agregación con agrupación en SQL?