Funciones de ventana en SQL: OVER, PARTITION BY y ORDER BY

Lección 7.1 · Tiempo de lectura: ~8 min · Tareas de práctica ↓

Las funciones de ventana en SQL hacen un cálculo sobre un conjunto de filas relacionadas con la fila actual y devuelven un valor para cada fila, sin agruparlas como hace GROUP BY. En esta lección aprenderás la cláusula OVER, cómo PARTITION BY y ORDER BY definen la ventana, en qué se diferencian las funciones de ventana de la agrupación y cómo calcular un porcentaje del total en una sola consulta.

GROUP BY responde preguntas como "¿cuánto pagó en total cada cliente?", pero devuelve una fila por cliente y pierde los pagos individuales. Muchas preguntas de análisis necesitan ambas cosas: cada pago y el total del cliente, cada categoría de películas y su porción del total, cada fila y su posición en un ranking. Las funciones de ventana responden a estas preguntas en una sola consulta.

Los ejemplos se ejecutan en la base de datos de ejemplo Sakila. Las funciones de ventana están disponibles en MySQL 8.0+, MariaDB 10.2+, PostgreSQL, SQL Server, Oracle y SQLite 3.25+.

Qué es una función de ventana

Una función de ventana es una función seguida de una cláusula OVER. La cláusula OVER define la ventana: el conjunto de filas que la función mira cuando calcula el valor de la fila actual.

function_name(expression) OVER (
    PARTITION BY partition_columns
    ORDER BY sort_columns
    frame_clause
)

• PARTITION BY divide las filas en grupos independientes. Sin él, todas las filas forman una sola ventana.
• ORDER BY fija el orden de las filas dentro de cada partición. Las funciones de ranking lo necesitan; en las agregaciones, convierte el resultado en un cálculo acumulado.
• El marco (frame) limita la ventana a las filas alrededor de la actual (lección 7.3).

Tres familias de funciones trabajan con OVER:

Agregaciones como funciones de ventana

Agrega OVER a una función de agregación y deja de agrupar filas. Aquí cada pago del cliente 1 aparece junto con el total del cliente y su número de pagos:

SELECT
    payment_id,
    payment_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
    COUNT(*) OVER (PARTITION BY customer_id) AS customer_payments
FROM payment
WHERE customer_id = 1
ORDER BY payment_date
LIMIT 5;

Resultado: los primeros 5 de 32 pagos; cada fila muestra el mismo total del cliente, 118.68, y 32 pagos.

Con GROUP BY, los mismos números darían una sola fila: cliente 1, 118.68, 32. Con una función de ventana, el detalle se conserva y el valor del grupo se puede usar junto a él, por ejemplo amount / SUM(amount) OVER (...) para la porción de cada pago.

Función de ventana vs. GROUP BY

Porcentaje del total

OVER () con paréntesis vacíos convierte todo el resultado en una sola ventana. Combinado con GROUP BY, da la porción de cada grupo en el total:

SELECT
    c.name AS category,
    COUNT(*) AS films,
    ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_all
FROM film_category fc
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
ORDER BY films DESC
LIMIT 4;

Resultado: Sports 74 películas (7.4%), Foreign 73 (7.3%), Family 69 (6.9%), Documentary 68 (6.8%).

SUM(COUNT(*)) OVER () parece raro, pero se lee con naturalidad: GROUP BY calcula COUNT(*) por grupo y luego la función de ventana suma esos conteos sobre todos los grupos. Las funciones de ventana se evalúan después de la agrupación, así que pueden usar los resultados de las agregaciones.

ORDER BY dentro de OVER: numerar filas

Con ORDER BY dentro de OVER, la función ve las filas en un orden definido. ROW_NUMBER() las numera; PARTITION BY reinicia la numeración en cada grupo:

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

Resultado: 59 filas; los pagos del cliente 1 se numeran del 1 al 32, y la numeración vuelve a empezar en 1 para el cliente 2.

El ORDER BY dentro de OVER solo controla el cálculo. El orden del resultado lo fija el ORDER BY final de la consulta; sin él, las filas pueden llegar en cualquier orden.

Dónde se pueden usar las funciones de ventana

Las funciones de ventana se calculan tarde en el procesamiento de la consulta: después de FROM, WHERE, GROUP BY y HAVING, pero antes del ORDER BY final y de LIMIT. Dos consecuencias:

• WHERE filtra las filas antes de calcular la ventana, así que WHERE customer_id = 1 en el primer ejemplo determinó a partir de qué filas se calcularon los totales.
• Una función de ventana no se puede usar en WHERE ni en HAVING. Para filtrar por su resultado, calcúlala en una CTE o subconsulta y filtra en la consulta externa:

WITH numbered AS (
    SELECT
        customer_id,
        payment_date,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY payment_date DESC
        ) AS recency
    FROM payment
)
SELECT customer_id, payment_date, amount
FROM numbered
WHERE recency = 1
ORDER BY customer_id
LIMIT 3;

Resultado: el último pago de los clientes 1, 2 y 3; la consulta completa devuelve una fila por cada uno de los 599 clientes.

Este patrón de "numerar y luego filtrar" resuelve los ejercicios de top N; se tratan en detalle en la lección 12.6.

Conclusiones clave:

• Una función de ventana devuelve un valor para cada fila, calculado sobre una ventana de filas relacionadas; no agrupa filas como GROUP BY.
• La cláusula OVER define la ventana: PARTITION BY divide las filas en grupos, ORDER BY las ordena y un marco las limita.
• SUM(...) OVER (PARTITION BY ...) pone el total del grupo junto a cada fila; OVER () usa todas las filas.
• Las funciones de ventana se evalúan después de GROUP BY, así que SUM(COUNT(*)) OVER () da un porcentaje del total.
• No se pueden usar en WHERE: calcúlalas en una CTE y filtra afuera.

Preguntas frecuentes

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

GROUP BY devuelve una fila por grupo y oculta las filas individuales. Una función de ventana conserva cada fila y le agrega un valor calculado, como el total del grupo o una posición en el ranking.

¿Es obligatorio PARTITION BY en una función de ventana?

No. Sin PARTITION BY, todo el resultado es una sola ventana: SUM(amount) OVER () es el total general en cada fila. Usa PARTITION BY cuando el cálculo deba reiniciarse en cada grupo.

¿Por qué no puedo usar una función de ventana en WHERE?

WHERE se evalúa antes de calcular las funciones de ventana, así que sus valores todavía no existen. Calcula la función de ventana en una CTE o subconsulta y filtra por ella en la consulta externa. Algunas bases de datos (Snowflake, DuckDB, BigQuery) tienen para esto la cláusula QUALIFY.

¿Qué versión de MySQL admite funciones de ventana?

MySQL 8.0 y posteriores, y MariaDB 10.2 y posteriores. MySQL 5.7 no tiene funciones de ventana; allí se obtienen resultados parecidos con self-joins o subconsultas correlacionadas.