Funciones de Ventana SQL — Referencia Detallada

Resumen

Las funciones de ventana calculan un valor sobre un conjunto de filas relacionadas con la fila actual, sin colapsar el resultado en grupos como hace GROUP BY. Esa única propiedad resuelve una clase de problemas que de otro modo son dolorosos de escribir: rankear filas dentro de categorías, comparar una fila con sus vecinas o mostrar un total acumulado junto a cada línea de pedido sin dejar de devolver todos los pedidos.

Un escenario concreto: quieres los tres productos más vendidos por categoría, cada uno con sus ingresos, su porcentaje del total de la categoría y la diferencia con el siguiente producto. Solo con GROUP BY necesitarías dos pasadas de agregación más un self-join de vuelta a las filas de detalle. Con funciones de ventana es una sola consulta legible. El ejemplo resuelto más adelante hace exactamente eso.

Antes de que existieran las funciones de ventana, estos informes se resolvían con subconsultas correlacionadas, self-joins sobre “la fila anterior” o, en el MySQL antiguo, variables de sesión recorriendo un resultado ordenado. Esos enfoques son más difíciles de leer, de indexar y fáciles de romper sutilmente con empates. Las funciones de ventana los sustituyeron por un único constructo declarativo que el planificador puede optimizar.

Todos los motores importantes las incluyen (PostgreSQL, MySQL 8+, MariaDB 10.2+, SQL Server, Oracle y SQLite 3.25+), así que si escribes SQL analítico como informes, dashboards o procesos de deduplicación, las usarás cada semana.

Cuándo Usarlas

• Necesitas rankings dentro de grupos (top-N por categoría, “último registro por cliente”).
• Se requieren totales acumulados, promedios móviles o porcentajes acumulados.
• Quieres comparar cada fila con la anterior o la siguiente (crecimiento mes a mes, huecos entre sesiones).
• Los agregados deben aparecer junto al detalle de cada fila; GROUP BY ocultaría ese detalle.
• Un self-join para comparar filas sería demasiado complejo o lento.

Cuándo no usarlas: si solo necesitas un agregado por grupo sin detalle de filas, GROUP BY es más simple. Y si la “ventana” es un puñado de filas que se calcula una vez por petición, resolverlo en código de aplicación puede ser más fácil de probar.

Si el problema de fondo es la velocidad de la consulta y no la analítica, empieza por la guía completa de optimización de consultas SQL. Las funciones de ventana añaden al menos una ordenación, y eso no es gratis.

Sintaxis

nombre_funcion(expresion) OVER (
    [PARTITION BY expresion_particion]
    [ORDER BY expresion_orden]
    [clausula_marco]
)

Tres piezas opcionales controlan la ventana. PARTITION BY divide las filas en grupos independientes. ORDER BY ordena las filas dentro de cada partición. La cláusula de marco (ROWS, RANGE o GROUPS) elige qué filas alrededor de la actual alimentan a la función.

Hay dos valores por defecto que importan. Si omites PARTITION BY, todo el resultado se convierte en una única partición. Si omites el marco, obtienes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, un valor por defecto sutil que es la primera causa de resultados incorrectos. La sección de marcos lo explica.

Cuando varias funciones comparten la misma ventana, declárala una vez:

SELECT
    employee_id,
    RANK() OVER w,
    PERCENT_RANK() OVER w
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Las cláusulas WINDOW nombradas funcionan en PostgreSQL, MySQL 8, MariaDB y SQLite, pero no en SQL Server.

Cómo se Ejecutan las Funciones de Ventana

Lógicamente, las funciones de ventana se ejecutan tarde en la evaluación de la consulta: después de FROM, WHERE, GROUP BY y HAVING, aproximadamente junto a SELECT, y antes de DISTINCT y del ORDER BY final. De ahí se derivan dos consecuencias prácticas:

1. No puedes usar una función de ventana en WHERE ni en GROUP BY, porque la ventana todavía no existe en esa fase. Filtra los resultados de la ventana en una consulta externa o en una CTE.
2. Las funciones de ventana ven las filas ya agrupadas, no las filas crudas de la tabla. SUM(amount) OVER () después de GROUP BY month suma los totales mensuales, no las líneas individuales.

Cada fila de entrada produce exactamente una fila de salida; nada se colapsa. Esa es la diferencia central con GROUP BY, y es la razón por la que las funciones de ventana combinan tan bien dentro de CTEs. Si acabas apilando agregados y ventanas en una misma consulta, la guía de CTE en SQL cubre el patrón para mantenerla legible.

Algo que conviene saber para el rendimiento: cada especificación OVER() distinta (cada combinación única de PARTITION BY/ORDER BY) necesita su propia pasada ordenada sobre los datos. Un SELECT con tres definiciones de ventana diferentes suele ordenar tres veces. Compartir una WINDOW nombrada o alinear las particiones permite al motor reutilizar una sola ordenación.

Funciones de Ventana vs GROUP BY

	GROUP BY	Función de ventana
Filas de salida	Una por grupo	Las mismas que de entrada
Detalle de fila preservado	No	Sí
Usable en WHERE	No (agregados vía HAVING)	No — filtra en una consulta externa
Uso típico	”Total por categoría"	"Cada fila junto al total de su categoría”

Regla práctica: si tu SELECT mezcla columnas de detalle y agregados y no escribiste GROUP BY, probablemente quieres una función de ventana.

Funciones de Ranking

Función	Comportamiento	Manejo de duplicados
ROW_NUMBER()	Entero secuencial por partición	Sin empates; orden arbitrario entre duplicados
RANK()	Rango con huecos	Mismo valor → mismo rango; el siguiente rango salta
DENSE_RANK()	Rango sin huecos	Mismo valor → mismo rango; el siguiente rango continúa

-- Top 3 productos por ingresos en cada categoría
WITH ranked AS (
    SELECT
        product_id,
        category,
        revenue,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank
    FROM product_revenue
)
SELECT * FROM ranked WHERE rank <= 3;

Dos detalles causan problemas en producción. Primero, ROW_NUMBER no es determinista con empates: dos filas con el mismo revenue pueden intercambiar posiciones entre ejecuciones. Añade un desempate (ORDER BY revenue DESC, product_id) si el resultado alimenta una deduplicación o un informe que alguien comparará. Segundo, RANK produce huecos — con dos productos empatados en el rango 1, el siguiente es rango 3, así que WHERE rank <= 3 puede devolver cuatro filas. Usa ROW_NUMBER para “exactamente N filas por partición”, DENSE_RANK para “todas las filas en los N primeros rangos” y RANK cuando las posiciones empatadas deban consumir números de rango.

Funciones de Desplazamiento: LAG y LEAD

LAG(expr, n) devuelve expr de la fila n posiciones anterior a la actual; LEAD mira hacia adelante. Ambas aceptan un valor por defecto opcional para las filas de los extremos — LAG(revenue, 1, 0) devuelve 0 en lugar de NULL en la primera fila.

-- Comparar el mes actual con el anterior
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month_change,
    LEAD(revenue) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

Cuando conviertas la diferencia en porcentaje, protege la división con NULLIF — un mes anterior con cero o NULL devuelve NULL o lanza un error de división. El ejemplo resuelto muestra el patrón completo.

Un detalle con las series temporales: LAG devuelve la fila anterior, no el periodo anterior. Si un mes no tuvo pedidos, la fila no existe y LAG salta al último mes que sí existe. Cuando los huecos del calendario importan, une tus datos a una tabla de fechas (o generate_series en PostgreSQL) para que cada periodo tenga su fila antes de aplicar LAG.

Funciones de Valor

FIRST_VALUE, LAST_VALUE y NTH_VALUE devuelven una columna desde una posición concreta dentro del marco:

-- Comparar cada salario con el máximo del departamento
SELECT
    employee_id,
    department,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS top_salary,
    salary - FIRST_VALUE(salary) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS gap_to_top
FROM employees;

LAST_VALUE esconde una trampa conocida: con el marco por defecto (RANGE ... AND CURRENT ROW) nunca ve las filas posteriores a la actual, así que devuelve el valor de la propia fila. Para obtener el último valor real, amplía el marco explícitamente:

LAST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Funciones de Ventana Agregadas

Cualquier agregado — SUM, AVG, COUNT, MIN, MAX — funciona como función de ventana:

-- Total acumulado y promedio móvil de 7 días
SELECT
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM orders;

SUM(amount) OVER (ORDER BY order_date) se apoya en el marco por defecto, correcto para totales acumulados con fechas únicas — pero lee la siguiente sección antes de confiar en él con claves de ordenación duplicadas.

En PostgreSQL y SQLite, los agregados de ventana además aceptan FILTER (WHERE ...), que restringe qué filas del marco alimentan el agregado — por ejemplo SUM(amount) FILTER (WHERE status = 'paid') OVER (ORDER BY order_date) para un acumulado solo de pedidos pagados. MySQL y SQL Server no soportan FILTER; el equivalente portable es un CASE dentro del agregado: SUM(CASE WHEN status = 'paid' THEN amount END) OVER (...).

Cláusulas de Marco: ROWS, RANGE y GROUPS

Marco	Significado
ROWS BETWEEN n PRECEDING AND CURRENT ROW	Filas físicas: n filas atrás hasta la actual
ROWS UNBOUNDED PRECEDING	Desde el inicio de la partición hasta la fila actual
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW	Ventana lógica sobre el valor del ORDER BY
GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING	Grupos de pares — los empates cuentan como uno

ROWS cuenta filas físicas. RANGE cuenta valores: incluye toda fila cuyo valor de ORDER BY caiga dentro del rango, así que los empates son siempre pares. GROUPS cuenta posiciones de ordenación distintas, dejando que los empates se muevan juntos con desplazamientos posicionales. GROUPS requiere PostgreSQL 11+ o SQLite; RANGE con INTERVAL funciona en PostgreSQL, MySQL 8 y Oracle, pero no en SQL Server.

El detalle crítico: cuando escribes ORDER BY dentro de OVER() sin marco, el valor por defecto es RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Con claves de ordenación duplicadas, las filas empatadas entran juntas en el marco y el total acumulado salta por escalones en vez de acumular fila a fila:

-- Dos pedidos en la misma fecha -> RANGE incluye AMBOS en cada fila
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS stepped_total,
    SUM(amount) OVER (
        ORDER BY order_date ROWS UNBOUNDED PRECEDING
    ) AS true_running_total
FROM orders;

Si quieres acumulación fila a fila, escribe ROWS. Usa RANGE cuando el valor define la ventana (“todas las filas dentro de 7 días”). Recurre a GROUPS cuando los empates deben moverse juntos pero sigues queriendo desplazamientos posicionales.

Soporte por Motor

Motor	Desde	Notas
PostgreSQL	8.4 básico, 11 para GROUPS	Implementación más completa
MySQL	8.0	Sin GROUPS; RANGE con INTERVAL soportado
MariaDB	10.2	Similar a MySQL 8
SQL Server	2012 parcial, 2022 para IGNORE NULLS	Sin WINDOW nombrada; sin GROUPS; RANGE limitado a UNBOUNDED/CURRENT ROW
Oracle	8i	Soporte completo de marcos, incluido RANGE INTERVAL
SQLite	3.25	Soporte completo, incluido GROUPS

Dos reglas de portabilidad: prueba los marcos RANGE con INTERVAL en tu motor antes de depender de ellos, y evita las cláusulas WINDOW nombradas si la consulta también debe correr en SQL Server.

Ejemplos del Mundo Real

Deduplicación (quedarse con el más reciente por grupo)

WITH ranked AS (
    SELECT
        customer_id,
        email,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
    FROM customer_profiles
)
SELECT customer_id, email, updated_at FROM ranked WHERE rn = 1;

La deduplicación es donde ROW_NUMBER demuestra su valor. El mismo patrón elimina eventos duplicados, elige el precio más reciente o conserva la última dirección por cliente. Para más variantes de ranking — empates densos, top-N paginado — consulta la receta de ranking con funciones de ventana.

Percentiles y cuartiles

SELECT
    employee_id,
    department,
    salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary) AS quartile,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) AS percentile
FROM employees;

NTILE(4) divide cada departamento en cuatro grupos casi iguales, mientras PERCENT_RANK devuelve una posición entre 0 y 1 útil para filtros de “top 10%” — envuelve la consulta y aplica WHERE percentile >= 0.9 fuera. CUME_DIST es la función complementaria: devuelve la fracción de filas en o por debajo del valor actual.

Sesionización con huecos e islas

Un problema clásico de analítica: agrupar los eventos de un usuario en sesiones donde un hueco de más de N minutos abre una sesión nueva. El truco es comparar cada evento con el anterior usando LAG, marcar las nuevas sesiones y acumular la marca:

WITH flagged AS (
    SELECT
        user_id,
        event_time,
        CASE
            WHEN event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
            ) > INTERVAL '30 minutes'
            THEN 1 ELSE 0
        END AS new_session
    FROM events
)
SELECT
    user_id,
    event_time,
    SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM flagged;

session_id se incrementa cada vez que el hueco supera 30 minutos — sin cursores ni código procedural. La sintaxis de intervalo mostrada es de PostgreSQL; en MySQL usa TIMESTAMPDIFF(MINUTE, prev, curr) y en SQL Server DATEDIFF.

Ejemplo Resuelto: Análisis de Ventas

Juntándolo todo sobre una tabla realista — orders(id, customer_id, order_date, product_category, amount) con 10M de filas en dos años — cuatro patrones de ventana cubren la mayoría de los informes de ventas.

Top 3 productos por categoría y mes usa RANK sobre un agregado mensual. Observa que la ventana se apoya en la salida del GROUP BY, razón por la que SUM(amount) aparece dentro del ORDER BY de la ventana:

WITH monthly_category_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        product_category,
        product_id,
        SUM(amount) AS total_revenue,
        RANK() OVER (
            PARTITION BY DATE_TRUNC('month', order_date), product_category
            ORDER BY SUM(amount) DESC
        ) AS rank_in_category
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY DATE_TRUNC('month', order_date), product_category, product_id
)
SELECT month, product_category, product_id, total_revenue, rank_in_category
FROM monthly_category_sales
WHERE rank_in_category <= 3
ORDER BY month DESC, product_category, rank_in_category;

El crecimiento mes a mes encadena llamadas a LAG. El envoltorio NULLIF evita la división por cero en el primer mes de cada categoría:

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        product_category,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date), product_category
)
SELECT
    month,
    product_category,
    revenue,
    LAG(revenue) OVER (PARTITION BY product_category ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (PARTITION BY product_category ORDER BY month) AS abs_change,
    ROUND((
        (revenue - LAG(revenue) OVER (PARTITION BY product_category ORDER BY month))
        / NULLIF(LAG(revenue) OVER (PARTITION BY product_category ORDER BY month), 0)
    ) * 100, 2) AS pct_change
FROM monthly_totals
ORDER BY product_category, month;

Los totales acumulados y el porcentaje del año combinan un marco particionado con un OVER () vacío, que es la forma idiomática de obtener un gran total junto a las filas de detalle:

WITH monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total,
    ROUND(
        revenue / NULLIF(SUM(revenue) OVER (), 0) * 100, 2
    ) AS pct_of_year,
    ROUND(
        AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
        2
    ) AS three_month_avg
FROM monthly_totals
ORDER BY month;

Por último, la segmentación de clientes por cuartil de gasto mezcla NTILE y PERCENT_RANK sobre un agregado por cliente:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS lifetime_value,
        COUNT(*) AS order_count,
        NTILE(4) OVER (ORDER BY SUM(amount) DESC) AS spending_quartile,
        PERCENT_RANK() OVER (ORDER BY SUM(amount) ASC) AS percentile
    FROM orders
    WHERE order_date >= '2026-01-01'
    GROUP BY customer_id
)
SELECT
    customer_id,
    lifetime_value,
    order_count,
    spending_quartile,
    ROUND(percentile * 100, 1) AS percentile_pct
FROM customer_totals
WHERE spending_quartile = 1  -- Top 25%
ORDER BY lifetime_value DESC;

Estos cuatro patrones — top-N por grupo, variaciones entre periodos, totales acumulados con porcentaje del total y segmentación por cuantiles — cubren la mayoría de los informes analíticos. Además son combinables: nada impide rankear clientes dentro de un spending_quartile o calcular la variación mensual sobre los conteos de sesiones del ejemplo anterior. El hábito que vale la pena construir es leer cualquier requisito analítico como “qué filas rodean a esta fila y qué calculo sobre ellas” — una vez planteado así, el PARTITION BY, el ORDER BY y el marco prácticamente se escriben solos.

Consideraciones de Rendimiento

Las funciones de ventana añaden trabajo después de la agregación: como mínimo una ordenación por cada combinación distinta de PARTITION BY/ORDER BY, y encima un nodo WindowAgg (PostgreSQL) o Window Spool (SQL Server). En tablas grandes:

• Crea índices compuestos que coincidan con PARTITION BY + ORDER BY. Para PARTITION BY category ORDER BY revenue DESC, un índice en (category, revenue DESC) puede alimentar la ordenación directamente.
• Para LAG/LEAD sobre series temporales, un índice en (entity_id, event_time) evita una ordenación aparte.
• Ejecuta EXPLAIN ANALYZE y busca un Sort explícito sobre WindowAgg — si está ahí y es lento, el índice no se está usando. Vigila ordenaciones que vuelcan a disco (Disk en el plan); subir work_mem en PostgreSQL suele arreglarlo.
• Particiona la tabla por fecha cuando las consultas siempre filtran un rango temporal; la poda de particiones reduce la entrada de la ventana antes de ordenar.
• Reutilizar una WINDOW nombrada para varias funciones permite al motor compartir una sola ordenación en vez de reordenar por función.

Para profundizar en planes de ejecución y estrategia de índices, consulta la guía de ajuste de rendimiento SQL.

Buenas Prácticas

• Escribe siempre ORDER BY dentro de OVER() en ventanas de desplazamiento y agregadas — el resultado no tiene sentido sobre datos sin ordenar.
• Añade un desempate determinista (ORDER BY revenue DESC, id) cuando la salida alimenta deduplicación, paginación o un informe comparable.
• Prefiere marcos ROWS para ventanas de conteo de filas y RANGE solo cuando el valor de ordenación define la pertenencia; conoce el marco por defecto.
• Coloca los resultados de ventana tras una CTE y filtra en la consulta externa — de todos modos no puedes referenciar la salida de la ventana en WHERE.
• Nombra ventanas compartidas con WINDOW w AS (...) cuando una consulta repite la misma partición y ordenación (no en SQL Server).
• Protege las divisiones de tasas de crecimiento con NULLIF(denominador, 0).

Errores Comunes

• Olvidar PARTITION BY — la ventana se aplica a todo el resultado, así que cada métrica “por categoría” se convierte en global.
• Confiar en el marco por defecto — RANGE UNBOUNDED PRECEDING trata los empates como pares; los totales acumulados saltan en vez de acumular con claves duplicadas.
• Usar ROW_NUMBER sin desempate — salida no determinista con empates: la deduplicación conserva una fila “más reciente” distinta entre ejecuciones.
• Poner funciones de ventana en WHERE — se ejecutan después del WHERE; la consulta falla. Filtra en una consulta externa o CTE.
• Mezclar GROUP BY y ventanas sin cuidado — las ventanas ven filas agrupadas, no crudas. Usa una CTE para que cada nivel sea explícito.
• Esperar que LAG devuelva un valor real en la primera fila — devuelve NULL salvo que pases un valor por defecto; LEAD tiene el mismo caso límite en la última fila.
• Sin índice en las columnas de PARTITION BY/ORDER BY — la ordenación vuelca a disco en tablas grandes.

Solución de Problemas

• El total acumulado salta por escalones: estás en el marco RANGE por defecto con valores duplicados en ORDER BY. Cambia a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW o añade un desempate.
• LAST_VALUE devuelve el valor de la fila actual: el marco por defecto termina en la fila actual. Añade ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• “Window functions are not allowed in WHERE” (PostgreSQL) o errores equivalentes: mueve el filtro a una consulta externa sobre una CTE.
• La consulta es rápida con miles de filas pero lenta en producción: busca en EXPLAIN ANALYZE una ordenación que vuelca a disco; añade el índice compuesto y revisa work_mem.
• Los grupos de NTILE parecen desiguales: las filas sobrantes van a los primeros grupos por diseño. Usa cortes de PERCENT_RANK cuando necesites percentiles exactos.

Referencia Rápida

Necesidad	Función	Patrón
Secuencia única por grupo	ROW_NUMBER()	deduplicación: WHERE rn = 1
Rango con huecos en empates	RANK()	clasificaciones
Rango sin huecos en empates	DENSE_RANK()	”los N primeros niveles”
Fila anterior / siguiente	LAG() / LEAD()	LAG(col, 1, 0) define un valor por defecto
Valor en una posición del marco	FIRST_VALUE() / LAST_VALUE() / NTH_VALUE()	ojo al marco en LAST_VALUE
Total acumulado	SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)	ROWS para determinismo
Promedio móvil	AVG() OVER (... ROWS BETWEEN n PRECEDING AND CURRENT ROW)	mirada atrás de n filas
Posición en la distribución	PERCENT_RANK() / CUME_DIST() / NTILE(k)	cortes de percentil

Lectura Adicional

• Documentación de PostgreSQL: Window Functions y la referencia de la cláusula de marco
• Referencia de MySQL 8.4: Window Functions
• Documentación de SQL Server: SELECT - cláusula OVER
• Documentación de SQLite: Window Functions
• Relacionado en este sitio: guía de joins en SQL para los patrones de join que acompañan a las consultas con ventanas.