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:

idsale_datedepartmentsales
12024-02-01Shoes6
22024-02-01Clothing5
32024-02-01Equipment10
42024-02-02Clothing6
52024-02-02Equipment4
62024-02-02Shoes4
72024-02-03Shoes8
82024-02-03Equipment10
92024-02-03Clothing4

Sintaxis de las funciones de ventana

💡
DETALLES TÉCNICOS: La sintaxis de las funciones de ventana puede variar en diferentes implementaciones de SQL. Algunas funciones pueden tener nombres diferentes o incluso no estar disponibles. Los comandos de esta guía están basados en PL\SQL, el dialecto que se utiliza en las bases de datos de Oracle DB.

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 como RANK(), 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 clave ROWS o RANGE.

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á:

departmentsum_sales
Clothing15
Equipment24
Shoes18

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á:

idsale_datedepartmentsalessum_sales
22024-02-01Clothing515
42024-02-02Clothing615
92024-02-03Clothing415
32024-02-01Equipment1024
52024-02-02Equipment424
82024-02-03Equipment1024
12024-02-01Shoes618
62024-02-02Shoes418
72024-02-03Shoes818

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:

idsale_datedepartmentsalesrank
32024-02-01Equipment101
12024-02-01Shoes62
22024-02-01Clothing53
42024-02-02Clothing61
52024-02-02Equipment42
62024-02-02Shoes42
82024-02-03Equipment101
72024-02-03Shoes82
92024-02-03Clothing43

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á:

idsale_datedepartmentsalessum_sales
12024-02-01Shoes611
22024-02-01Clothing521
32024-02-01Equipment1021
42024-02-02Clothing620
52024-02-02Equipment414
62024-02-02Shoes416
72024-02-03Shoes822
82024-02-03Equipment1022
92024-02-03Clothing414

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á:

idsale_datedepartmentsalescumulative_sales
12024-02-01Shoes621
22024-02-01Clothing521
32024-02-01Equipment1021
42024-02-02Clothing635
52024-02-02Equipment435
62024-02-02Shoes435
72024-02-03Shoes857
82024-02-03Equipment1057
92024-02-03Clothing457

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) y MIN(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:

idsale_datedepartmentsalesavg_ssum_smax_smin_scount_s
12024-02-01Shoes67.0211053
22024-02-01Clothing57.0211053
32024-02-01Equipment107.0211053
42024-02-02Clothing64.714643
52024-02-02Equipment44.714643
62024-02-02Shoes44.714643
72024-02-03Shoes87.3221043
82024-02-03Equipment107.3221043
92024-02-03Clothing47.3221043

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:

idsale_datedepartmentsalesrank_s
22024-02-01Clothing51
12024-02-01Shoes62
32024-02-01Equipment103
52024-02-02Equipment41
62024-02-02Shoes41
42024-02-02Clothing63
92024-02-03Clothing41
72024-02-03Shoes82
82024-02-03Equipment103
  • La función DENSE_RANK(). Es similar a RANK(), 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:

idsale_datedepartmentsalesdn_rank_s
22024-02-01Clothing51
12024-02-01Shoes62
32024-02-01Equipment103
52024-02-02Equipment41
62024-02-02Shoes41
42024-02-02Clothing62
92024-02-03Clothing41
72024-02-03Shoes82
82024-02-03Equipment103
  • 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:

idsale_datedepartmentsalesrow_n
22024-02-01Clothing51
32024-02-01Equipment102
12024-02-01Shoes63
42024-02-02Clothing61
52024-02-02Equipment42
62024-02-02Shoes43
92024-02-03Clothing41
82024-02-03Equipment102
72024-02-03Shoes83

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):

idsale_datedepartmentsalesnext_day_s
22024-02-01Clothing56
42024-02-02Clothing64
92024-02-03Clothing4None
32024-02-01Equipment104
52024-02-02Equipment410
82024-02-03Equipment10None
12024-02-01Shoes64
62024-02-02Shoes48
72024-02-03Shoes8None
  • La función LAG(). Es similar a LEAD(), 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:

idsale_datedepartmentsaleslast_day_s
22024-02-01Clothing5None
42024-02-02Clothing65
92024-02-03Clothing46
32024-02-01Equipment10None
52024-02-02Equipment410
82024-02-03Equipment104
12024-02-01Shoes6None
62024-02-02Shoes46
72024-02-03Shoes84
  • Las funciones FIRST_VALUE() y LAST_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í:

idsale_datedepartmentsalesfirst_salelast_sale
22024-02-01Clothing554
42024-02-02Clothing654
92024-02-03Clothing454
32024-02-01Equipment101010
52024-02-02Equipment41010
82024-02-03Equipment101010
12024-02-01Shoes668
62024-02-02Shoes468
72024-02-03Shoes868

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.

Categorizado en:

SQL,