Lesión 7.1 · Tiempo de lectura: ~8 minutos

Esta lección introduce las funciones de ventana en SQL y muestra cómo mantienen el detalle a nivel de fila mientras añaden contexto analítico. Aprenderás cómo funcionan OVER, PARTITION BY y ORDER BY, y al final podrás construir consultas básicas de ventana en Sakila.

Funciones de Ventana para Análisis Avanzado de Datos

Las funciones de ventana son una de las características más poderosas en SQL para realizar cálculos analíticos complejos. A diferencia de las funciones de agregación que colapsan múltiples filas en un único resultado, las funciones de ventana te permiten realizar cálculos a través de un conjunto de filas que están relacionadas con la fila actual, todo mientras se preservan las filas individuales en tu conjunto de resultados.

Esta lección introduce los conceptos fundamentales de las funciones de ventana y demuestra cómo pueden transformar tus capacidades de análisis de datos.

¿Qué Son las Funciones de Ventana?

Una función de ventana realiza un cálculo a través de un conjunto de filas de la tabla que están de alguna manera relacionadas con la fila actual. Este conjunto de filas se llama "ventana" o "marco de ventana". La diferencia clave con respecto a las funciones de agregación regulares es que las funciones de ventana no causan que las filas se agrupen en una única fila de salida; cada fila mantiene su identidad.

Piénsalo como mirar a través de una ventana en movimiento mientras escaneas tus datos. Para cada fila, puedes ver y calcular valores basados en filas relacionadas a su alrededor, pero cada fila aún aparece por separado en el resultado.

Características clave:

• Las funciones de ventana operan sobre un conjunto de filas definidas por la cláusula OVER
• Devuelven un valor para cada fila en el conjunto de resultados
• No reducen el número de filas devueltas por la consulta
• Pueden ser utilizadas para clasificaciones, agregaciones y operaciones analíticas

Sintaxis Básica

La sintaxis general para una función de ventana es:

window_function_name(expresión) OVER (
    [PARTITION BY expresión_de_partición]
    [ORDER BY expresión_de_ordenación]
    [cláusula_de_marco_de_ventana]
)

Componentes:

• window_function_name: La función a aplicar (por ejemplo, ROW_NUMBER, SUM, AVG)
• cláusula OVER: Define la ventana de filas para la función
• PARTITION BY (opcional): Divide el conjunto de resultados en particiones (grupos)
• ORDER BY (opcional): Define el orden de las filas dentro de cada partición
• cláusula_de_marco_de_ventana (opcional): Refina aún más qué filas se incluyen en la ventana

Tu Primera Función de Ventana: ROW_NUMBER()

Comencemos con una de las funciones de ventana más comúnmente utilizadas: ROW_NUMBER(). Esta función asigna un número secuencial único a cada fila dentro de una partición.

Ejemplo 1: Numerando Todos los Pagos

SELECT
    payment_id,
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (ORDER BY payment_date) AS row_num
FROM
    payment
LIMIT 10;

Esta consulta asigna un número secuencial a cada pago ordenado por la fecha de pago. La cláusula OVER (ORDER BY payment_date) le dice a SQL que:

1. Ordene todas las filas por payment_date
2. Asigne números de fila comenzando desde 1

Ejemplo 2: Numerando Dentro de Grupos Usando PARTITION BY

El verdadero poder de las funciones de ventana se manifiesta cuando usas PARTITION BY para crear ventanas separadas para diferentes grupos:

SELECT
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY payment_date
    ) AS payment_number
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id, 
    payment_date;

Esto es lo que sucede:

• PARTITION BY customer_id crea una ventana separada para cada cliente
• Dentro de la ventana de cada cliente, las filas se ordenan por payment_date
• ROW_NUMBER() comienza a contar desde 1 para cada nuevo cliente
• Esto te permite ver el 1er, 2do, 3er pago para cada cliente

Visualización:

Cliente 1:     Cliente 2:     Cliente 3:
Fila 1 ----\     Fila 1 ----\     Fila 1 ----\
Fila 2 -----\    Fila 2 -----\    Fila 2 -----\
Fila 3 ------\   Fila 3 ------\   Fila 3 ------\
   ...           ...             ...

Cada cliente tiene su propia numeración de filas independiente.

Aplicaciones Prácticas

Encontrando la Transacción Más Reciente

Las funciones de ventana facilitan la identificación del registro más reciente en cada grupo:

WITH numbered_payments AS (
    SELECT
        customer_id,
        amount,
        payment_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY payment_date DESC
        ) AS recency_rank
    FROM
        payment
)
SELECT
    customer_id,
    amount,
    payment_date
FROM
    numbered_payments
WHERE
    recency_rank = 1
ORDER BY
    customer_id
LIMIT 10;

Esta consulta encuentra el pago más reciente para cada cliente al:

1. Numerar los pagos para cada cliente en orden de fecha descendente
2. Filtrar para recency_rank = 1 (el más reciente)

Comparando Cada Fila con Valores Agregados

Las funciones de ventana también pueden realizar agregaciones mientras mantienen filas individuales:

SELECT
    customer_id,
    amount,
    payment_date,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_spent,
    AVG(amount) OVER (PARTITION BY customer_id) AS avg_payment,
    amount - AVG(amount) OVER (PARTITION BY customer_id) AS diff_from_avg
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_date;

Para cada pago, esta consulta muestra:

• El monto del pago individual
• El monto total que este cliente ha gastado (a través de todos sus pagos)
• El monto promedio de pago para este cliente
• Cuánto difiere este pago específico de su promedio

Observa cómo las funciones de agregación regulares requerirían un GROUP BY y colapsarían las filas, pero las funciones de ventana te permiten mantener todo el detalle mientras añades contexto agregado.

Funciones de Ventana vs. GROUP BY

Es importante entender la diferencia:

GROUP BY (Funciones de Agregación):

SELECT
    customer_id,
    COUNT(*) AS payment_count,
    SUM(amount) AS total_amount
FROM
    payment
GROUP BY
    customer_id;

Resultado: Una fila por cliente

Funciones de Ventana:

SELECT
    customer_id,
    payment_id,
    amount,
    COUNT(*) OVER (PARTITION BY customer_id) AS payment_count,
    SUM(amount) OVER (PARTITION BY customer_id) AS total_amount
FROM
    payment;

Resultado: Cada fila de pago preservada, con valores agregados añadidos como columnas adicionales

Preguntas Frecuentes

¿Por qué usar funciones de ventana en lugar de GROUP BY?

GROUP BY es útil para tablas resumen, pero oculta el detalle a nivel de fila. Las funciones de ventana te permiten mantener cada fila y añadir contexto agregado junto a ella.

¿Es necesario PARTITION BY?

No. Si lo omites, todo el conjunto de resultados se convierte en una partición. Eso es útil cuando deseas clasificaciones o métricas a través de todas las filas.

¿Se pueden combinar funciones de ventana con WHERE?

Sí. WHERE filtra filas antes de que se ejecute el cálculo de la ventana, por lo que primero seleccionas los datos relevantes y luego calculas la ventana.

Preguntas de Entrevista

¿Cuál es la principal diferencia entre una función de ventana y una función de agregación?

Una agregación con GROUP BY reduce filas, mientras que una función de ventana devuelve un valor para cada fila y preserva el detalle.

¿Qué hace PARTITION BY en una ventana?

Divide el conjunto de resultados en secciones independientes, y la función de ventana se evalúa por separado dentro de cada sección.

¿Cuándo usarías ROW_NUMBER?

Úsalo cuando necesites numeración de filas dentro de un grupo, la fila más reciente o primera, o selección de los N principales dentro de cada categoría.

Conclusiones Clave

• Las funciones de ventana realizan cálculos a través de filas relacionadas mientras mantienen todas las filas individuales en el conjunto de resultados.
• La cláusula OVER es esencial y define la ventana de filas sobre la que la función debe operar.
• PARTITION BY divide los datos en grupos, aplicando la función de ventana por separado a cada grupo.
• ORDER BY dentro de la cláusula OVER determina el orden de las filas para la función (crucial para funciones como ROW_NUMBER()).
• Las funciones de ventana son perfectas para clasificaciones, totales acumulativos, promedios móviles y comparaciones de valores individuales con agregados de grupo.
• A diferencia de GROUP BY, las funciones de ventana no colapsan filas; añaden columnas calculadas a tus datos existentes.

En las próximas lecciones, exploraremos más funciones de ventana como RANK(), DENSE_RANK(), NTILE(), y profundizaremos en marcos de ventana y cálculos analíticos avanzados.