Explorando las Funciones de Ventana en PostgreSQL: Análisis Avanzado de Datos

Las funciones de ventana son una característica increíblemente poderosa en SQL, y PostgreSQL las implementa de manera robusta, permitiéndote realizar cálculos complejos sobre un conjunto de filas relacionadas con la fila actual, sin agruparlas y, por lo tanto, sin reducir el número de filas en tu resultado. Esto abre un abanico de posibilidades para el análisis de datos que antes requeriría subconsultas complejas o múltiples pasos.

En este tutorial, exploraremos a fondo qué son las funciones de ventana, cómo se estructuran y, lo más importante, cómo puedes aplicarlas para resolver problemas de análisis de datos comunes y avanzados.

🚀 ¿Qué son las Funciones de Ventana?

Imagina que tienes una lista de ventas y quieres saber la venta promedio de los últimos tres días para cada venta. O quizás quieres clasificar a tus empleados por su salario dentro de cada departamento. Estas son el tipo de problemas donde las funciones de ventana brillan.

Una función de ventana realiza un cálculo sobre un conjunto de filas de la tabla que están relacionadas con la fila actual. Este conjunto de filas se denomina la ventana. A diferencia de las funciones de agregación regulares (como SUM(), AVG(), COUNT()) que colapsan las filas en un solo resultado por grupo, las funciones de ventana devuelven un valor para cada fila del conjunto de resultados, manteniendo la granularidad original de los datos.

🛠️ Componentes Clave de una Función de Ventana

La sintaxis básica de una función de ventana es funcion_ventana(argumentos) OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...). Analicemos cada parte:

• funcion_ventana(argumentos): Puede ser una función de agregación estándar (como SUM, AVG, COUNT, MAX, MIN) utilizada en un contexto de ventana, o una función de ventana específica (como ROW_NUMBER, RANK, LEAD, LAG).
• OVER: La palabra clave OVER es lo que transforma una función de agregación normal en una función de ventana. Indica que el cálculo se realizará sobre una ventana de filas.
• PARTITION BY: (Opcional) Divide el conjunto de resultados en grupos o particiones a las que se aplicará la función de ventana de forma independiente. Si se omite, toda la tabla se considera una única partición.
• ORDER BY: (Opcional, pero muy común) Define el orden de las filas dentro de cada partición. Esto es crucial para funciones que dependen del orden, como los rankings o los promedios móviles.
• frame_clause (Cláusula de Marco): (Opcional) Define el subconjunto de filas dentro de la partición actual que se incluye en la ventana. Se especifica con ROWS o RANGE y cláusulas BETWEEN.
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Todas las filas desde el inicio de la partición hasta la fila actual (acumulativo).
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: La fila anterior, la actual y la siguiente.
  • ROWS BETWEEN N PRECEDING AND CURRENT ROW: Las últimas N filas y la actual.

📖 Preparando Nuestro Entorno: Datos de Ejemplo

Para este tutorial, utilizaremos una tabla simple de ventas. Crearemos la tabla y la poblaremos con algunos datos de ejemplo para ilustrar los conceptos.

CREATE TABLE ventas (
    id SERIAL PRIMARY KEY,
    fecha DATE NOT NULL,
    producto VARCHAR(100) NOT NULL,
    cantidad INT NOT NULL,
    precio DECIMAL(10, 2) NOT NULL,
    region VARCHAR(50) NOT NULL
);

INSERT INTO ventas (fecha, producto, cantidad, precio, region) VALUES
('2023-01-01', 'Laptop', 2, 1200.00, 'Norte'),
('2023-01-01', 'Mouse', 5, 25.00, 'Norte'),
('2023-01-02', 'Teclado', 3, 75.00, 'Norte'),
('2023-01-02', 'Monitor', 1, 300.00, 'Sur'),
('2023-01-03', 'Laptop', 1, 1250.00, 'Norte'),
('2023-01-03', 'Webcam', 4, 50.00, 'Sur'),
('2023-01-04', 'Mouse', 10, 20.00, 'Norte'),
('2023-01-04', 'Teclado', 2, 80.00, 'Sur'),
('2023-01-05', 'Monitor', 2, 320.00, 'Norte'),
('2023-01-05', 'Laptop', 1, 1300.00, 'Sur'),
('2023-01-06', 'Mouse', 3, 22.00, 'Norte'),
('2023-01-06', 'Webcam', 5, 48.00, 'Sur'),
('2023-01-07', 'Teclado', 1, 70.00, 'Norte'),
('2023-01-07', 'Monitor', 1, 310.00, 'Sur');

✨ Funciones de Ventana de Ranking

Estas funciones asignan un rango a cada fila dentro de una partición. Son muy útiles para clasificaciones.

ROW_NUMBER(): Número de Fila Consecutivo

Añade un número secuencial único a cada fila dentro de la partición, basado en el ORDER BY.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    ROW_NUMBER() OVER (ORDER BY fecha, id) AS num_fila_global
FROM ventas;

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY fecha, id) AS num_fila_region
FROM ventas;

Aquí, ROW_NUMBER() asigna un número único a cada venta. En el segundo ejemplo, la numeración se reinicia para cada region.

RANK() y DENSE_RANK(): Rankings con o sin Saltos

• RANK(): Asigna un rango a cada fila. Si hay empates (filas con el mismo valor en la columna de ordenación), reciben el mismo rango y el siguiente rango salta (ej. 1, 1, 3).
• DENSE_RANK(): Similar a RANK(), pero no salta rangos después de los empates (ej. 1, 1, 2).

Vamos a clasificar las ventas por valor_venta dentro de cada region.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    RANK() OVER (PARTITION BY region ORDER BY (cantidad * precio) DESC) AS ranking_venta,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY (cantidad * precio) DESC) AS dense_ranking_venta
FROM ventas
ORDER BY region, ranking_venta;

NTILE(N): Distribución en Grupos

Divide las filas de una partición en N grupos (o tiles) tan iguales como sea posible y asigna un número de grupo a cada fila.

Queremos dividir nuestras ventas en 3 grupos (terciles) por valor de venta global.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    NTILE(3) OVER (ORDER BY (cantidad * precio) DESC) AS tercil_venta_global
FROM ventas
ORDER BY tercil_venta_global, valor_venta DESC;

📊 Funciones de Ventana de Agregación

Estas son funciones de agregación estándar (SUM, AVG, COUNT, MAX, MIN) utilizadas con OVER(), lo que les permite calcular agregados sobre la ventana sin colapsar las filas.

Sumas Acumulativas (Running Total)

Calcula la suma de un valor para todas las filas desde el inicio de la partición hasta la fila actual.

Queremos el total de ventas acumulado día a día en cada región.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    SUM(cantidad * precio) OVER (
        PARTITION BY region
        ORDER BY fecha, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_acumulado_region
FROM ventas
ORDER BY region, fecha, id;

Promedios Móviles (Moving Average)

Calcula el promedio de un valor sobre un número específico de filas anteriores y/o posteriores a la fila actual.

Queremos el promedio de ventas de los últimos 2 días para cada venta dentro de su región.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    AVG(cantidad * precio) OVER (
        PARTITION BY region
        ORDER BY fecha
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS avg_ultimos_2_dias
FROM ventas
ORDER BY region, fecha, id;

Este ejemplo calcula el promedio de la venta actual y la venta del día anterior (dentro de la misma región).

↔️ Funciones de Ventana de Desplazamiento (Offset)

Estas funciones permiten acceder a los valores de filas adyacentes a la fila actual dentro de la ventana.

LAG() y LEAD(): Accediendo a Filas Anteriores y Posteriores

• LAG(columna, offset, default_value): Recupera el valor de columna de una fila offset posiciones antes de la fila actual. Si no hay fila, devuelve default_value (o NULL).
• LEAD(columna, offset, default_value): Recupera el valor de columna de una fila offset posiciones después de la fila actual. Si no hay fila, devuelve default_value (o NULL).

Queremos comparar el valor de venta actual con el valor de la venta anterior y la siguiente dentro de la misma región.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    LAG(cantidad * precio, 1, 0.00) OVER (
        PARTITION BY region
        ORDER BY fecha, id
    ) AS venta_anterior,
    LEAD(cantidad * precio, 1, 0.00) OVER (
        PARTITION BY region
        ORDER BY fecha, id
    ) AS venta_siguiente
FROM ventas
ORDER BY region, fecha, id;

Estas funciones son extremadamente útiles para calcular diferencias entre períodos, detectar tendencias o comparar valores secuenciales.

FIRST_VALUE() y LAST_VALUE(): Primer y Último Valor de la Ventana

• FIRST_VALUE(columna): Recupera el valor de columna de la primera fila en la ventana.
• LAST_VALUE(columna): Recupera el valor de columna de la última fila en la ventana.

Queremos ver la primera y la última venta de cada día dentro de cada región.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    FIRST_VALUE(producto) OVER (
        PARTITION BY region, fecha
        ORDER BY (cantidad * precio) DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS producto_mas_caro_del_dia,
    LAST_VALUE(producto) OVER (
        PARTITION BY region, fecha
        ORDER BY (cantidad * precio) DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS producto_menos_caro_del_dia
FROM ventas
ORDER BY region, fecha, valor_venta DESC;

📝 Consideraciones Adicionales y Rendimiento

Rendimiento

Las funciones de ventana son poderosas, pero como cualquier operación compleja, pueden impactar el rendimiento. PostgreSQL es eficiente en su implementación, pero es importante tener en cuenta:

• ORDER BY: Un ORDER BY dentro de OVER() puede requerir una ordenación (sort) de los datos, lo cual es una operación costosa para grandes conjuntos de datos. Asegúrate de tener índices apropiados en las columnas usadas en PARTITION BY y ORDER BY.
• PARTITION BY: La partición de datos también implica una agrupación interna que puede ser costosa si no está bien indexada.
• frame_clause: Cláusulas de marco muy amplias (UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) pueden requerir que más datos se mantengan en memoria para cada cálculo.

Reuso de Definiciones de Ventana con WINDOW

Para consultas con múltiples funciones de ventana que comparten la misma definición OVER(), puedes definir la ventana una vez y reutilizarla usando la cláusula WINDOW.

SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    ROW_NUMBER() OVER w AS num_fila,
    RANK() OVER w AS ranking_venta
FROM ventas
WINDOW w AS (PARTITION BY region ORDER BY (cantidad * precio) DESC)
ORDER BY region, ranking_venta;

Esto no solo mejora la legibilidad sino que también puede ayudar a PostgreSQL a optimizar la ejecución, ya que solo necesita procesar la partición y el ordenamiento una vez.

🎯 Ejercicios Prácticos

Para consolidar tu conocimiento, intenta resolver los siguientes problemas usando las funciones de ventana:

1. Venta más alta por producto: Para cada producto, encuentra la venta con el ID más alto. (Pista: ROW_NUMBER())
2. Diferencia de venta diaria: Calcula la diferencia entre el valor de venta de cada registro y el valor de venta del registro del día anterior en la misma región. (Pista: LAG())
3. Porcentaje del total de la región: Para cada venta, calcula qué porcentaje representa del total de ventas de su región. (Pista: SUM() como función de ventana con UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- Ejercicio 1: Venta más alta por producto (no se pide el valor, sino el registro completo de la venta con el ID más alto)
WITH RankedSales AS (
    SELECT
        id,
        fecha,
        producto,
        region,
        (cantidad * precio) AS valor_venta,
        ROW_NUMBER() OVER (PARTITION BY producto ORDER BY id DESC) AS rn
    FROM ventas
)
SELECT
    id,
    fecha,
    producto,
    region,
    valor_venta
FROM RankedSales
WHERE rn = 1;

-- Ejercicio 2: Diferencia de venta diaria por región
SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    LAG(cantidad * precio, 1, 0.00) OVER (PARTITION BY region ORDER BY fecha, id) AS valor_venta_anterior,
    (cantidad * precio) - LAG(cantidad * precio, 1, 0.00) OVER (PARTITION BY region ORDER BY fecha, id) AS diferencia_con_anterior
FROM ventas
ORDER BY region, fecha, id;

-- Ejercicio 3: Porcentaje del total de la región
SELECT
    id,
    fecha,
    producto,
    region,
    (cantidad * precio) AS valor_venta,
    SUM(cantidad * precio) OVER (PARTITION BY region) AS total_ventas_region,
    ((cantidad * precio) / SUM(cantidad * precio) OVER (PARTITION BY region)) * 100 AS porcentaje_del_total_region
FROM ventas
ORDER BY region, fecha, id;

🔚 Conclusión

Las funciones de ventana en PostgreSQL son una herramienta indispensable para cualquier analista o desarrollador de bases de datos que necesite realizar análisis de datos complejos y detallados. Te permiten transformar tus datos de maneras que antes eran tediosas o imposibles con SQL estándar, ofreciendo una flexibilidad y un poder analítico excepcionales. Al dominar OVER(), PARTITION BY, ORDER BY y las cláusulas de marco, desbloquearás un nuevo nivel de capacidades en tus consultas SQL.

Sigue practicando con diferentes escenarios y pronto te sentirás cómodo aplicando estas poderosas funciones para resolver una amplia gama de desafíos de análisis de datos.