Te explicamos cómo hacer cálculos agregados sin perder las filas originales.
Las funciones de ventana en SQL (del inglés window function) son un tipo especial de funciones que te permiten hacer cálculos sobre grupos específicos de filas en una base de datos. La diferencia es que no juntan las filas en una sola, sino que devuelven la misma cantidad de filas que tenías al principio.
Estas funciones son muy útiles para crear informes, analizar datos, hacer modelos financieros y otras tareas donde necesitas ver los resultados en el contexto de los datos originales. Con ellas, puedes hacer todo tipo de cálculos para un conjunto de filas: calcular el promedio, la suma, y mucho más.
Aquí te damos un resumen de lo más importante sobre las funciones de ventana en SQL.
Tabla para Ejemplos
Vamos a crear una tabla llamada sport_sales
, que contiene las ventas de una tienda de artículos deportivos por departamento. Incluiremos los siguientes campos:
id
: un identificador único para cada registro.sale_date
: la fecha de las ventas de cada departamento.department
: el nombre del departamento.sales
: la cantidad de ventas de cada departamento por día.
Puedes crear la tabla con el siguiente comando:
CREATE TABLE IF NOT EXISTS sport_sales (
id integer primary key,
sale_date date,
department varchar(50),
sales integer
);
Y para rellenar la tabla con datos, puedes usar el siguiente código:
insert into sport_sales (id, sale_date, department, sales) values
(1, '2024-02-01', 'Shoes', 6),
(2, '2024-02-01', 'Clothing', 5),
(3, '2024-02-01', 'Equipment', 10),
(4, '2024-02-02', 'Clothing', 6),
(5, '2024-02-02', 'Clothing', 6),
(5, '2024-02-02', 'Equipment', 4),
(6, '2024-02-01', 'Shoes', 4),
(7, '2024-02-03', 'Shoes', 8),
(8, '2024-02-03', 'Equipment', 10),
(9, '2024-02-01', 'Clothing', 4);
El resultado será una tabla de base de datos como esta, que vamos a usar:
id | sale_date | department | sales |
---|---|---|---|
1 | 2024-02-01 | Shoes | 6 |
2 | 2024-02-01 | Clothing | 5 |
3 | 2024-02-01 | Equipment | 10 |
4 | 2024-02-02 | Clothing | 6 |
5 | 2024-02-02 | Equipment | 4 |
6 | 2024-02-02 | Shoes | 4 |
7 | 2024-02-03 | Shoes | 8 |
8 | 2024-02-03 | Equipment | 10 |
9 | 2024-02-03 | Clothing | 4 |
Sintaxis de las funciones de ventana
En general, la sintaxis de las funciones de ventana se parece a esto:
<window_function>(arguments) OVER ([Partitioning] [Ordering] [Frame])
Veamos la estructura con más detalle:
<window_function_name>
: el nombre de la función de ventana, por ejemplo,ROW_NUMBER()
,RANK()
,DENSE_RANK()
, etc.(arguments)
: los argumentos de la función, como el nombre de la columna sobre la que se va a hacer el cálculo.OVER()
: la palabra clave que define cómo se va a aplicar la función de ventana al conjunto de datos.[Partitioning]
: define el criterio según el cual las filas se dividen en subgrupos. Este componente es opcional.[Ordering]
: indica el orden de las filas en cada subgrupo, algo importante para funciones comoRANK()
,ROW_NUMBER()
, etc. Este elemento también es opcional.[Frame]
: define el «marco» de filas en relación a la actual. Normalmente se usa con las palabras claveROWS
oRANGE
.
Vamos a ver estos componentes con más detalle.
PARTITION BY
Este parámetro te permite dividir los datos en grupos, dentro de los cuales se va a aplicar la función de ventana. PARTITION BY
funciona de forma similar a GROUP BY
en las funciones agregadas, pero en las funciones de ventana, el resultado se devuelve para cada fila de los datos de entrada.
Por ejemplo, veamos cómo funciona un comando con la agrupación GROUP BY
, que cuenta las ventas totales por departamento en un periodo:
SELECT
department,
sum(sales) AS sum_sales
FROM sport_sales
GROUP BY department
ORDER BY department;
El resultado será:
department | sum_sales |
---|---|
Clothing | 15 |
Equipment | 24 |
Shoes | 18 |
Este comando muestra en la columna sum_sales
la suma de las ventas de cada departamento.
Ahora vamos a usar una función de ventana:
SELECT
id, sale_date, department, sales,
sum(sales) OVER(PARTITION BY department) AS sum_sales
FROM sport_sales
ORDER BY department, sale_date;
El resultado será:
id | sale_date | department | sales | sum_sales |
---|---|---|---|---|
2 | 2024-02-01 | Clothing | 5 | 15 |
4 | 2024-02-02 | Clothing | 6 | 15 |
9 | 2024-02-03 | Clothing | 4 | 15 |
3 | 2024-02-01 | Equipment | 10 | 24 |
5 | 2024-02-02 | Equipment | 4 | 24 |
8 | 2024-02-03 | Equipment | 10 | 24 |
1 | 2024-02-01 | Shoes | 6 | 18 |
6 | 2024-02-02 | Shoes | 4 | 18 |
7 | 2024-02-03 | Shoes | 8 | 18 |
Este comando muestra las ventas de cada departamento en la columna sales
y las ventas totales en la columna sum_sales
. Se mantienen todas las filas de la tabla original, pero se agrupan por departamento.
ORDER BY
La palabra clave ORDER BY
define cómo se van a ordenar los datos cuando se aplica la función de ventana.
Para definir el orden de las filas, se usan las palabras clave ASC
y DESC
:
ASC
: orden ascendente. Este es el valor predeterminado. Ordena de menor a mayor valor.DESC
: orden descendente. Ordena de mayor a menor valor.
ORDER BY
puede incluir varias columnas. Por ejemplo, ORDER BY sale_date ASC, sale DESC
ordena los datos de los departamentos primero por fechas en orden ascendente y luego por ventas en orden descendente.
Vamos a crear un comando que usa la función RANK()
, que asigna un rango a cada fila en función del valor de la columna sale
. Al departamento con las ventas más altas del día se le asigna el rango 1, y al que tenga las ventas más bajas, el rango 3. Los valores de la columna sale_date
se agrupan en orden ascendente, y los de sales
, en orden descendente:
SELECT
id, sale_date, department, sales,
RANK() OVER(PARTITION BY sale_date ORDER BY sale_date ASC, sales DESC) AS rank
FROM sport_sales;
Esta es la tabla que obtenemos como resultado:
id | sale_date | department | sales | rank |
---|---|---|---|---|
3 | 2024-02-01 | Equipment | 10 | 1 |
1 | 2024-02-01 | Shoes | 6 | 2 |
2 | 2024-02-01 | Clothing | 5 | 3 |
4 | 2024-02-02 | Clothing | 6 | 1 |
5 | 2024-02-02 | Equipment | 4 | 2 |
6 | 2024-02-02 | Shoes | 4 | 2 |
8 | 2024-02-03 | Equipment | 10 | 1 |
7 | 2024-02-03 | Shoes | 8 | 2 |
9 | 2024-02-03 | Clothing | 4 | 3 |
ROWS
o RANGE
Las palabras clave ROWS
y RANGE
definen qué filas de la ventana se van a tener en cuenta para hacer los cálculos. Establecen el «marco» de la ventana: un conjunto de filas en relación a la fila actual que se utilizará para los cálculos.
ROWS
define los límites de la ventana en términos de una cantidad de filas antes o después de la fila actual. Por ejemplo, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
indica que la ventana incluye la fila anterior a la actual, la actual y la fila siguiente. La función se calcula en base a ellas.
Vamos a escribir un comando que suma las ventas en la fila actual, la anterior y la siguiente, y coloca las sumas obtenidas en la columna sum_sales
:
SELECT
id, sale_date, department, sales,
SUM(sales) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_sales
FROM sport_sales;
El resultado será:
id | sale_date | department | sales | sum_sales |
---|---|---|---|---|
1 | 2024-02-01 | Shoes | 6 | 11 |
2 | 2024-02-01 | Clothing | 5 | 21 |
3 | 2024-02-01 | Equipment | 10 | 21 |
4 | 2024-02-02 | Clothing | 6 | 20 |
5 | 2024-02-02 | Equipment | 4 | 14 |
6 | 2024-02-02 | Shoes | 4 | 16 |
7 | 2024-02-03 | Shoes | 8 | 22 |
8 | 2024-02-03 | Equipment | 10 | 22 |
9 | 2024-02-03 | Clothing | 4 | 14 |
En la tabla anterior, los datos se agrupan por fecha y la columna sum_sales
se calcula así:
- Cuando la actual es la primera fila, se suman las ventas de la primera y la segunda fila.
- Cuando la actual es la segunda fila, se suman las ventas de la primera, la segunda y la tercera fila, y así sucesivamente.
RANGE
define los límites de la ventana en base a los valores de la columna especificada, no a la posición física de las filas como lo hace ROWS
. Agrupa las filas que tienen valores iguales o cercanos en la columna de ordenación especificada.
Es decir, cuando usas RANGE
junto con ORDER BY
, SQL procesa la ventana para cada fila, incluyendo en ella todas las filas con valores iguales o comparables en la columna especificada en ORDER BY
. Esto significa que, si hay varias filas con los mismos valores en la columna de ordenación (por ejemplo, las mismas fechas o departamentos), todas se incluirán en la ventana.
Por ejemplo, vamos a calcular la suma acumulativa de las ventas por fecha. Para ello, vamos a escribir el siguiente comando:
SELECT
id, sale_date, department, sales,
SUM(sales) OVER (
ORDER BY sale_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sales
FROM sport_sales;
En este ejemplo, para cada fecha, se sumarán todos los valores de sales
desde la primera entrada hasta la fecha actual, incluyendo todas las filas con fechas iguales a la fecha actual.
El resultado será:
id | sale_date | department | sales | cumulative_sales |
---|---|---|---|---|
1 | 2024-02-01 | Shoes | 6 | 21 |
2 | 2024-02-01 | Clothing | 5 | 21 |
3 | 2024-02-01 | Equipment | 10 | 21 |
4 | 2024-02-02 | Clothing | 6 | 35 |
5 | 2024-02-02 | Equipment | 4 | 35 |
6 | 2024-02-02 | Shoes | 4 | 35 |
7 | 2024-02-03 | Shoes | 8 | 57 |
8 | 2024-02-03 | Equipment | 10 | 57 |
9 | 2024-02-03 | Clothing | 4 | 57 |
RANGE
puede ser útil cuando necesitas hacer cálculos en grupos de datos que están relacionados de forma lógica (por ejemplo, entradas con las mismas fechas o precios similares).
Clases de Funciones de Ventana
En SQL hay muchas funciones de ventana que simplifican el trabajo con los datos. Con ellas, puedes hacer rápidamente cálculos complejos sobre grupos de filas relacionadas con la actual. Las funciones de ventana principales se pueden dividir en tres grandes grupos.
Funciones agregadas
Las funciones agregadas te permiten hacer sumas, contar, encontrar máximos y mínimos, y valores medios. Hacen cálculos sobre un conjunto de filas de la ventana y devuelven un solo valor resultante:
SUM(column_name)
: devuelve la suma de los valores seleccionados.AVG(column_name)
: calcula el valor medio.MAX(column_name)
yMIN(column_name)
: devuelven el valor máximo y el mínimo, respectivamente.COUNT(column_name)
: encuentra la cantidad de valores.
Como ejemplo, vamos a aplicar funciones agregadas a las ventas de los departamentos para cada fecha, de forma que cada fila de la ventana conserve los valores originales:
SELECT
id, sale_date, department, sales,
AVG(sales) OVER (PARTITION BY sale_date) as avg_s,
SUM(sales) OVER (PARTITION BY sale_date) as sum_s,
MAX(sales)OVER (PARTITION BY sale_date) as max_s,
MIN(sales)OVER (PARTITION BY sale_date) as min_s,
COUNT(sales) OVER (PARTITION BY sale_date) as count_s
FROM sport_sales;
El resultado será la siguiente tabla:
id | sale_date | department | sales | avg_s | sum_s | max_s | min_s | count_s |
---|---|---|---|---|---|---|---|---|
1 | 2024-02-01 | Shoes | 6 | 7.0 | 21 | 10 | 5 | 3 |
2 | 2024-02-01 | Clothing | 5 | 7.0 | 21 | 10 | 5 | 3 |
3 | 2024-02-01 | Equipment | 10 | 7.0 | 21 | 10 | 5 | 3 |
4 | 2024-02-02 | Clothing | 6 | 4.7 | 14 | 6 | 4 | 3 |
5 | 2024-02-02 | Equipment | 4 | 4.7 | 14 | 6 | 4 | 3 |
6 | 2024-02-02 | Shoes | 4 | 4.7 | 14 | 6 | 4 | 3 |
7 | 2024-02-03 | Shoes | 8 | 7.3 | 22 | 10 | 4 | 3 |
8 | 2024-02-03 | Equipment | 10 | 7.3 | 22 | 10 | 4 | 3 |
9 | 2024-02-03 | Clothing | 4 | 7.3 | 22 | 10 | 4 | 3 |
Es importante saber que las funciones agregadas son sensibles a los valores NULL
. Por ejemplo, AVG()
y SUM()
ignoran estos valores, mientras que COUNT()
cuenta todas las filas, incluso las que tienen NULL
.
Funciones de clasificación
Las funciones de clasificación te dan maneras de evaluar la posición de cada fila entre sus vecinas en un determinado orden. Son útiles cuando necesitas determinar los rangos en un conjunto de datos.
Las funciones de clasificación principales son:
- La función
RANK()
. Asigna un rango a cada fila de la sección de la ventana. Si las filas son iguales, reciben el mismo rango, pero el siguiente rango se incrementa en la cantidad de filas con el mismo rango.
En el ejemplo siguiente, el comando SQL asigna rangos a las filas en orden ascendente según el número de ventas. Si la cantidad de ventas es la misma (id 5 y 6), se les asigna el rango 1, y a la fila siguiente en el orden (id 4) se le asigna el rango 3:
SELECT
id, sale_date, department, sales,
RANK() OVER (PARTITION BY sale_date ORDER BY sales) AS rank_s
FROM sport_sales;
Así se ve la tabla con el resultado del comando:
id | sale_date | department | sales | rank_s |
---|---|---|---|---|
2 | 2024-02-01 | Clothing | 5 | 1 |
1 | 2024-02-01 | Shoes | 6 | 2 |
3 | 2024-02-01 | Equipment | 10 | 3 |
5 | 2024-02-02 | Equipment | 4 | 1 |
6 | 2024-02-02 | Shoes | 4 | 1 |
4 | 2024-02-02 | Clothing | 6 | 3 |
9 | 2024-02-03 | Clothing | 4 | 1 |
7 | 2024-02-03 | Shoes | 8 | 2 |
8 | 2024-02-03 | Equipment | 10 | 3 |
- La función
DENSE_RANK()
. Es similar aRANK()
, pero funciona de forma más «densa». Esto significa que el siguiente rango se incrementa en 1, independientemente de la cantidad de filas con el mismo rango.
Vamos a escribir un comando simple que usa la función DENSE_RANK()
:
SELECT
id, sale_date, department, sales,
DENSE_RANK() OVER (PARTITION BY sale_date ORDER BY sales) AS dn_rank_s
FROM sport_sales;
En este ejemplo, el comando SQL también asigna rangos a las filas en orden ascendente según la cantidad de ventas. Si la cantidad de ventas es la misma (id 5 y 6), se les asigna el rango 1, y a la fila siguiente en el orden (id 4) se le asigna el rango 2. Esta es la tabla que se obtiene como resultado:
id | sale_date | department | sales | dn_rank_s |
---|---|---|---|---|
2 | 2024-02-01 | Clothing | 5 | 1 |
1 | 2024-02-01 | Shoes | 6 | 2 |
3 | 2024-02-01 | Equipment | 10 | 3 |
5 | 2024-02-02 | Equipment | 4 | 1 |
6 | 2024-02-02 | Shoes | 4 | 1 |
4 | 2024-02-02 | Clothing | 6 | 2 |
9 | 2024-02-03 | Clothing | 4 | 1 |
7 | 2024-02-03 | Shoes | 8 | 2 |
8 | 2024-02-03 | Equipment | 10 | 3 |
- La función
ROW_NUMBER()
. Asigna un número de orden único a cada fila de la sección de la ventana.
El comando SQL se ve así:
SELECT
id, sale_date, department, sale,
ROW_NUMBER() OVER (PARTITION BY sale_date ORDER BY department) AS row_n
FROM sport_sales;
Como resultado, todos los datos se ordenan por fecha de venta y se marcan con números únicos:
id | sale_date | department | sales | row_n |
---|---|---|---|---|
2 | 2024-02-01 | Clothing | 5 | 1 |
3 | 2024-02-01 | Equipment | 10 | 2 |
1 | 2024-02-01 | Shoes | 6 | 3 |
4 | 2024-02-02 | Clothing | 6 | 1 |
5 | 2024-02-02 | Equipment | 4 | 2 |
6 | 2024-02-02 | Shoes | 4 | 3 |
9 | 2024-02-03 | Clothing | 4 | 1 |
8 | 2024-02-03 | Equipment | 10 | 2 |
7 | 2024-02-03 | Shoes | 8 | 3 |
Funciones de desplazamiento
Las funciones de desplazamiento te permiten hacer operaciones sobre la fila actual, en función de otras filas de la ventana. Son útiles cuando necesitas analizar secuencias o series temporales de datos.
Las funciones de desplazamiento principales son:
- La función
LEAD()
. Te permite mirar hacia adelante en una cantidad de filas determinada a partir de la actual y obtener el valor de la columna en esas filas.
Por ejemplo, un comando puede verse así:
SELECT id, sale_date, department, sales,
LEAD(sales, 1) OVER (PARTITION BY department ORDER BY sale_date) AS next_day_s
FROM sport_sales;
Para cada departamento, el comando devuelve las ventas del día actual (sales
) y del siguiente (next_day_s
):
id | sale_date | department | sales | next_day_s |
---|---|---|---|---|
2 | 2024-02-01 | Clothing | 5 | 6 |
4 | 2024-02-02 | Clothing | 6 | 4 |
9 | 2024-02-03 | Clothing | 4 | None |
3 | 2024-02-01 | Equipment | 10 | 4 |
5 | 2024-02-02 | Equipment | 4 | 10 |
8 | 2024-02-03 | Equipment | 10 | None |
1 | 2024-02-01 | Shoes | 6 | 4 |
6 | 2024-02-02 | Shoes | 4 | 8 |
7 | 2024-02-03 | Shoes | 8 | None |
- La función
LAG()
. Es similar aLEAD()
, pero en lugar de «mirar hacia adelante», esta función «mira hacia atrás» una cantidad de filas determinada.
Aquí, en cada fila de la ventana, la columna sales
mostrará las ventas del día actual, y la columna last_day_s
mostrará las ventas del día anterior para cada departamento:
id | sale_date | department | sales | last_day_s |
---|---|---|---|---|
2 | 2024-02-01 | Clothing | 5 | None |
4 | 2024-02-02 | Clothing | 6 | 5 |
9 | 2024-02-03 | Clothing | 4 | 6 |
3 | 2024-02-01 | Equipment | 10 | None |
5 | 2024-02-02 | Equipment | 4 | 10 |
8 | 2024-02-03 | Equipment | 10 | 4 |
1 | 2024-02-01 | Shoes | 6 | None |
6 | 2024-02-02 | Shoes | 4 | 6 |
7 | 2024-02-03 | Shoes | 8 | 4 |
- Las funciones
FIRST_VALUE()
yLAST_VALUE()
. Estas funciones devuelven el primer y el último valor de la columna de la ventana, respectivamente.
Con este comando, puedes mostrar las ventas del día actual y las ventas del primer y tercer día para cada departamento:
SELECT id, sale_date, department, sale,
FIRST_VALUE(sale) OVER (PARTITION BY department ORDER BY sale_date) AS first_sale,
LAST_VALUE(sale) OVER (PARTITION BY department ORDER BY sale_date) AS last_sale
FROM sport_sales;
El resultado del comando se ve así:
id | sale_date | department | sales | first_sale | last_sale |
---|---|---|---|---|---|
2 | 2024-02-01 | Clothing | 5 | 5 | 4 |
4 | 2024-02-02 | Clothing | 6 | 5 | 4 |
9 | 2024-02-03 | Clothing | 4 | 5 | 4 |
3 | 2024-02-01 | Equipment | 10 | 10 | 10 |
5 | 2024-02-02 | Equipment | 4 | 10 | 10 |
8 | 2024-02-03 | Equipment | 10 | 10 | 10 |
1 | 2024-02-01 | Shoes | 6 | 6 | 8 |
6 | 2024-02-02 | Shoes | 4 | 6 | 8 |
7 | 2024-02-03 | Shoes | 8 | 6 | 8 |
En Resumen
Las funciones de ventana hacen que los comandos SQL sean más flexibles y potentes. Te ayudan a evitar la pérdida de información que se encuentra en cada fila de los datos originales. También hacen todo tipo de cálculos con las tablas, como clasificar, sumar o mostrar estadísticas.
Las funciones de ventana operan en el contexto de una «ventana»: un subconjunto de la tabla, y añaden nueva información en columnas separadas. Te permiten evitar comandos adicionales, lo que simplifica el trabajo con las bases de datos y hace que los comandos SQL sean más fáciles de leer.