Las funciones de ventana son clave para escribir código SQL que sea eficiente y fácil de entender. Saber cómo funcionan y cuándo utilizarlos desbloqueará nuevas formas de resolver sus problemas de generación de informes.
El objetivo de este artículo es explicar paso a paso las funciones de ventana en SQL de una manera comprensible para que no tengas que depender únicamente de memorizar la sintaxis.
Esto es lo que cubriremos:
- Una explicación sobre cómo deberías ver las funciones de la ventana.
- Repase muchos ejemplos en dificultad creciente.
- Mire un escenario de caso real específico para poner en práctica nuestros aprendizajes.
- Revisa lo que hemos aprendido
Nuestro conjunto de datos es simple, seis filas de datos de ingresos para dos regiones en el año 2023.
Si tomamos este conjunto de datos y ejecutamos un GROUP BY suma sobre los ingresos de cada región, quedaría claro lo que pasa, ¿no? Resultaría en sólo dos filas restantes, una para cada región, y luego la suma de los ingresos:
La forma en que quiero que veas las funciones de la ventana es muy similar a esta pero, en lugar de reducir el número de filas, la agregación se ejecutará “en segundo plano” y los valores se agregarán a nuestras filas existentes.
Primero, un ejemplo:
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER () as total_revenue
FROM
sales
Fíjate que no tenemos ninguno. GROUP BY y nuestro conjunto de datos queda intacto. Y aún así pudimos obtener la suma de todos los ingresos. Antes de profundizar más en cómo funcionó esto, hablemos rápidamente sobre la sintaxis completa antes de comenzar a desarrollar nuestro conocimiento.
La sintaxis es la siguiente:
SUM([some_column]) OVER (PARTITION BY [some_columns] ORDER BY [some_columns])
Desmenuzando cada sección, esto es lo que tenemos:
- Una función de agregación o ventana:
SUM,AVG,MAX,RANK,FIRST_VALUE - El
OVERpalabra clave que dice que esta es una función de ventana - El
PARTITION BYsección, que define la grupos - El
ORDER BYsección que define si es una función en ejecución (cubriremos esto más adelante)
No se preocupe todavía por lo que significa cada uno de estos, ya que quedará claro cuando repasemos los ejemplos. Por ahora solo sepa que para definir una función de ventana usaremos el OVER palabra clave. Y como vimos en el primer ejemplo, ese es el único requisito.
Pasando a algo realmente útil, ahora aplicaremos un grupo en nuestra función. El cálculo inicial se conservará para mostrarle que Podemos ejecutar más de una función de ventana a la vez.lo que significa que podemos realizar diferentes agregaciones a la vez en la misma consulta, sin necesidad de realizar subconsultas.
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY region) as region_total,
SUM(revenue) OVER () as total_revenue
FROM sales
Como dijo, usamos el PARTITION BY para definir nuestros grupos (ventanas) que son utilizados por nuestra función de agregación. Entonces, manteniendo nuestro conjunto de datos intacto, tenemos:
- Los ingresos totales de cada región.
- Los ingresos totales de todo el conjunto de datos.
Tampoco estamos restringidos a un solo grupo. Similar a GROUP BY Podemos dividir nuestros datos en Región y Trimestre, por ejemplo:
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY
region,
date_trunc('quarter', date)
) AS region_quarterly_revenue
FROM sales
En la imagen vemos que los únicos dos puntos de datos para la misma región y trimestre se agruparon.
En este punto espero que quede claro cómo podemos ver esto como hacer un GROUP BY pero in situ, sin reducir la cantidad de filas en nuestro conjunto de datos. Por supuesto, no siempre queremos eso, pero no es tan raro ver consultas en las que alguien agrupa datos y luego los vuelve a unir al conjunto de datos original, complicando lo que podría ser una función de ventana única.
Pasando a la ORDER BY palabra clave. Este define una función de ventana en ejecución. Probablemente hayas oído hablar de la suma corriente alguna vez en tu vida, pero si no, deberíamos empezar con un ejemplo para que todo quede claro.
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (ORDER BY id) as running_total
FROM sales
Lo que sucede aquí es que hemos ido, fila por fila, sumando los ingresos con todos los valores anteriores. Esto se hizo siguiendo el orden del id columna, pero podría haber sido cualquier otra columna.
Este ejemplo específico no es particularmente útil porque estamos sumando meses aleatorios y dos regiones, pero usando lo que hemos aprendido ahora podemos encontrar los ingresos acumulados por región. Lo hacemos aplicando la suma corriente dentro de cada grupo.
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (PARTITION BY region ORDER BY date) as running_total
FROM sales
Tómese el tiempo para asegurarse de comprender lo que sucedió aquí:
- Para cada región, avanzamos mes a mes y sumamos los ingresos.
- Una vez que esté hecho para esa región, pasamos a la siguiente, comenzando desde cero y avanzando nuevamente con los meses.
Es bastante interesante notar aquí que cuando escribimos estas funciones en ejecución tenemos el “contexto” de otras filas. Lo que quiero decir es que para obtener la suma acumulada en un punto, debemos conocer los valores anteriores de las filas anteriores. Esto se vuelve más obvio cuando aprendemos que podemos elegir manualmente cuántas filas antes/después queremos agregar.
SELECT
id,
date,
region,
revenue,
SUM(revenue) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
AS useless_sum
FROM
sales
Para esta consulta especificamos que para cada fila queríamos mirar una fila detrás y dos filas adelante, lo que significa que obtenemos la suma de ese rango. Dependiendo del problema que esté resolviendo, esto puede ser extremadamente poderoso ya que le brinda control total sobre cómo agrupa sus datos.
Finalmente, una última función que quiero mencionar antes de pasar a un ejemplo más complicado es la RANK función. Esto se pregunta mucho en las entrevistas y la lógica detrás de esto es la misma que todo lo que hemos aprendido hasta ahora.
SELECT
*,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) as rank,
RANK() OVER (ORDER BY revenue DESC) as overall_rank
FROM
sales
ORDER BY region, revenue DESC
Al igual que antes, utilizamos ORDER BY para especificar el orden en el que caminaremos, fila por fila, y PARTITION BY para especificar nuestros subgrupos.
La primera columna clasifica cada fila dentro de cada región, lo que significa que tendremos múltiples “clasificaciones uno” en el conjunto de datos. El segundo cálculo es la clasificación en todas las filas del conjunto de datos.
Este es un problema que aparece de vez en cuando y para resolverlo en SQL se requiere un uso intensivo de las funciones de ventana. Para explicar este concepto utilizaremos un conjunto de datos diferente que contiene marcas de tiempo y mediciones de temperatura. Nuestro objetivo es completar las filas que faltan mediciones de temperatura con el último valor medido.
Esto es lo que esperamos tener al final:
Antes de comenzar solo quiero mencionar que si estás usando Pandas puedes resolver este problema simplemente ejecutando df.ffill() pero si estás en SQL el problema se vuelve un poco más complicado.
El primer paso para resolver esto es, de alguna manera, agrupar los NULL con el valor no nulo anterior. Puede que no esté claro cómo lo hacemos, pero espero que quede claro que requerirá una función de ejecución. Lo que significa que es una función que “caminará fila por fila”, sabiendo cuándo alcanzamos un valor nulo y cuándo alcanzamos un valor no nulo.
La solución es utilizar COUNT y, más concretamente, contar los valores de las mediciones de temperatura. En la siguiente consulta ejecuto un recuento corriente normal y también un recuento de los valores de temperatura.
SELECT
*,
COUNT() OVER (ORDER BY timestamp) as normal_count,
COUNT(temperature) OVER (ORDER BY timestamp) as group_count
from sensor
- En el primer cálculo simplemente contamos cada fila cada vez más.
- En el segundo contamos todos los valores de temperatura que vimos, sin contar cuando era NULL.
El normal_count La columna es inútil para nosotros, solo quería mostrar qué carrera COUNT parecía. Sin embargo, nuestro segundo cálculo, el group_count ¡nos acerca a la solución de nuestro problema!
Tenga en cuenta que esta forma de contar garantiza que se cuente el primer valor, justo antes de que comiencen los NULL, y luego, cada vez que la función ve un valor nulo, no sucede nada. Esto asegura que estemos “etiquetando” cada nulo posterior con el mismo recuento que teníamos cuando dejamos de tener mediciones.
Continuando, ahora necesitamos copiar el primer valor que se etiquetó en todas las demás filas dentro de ese mismo grupo. Lo que significa que para el grupo 2 todo debe estar lleno del valor 15.0.
¿Puedes pensar en una función ahora que podamos usar aquí? Hay más de una respuesta para esto, pero, nuevamente, espero que al menos quede claro que ahora estamos ante una simple agregación de ventanas con PARTITION BY .
SELECT
*,
FIRST_VALUE(temperature) OVER (PARTITION BY group_count) as filled_v1,
MAX(temperature) OVER (PARTITION BY group_count) as filled_v2
FROM (
SELECT
*,
COUNT(temperature) OVER (ORDER BY timestamp) as group_count
from sensor
) as grouped
ORDER BY timestamp ASC
Podemos usar ambos FIRST_VALUE o MAX para lograr lo que queremos. El único objetivo es que obtengamos el primer valor no nulo. Como sabemos que cada grupo contiene un valor no nulo y un montón de valores nulos, ¡ambas funciones funcionan!
Este ejemplo es una excelente manera de practicar las funciones de las ventanas. Si desea un desafío similar, intente agregar dos sensores y luego complete los valores con la lectura anterior de ese sensor. Algo parecido a esto:
¿Lo podrías hacer? No utiliza nada que no hayamos aprendido aquí hasta ahora.
Ahora sabemos todo lo que necesitamos sobre cómo funcionan las funciones de ventana en SQL, ¡así que hagamos un resumen rápido!
Esto es lo que hemos aprendido:
- Usamos el
OVERpalabra clave para escribir funciones de ventana - Usamos
PARTITION BYpara especificar nuestros subgrupos (ventanas) - Si proporcionamos sólo el
OVER()La palabra clave nuestra ventana es el conjunto de datos completo. - Usamos
ORDER BYcuando queremos tener una función en ejecución, lo que significa que nuestro cálculo avanza fila por fila - Las funciones de ventana son útiles cuando queremos agrupar datos para ejecutar una agregación pero queremos mantener nuestro conjunto de datos como está.