El área de TI es conocida por sus constantes cambios, con nuevas herramientas, nuevos marcos, nuevos proveedores de nube y nuevos LLM que se crean cada día. Sin embargo, incluso en este mundo ajetreado, algunos principios, paradigmas y herramientas parecen desafiar la status quo de ‘nada es para siempre’. Y, en el ámbito de los datos, no hay ejemplo de ello tan imponente como el lenguaje SQL.
Desde su creación allá por los años 80, pasó la era de los Data Warehouses, se materializó en Hadoop/Data-lake/Big Data como Hive, y sigue viva hoy como una de las API de Spark. El mundo cambió mucho pero SQL siguió no sólo vivo sino muy importante y presente.
Pero SQL es como el ajedrez: las reglas básicas son fáciles de entender pero difíciles de dominar. Es un lenguaje con muchas posibilidades, muchas formas de resolver un mismo problema, muchas funciones y palabras clave, y, lamentablemente, muchas funcionalidades subestimadas que, de conocerse mejor, podrían ayudarnos mucho a la hora de construir consultas.
Debido a esto, en esta publicación quiero hablar sobre una de las características SQL no tan famosas que encontré extremadamente útiles al crear mis consultas diarias: Funciones de ventana.
Los SGBD tradicionales y más famosos (PostgreSQL, MySQL y Oracle) se basan en conceptos de álgebra relacional. En él, las líneas se llaman tuplas y las tablas, relaciones. Una relación es un conjunto (en el sentido matemático) de tuplas, es decir, no existe ningún orden ni conexión entre ellas. Debido a esto, no existe un orden predeterminado de líneas en una tabla, y el cálculo realizado en una línea no afecta ni se ve afectado por los resultados de otra. Incluso cláusulas como ORDER BY, solo ordenan tablas, y no es posible hacer cálculos en una línea basándose en los valores de otras líneas.
En pocas palabras, las funciones de ventana solucionan este problema, ampliando las funcionalidades de SQL y permitiéndonos realizar cálculos en una fila en función de los valores de otras líneas.
1-Agregación sin agregación
El ejemplo más trivial para entender las funciones de Windows es la capacidad de ‘agregado sin agregación‘.
Cuando realizamos una agregación con GROUP BY tradicional, toda la tabla se condensa en una segunda tabla, donde cada línea representa el elemento de un grupo. Con Windows Functions, en lugar de condensar las líneas, es posible crear una nueva columna en la misma tabla que contiene los resultados de la agregación.
Por ejemplo, si necesita sumar todos los gastos en su tabla de gastos, tradicionalmente haría:
SELECT SUM(value) AS total FROM myTable
Con las funciones de Windows, harías algo como esto:
SELECT *, SUM(value) OVER() FROM myTable
-- Note that the window function is defined at column-level
-- in the query
La siguiente imagen muestra los resultados:
En lugar de crear una nueva tabla, devolverá el valor de la agregación en una nueva columna. Tenga en cuenta que el valor es el mismo, pero la tabla no era ‘resumido‘, se mantuvieron las líneas originales: simplemente calculamos un agregación sin agregar la mesa 😉
La cláusula OVER es la indicación de que estamos creando una función de ventana. Esta cláusula define sobre qué líneas se realizará el cálculo. Está vacío en el código anterior, por lo que calculará SUM() en todas las líneas.
Esto es útil cuando necesitamos hacer cálculos basados en totales (o promedios, mínimos, máximos) de columnas. Por ejemplo, para calcular cuánto aporta cada gasto en porcentaje respecto al total.
En casos reales, es posible que también queramos el detalle por alguna categoría, como en el ejemplo de la imagen 2, donde tenemos los gastos de la empresa por departamento. Nuevamente, podemos lograr el total gastado por cada departamento con un simple GRUPO POR:
SELECT depto, sum(value) FROM myTable GROUP BY depto
O especifique una lógica de PARTICIÓN en la función de ventana:
SELECT *, SUM(value) OVER(PARTITION BY depto) FROM myTable
Vea el resultado:
Este ejemplo ayuda a comprender por qué la operación se llama función de ‘ventana’: la cláusula OVER define un conjunto de líneas sobre las cuales operará la función correspondiente, una ‘ventana’ en la tabla.
En el caso anterior, la función SUMA() operará en las particiones creadas por la columna de departamento (RH y VENTAS); sumará todos los valores en la columna ‘valor’ para cada elemento en la columna de departamento de forma aislada. El grupo al que pertenece la línea (RH o VENTAS) determina el valor en la columna ‘Total’.
2 – Conciencia del tiempo y los pedidos
A veces necesitamos calcular el valor de una columna de una fila en función de los valores de otras filas. Un ejemplo clásico es el crecimiento anual del PIB de un país, calculado utilizando el valor actual y el anterior.
Cálculos de este tipo, donde necesitamos el valor del año pasado, la diferencia entre la fila actual y la siguiente, el primer valor de una serie, etc., son un testimonio del poder de la función de Windows. De hecho, ¡no sé si este comportamiento podría lograrse con comandos SQL estándar! Probablemente podría, pero sería una consulta muy compleja…
Pero las funciones de Windows lo hicieron sencillo, vea la imagen a continuación (una tabla que registra la altura de algunos niños):
SELECT
year, height,
LAG(height) OVER (ORDER BY year) AS height_last_year
FROM myTable
La función LAG( ‘columna’ ) es responsable de hacer referencia al valor de ‘columna’ en la fila anterior. Puedes imaginarlo como una secuencia de pasos: en la segunda línea, considera el valor de la primera; En el tercero, el valor del segundo; y así sucesivamente… La primera línea no cuenta (de ahí el NULO), ya que no tiene predecesor.
Naturalmente, se necesita algún criterio de ordenamiento para definir cuál es la «línea anterior». Y ese es otro concepto importante en las funciones de Windows: funciones analíticas.
A diferencia de las funciones SQL tradicionales, las funciones analíticas (como LAG) consideran que existe un orden en las líneas, y este orden está definido por la cláusula ORDER BY dentro de OVER(), es decir, el concepto de primera, segunda, tercera línea y y así sucesivamente se define dentro de la palabra clave OVER. La característica principal de estas funciones es la capacidad de hacer referencia a otras filas en relación con la fila actual: LAG hace referencia a la fila anterior, LEAD hace referencia a las siguientes filas, FIRST hace referencia a la primera fila de la partición, etc.
Una cosa buena de LAG y LEAD es que ambos aceptan un segundo argumento, el desplazamiento, que especifica cuántas filas hacia adelante (para LEAD) o hacia atrás (para LAG) mirar.
SELECT
LAG(height, 2) OVER (ORDER BY year) as height_two_years_ago,
LAG(height, 3) OVER (ORDER BY year) as height_three_years_ago,
LEAD(height) OVER (ORDER BY year) as height_next_year
FROM ...
Y también es perfectamente posible realizar cálculos con estas funciones:
SELECT
100*height/(LAG(height) OVER (ORDER BY year))
AS "annual_growth_%"
FROM ...
3 – Conciencia y agregación del tiempo
El tiempo y el espacio son solo uno — dijo una vez Einsteinm, o algo así, no lo sé ¯\_(ツ)_/¯
Ahora que sabemos cómo dividir y ordenar, ¡podemos usar estos dos juntos! Volviendo al ejemplo anterior, supongamos que hay más niños en esa mesa y necesitamos calcular la tasa de crecimiento de cada uno. Es muy simple, ¡simplemente combine ordenar y particionar! Ordenemos por año y particionemos por nombre del niño.
SELECT 1-height/LAG(height) OVER (ORDER BY year PARTITION BY name) ...
La consulta anterior hace lo siguiente: particiona la tabla por hijo y, en cada partición, ordena los valores por año y divide el valor de altura del año actual con el valor anterior (y resta el resultado de uno).
¡Nos estamos acercando al concepto completo de «ventana»! Es un segmento de tabla, un conjunto de filas agrupadas por las columnas definidas en PARTITION BY que están ordenadas por los campos en ORDER BY, donde todos los cálculos se realizan considerando solo las filas del mismo grupo (partición) y un orden específico.
4-Ranking y Posición
Las funciones de Windows se pueden dividir en tres categorías, dos de las cuales ya hablamos: funciones de agregación (COUNT, SUM, AVG, MAX,…) y funciones analíticas (LAG, LEAD, FIRST_VALUE, LAST_VALUE,…).
El tercer grupo es el más simple: funciones de clasificación, cuyo mayor exponente es la función row_number(), que devuelve un número entero que representa la posición de una fila en el grupo (según el orden definido).
SELECT row_number() OVER(ORDER BY score)
Las funciones de clasificación, como su nombre indica, devuelven valores basados en la posición de la línea en el grupo, definido por los criterios de ordenación. ROW_NUMBER, RANK y NTILE son algunos de los más utilizados.
En la imagen de arriba, se crea un número de fila en función de la puntuación de cada jugador.
… y sí, comete el atroz pecado de programación de empezar desde 1.
Tamaño de 5 ventanas
Todas las funciones presentadas hasta este punto consideran TODAS las filas de la partición/grupo al calcular los resultados. Por ejemplo, SUM() descrita en el primer ejemplo considera todas las filas del departamento para calcular el total.
Pero es posible especificar un tamaño de ventana más pequeño, es decir, cuántas líneas antes y después de la línea actual deben considerarse en los cálculos. Esta es una funcionalidad útil para calcular promedios móviles/ventanas móviles.
Consideremos el siguiente ejemplo, con una tabla que contiene el número diario de casos de una determinada enfermedad, donde necesitamos calcular el número promedio de casos considerando el día actual y los dos anteriores. Tenga en cuenta que es posible resolver este problema con la función LAG, que se mostró anteriormente:
SELECT
( n_cases + LAG(n_cases, 1) + LAG(n_cases, 2) )/3
OVER (ORDER BY date_reference)
Pero podemos lograr el mismo resultado de manera más elegante usando el concepto de marcos:
SELECT
AVG(n_cases)
OVER (
ORDER BY date_reference
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
El cuadro de arriba especifica que debemos calcular el promedio mirando solo las dos filas anteriores (ANTERIORES) y la fila actual. Si deseamos considerar la línea anterior, la actual y la siguiente, podemos cambiar el marco:
AVG(n_cases)
OVER (
ORDER BY date_reference
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
Y eso es todo lo que es un marco: una forma de limitar el alcance de una función a un límite específico. Por defecto (en la mayoría de los casos), las funciones de Windows consideran el siguiente marco:
ROWS BETWEEN UNBOUDED PRECEDING AND CURRENT ROW
-- ALL THE PREVIOUS ROWS + THE CURRENT ROW
