Funciones de ventana en SQL: ROW_NUMBER, RANK y acumulados

Las funciones de ventana en SQL calculan un valor para cada fila usando una "ventana" de filas relacionadas, sin colapsar las filas como lo hace GROUP BY. Con ellas puedes hacer rankings de vendedores con ROW_NUMBER, RANK y DENSE_RANK, calcular acumulados con SUM() OVER y comparar cada mes con el anterior usando LAG. Todas las bases de datos principales las soportan, incluidas PostgreSQL, SQL Server, MySQL 8+, SQLite 3.25+ y los data warehouses en la nube.

Funciones de ventana vs GROUP BY

Con GROUP BY, diez filas de una región se convierten en una sola fila de resumen. Con una función de ventana, las diez filas se mantienen y cada una recibe una columna extra, como su posición dentro de la región o el total de la región al lado. Por eso las funciones de ventana son ideales para preguntas como "¿qué porcentaje de las ventas de la región hizo cada vendedor?", donde necesitas el detalle y el total juntos. Si las agregaciones son nuevas para ti, lee primero GROUP BY y funciones de agregación.

La cláusula OVER

Toda función de ventana lleva una cláusula OVER (...) con hasta tres partes:

• PARTITION BY: divide las filas en grupos (por ejemplo, por región). El cálculo se reinicia en cada partición. Es opcional.
• ORDER BY: define el orden dentro de cada partición. Es obligatorio para rankings, LAG/LEAD y acumulados.
• Marco (ROWS BETWEEN ...): indica exactamente qué filas alrededor de la actual se incluyen, como "las dos filas anteriores y esta".

Rankings: ROW_NUMBER, RANK y DENSE_RANK

Toma una tabla rep_totals con las ventas trimestrales de cada vendedor: Ana (East) 1,200, Mia (West) 1,200, Rosa (East) 1,000 y Luis (East) 800. Ana y Mia están empatadas, y ahí es donde las tres funciones de ranking se diferencian.

SELECT
    region,
    rep,
    total,
    ROW_NUMBER() OVER (ORDER BY total DESC) AS row_num,
    RANK()       OVER (ORDER BY total DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY total DESC) AS dense_rnk
FROM rep_totals;

• ROW_NUMBER siempre da números únicos. Con empates, cuál fila recibe el 1 o el 2 es arbitrario, a menos que agregues un criterio de desempate, como ORDER BY total DESC, rep.
• RANK da el mismo número a los empates y luego salta (1, 1, 3), como la tabla de posiciones de un torneo.
• DENSE_RANK da el mismo número a los empates sin dejar huecos (1, 1, 2).

Ranking dentro de cada grupo

Agrega PARTITION BY para reiniciar el ranking en cada región:

SELECT
    region,
    rep,
    total,
    RANK() OVER (PARTITION BY region ORDER BY total DESC) AS region_rank
FROM rep_totals;

En East, Ana queda en 1, Rosa en 2 y Luis en 3; en West, Mia queda en 1.

NTILE: divide las filas en grupos

NTILE(n) divide las filas ordenadas en n grupos de tamaño parecido y devuelve el número de grupo. Es una forma rápida de etiquetar clientes como mitad superior o mitad inferior, o de armar cuartiles para un reporte de segmentación:

SELECT
    rep,
    total,
    NTILE(2) OVER (ORDER BY total DESC, rep) AS half
FROM rep_totals;

Ana y Mia quedan en el grupo 1, y Rosa y Luis en el grupo 2. Cuando las filas no se dividen exactamente, los primeros grupos reciben una fila extra cada uno. Recuerda que NTILE divide por cantidad de filas, no por valor, así que valores empatados pueden quedar en grupos distintos.

Top N por grupo y eliminación de duplicados

No puedes filtrar una función de ventana en el WHERE, porque las ventanas se calculan después de que se ejecuta el WHERE. Envuelve la consulta en un CTE y filtra afuera. Este patrón clásico devuelve el mes más reciente de cada vendedor desde una tabla monthly_sales (rep, region, month, amount):

WITH ranked AS (
    SELECT
        rep,
        month,
        amount,
        ROW_NUMBER() OVER (PARTITION BY rep ORDER BY month DESC) AS rn
    FROM monthly_sales
)
SELECT rep, month, amount
FROM ranked
WHERE rn = 1;

El mismo patrón sirve para eliminar registros duplicados (te quedas con rn = 1 por cada ID de cliente) y para encontrar los 3 productos principales de cada categoría (te quedas con rn <= 3). Aprende más sobre esta estructura en CTEs y subconsultas en SQL.

Acumulados y promedios móviles

Supón que monthly_sales tiene las ventas de Ana: 500 en julio, 300 en agosto y 400 en septiembre. Un acumulado suma cada mes a todo lo anterior:

SELECT
    rep,
    month,
    amount,
    SUM(amount) OVER (
        PARTITION BY rep
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM monthly_sales;

El acumulado de Ana es 500, 800 y 1,200. Se reinicia para cada vendedor gracias a la partición.

¿Por qué escribir el marco de forma explícita? Cuando usas ORDER BY sin marco, la mayoría de las bases de datos usa por defecto RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, que trata como un solo bloque las filas con el mismo valor en el ORDER BY. Si dos filas comparten fecha, ambas reciben el mismo acumulado. Con ROWS obtienes una acumulación real fila por fila.

Promedio móvil

Un promedio móvil de 3 meses suaviza los datos con mucho ruido:

AVG(amount) OVER (
    PARTITION BY rep
    ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m

Para los meses de Mia (600, 250, 350) devuelve 600, 425 y 400. Observa que los dos primeros meses promedian menos de tres valores.

Porcentaje del total

Una agregación con OVER (PARTITION BY ...) y sin ORDER BY pone el total del grupo en cada fila, lo que facilita calcular participaciones:

SELECT
    region,
    rep,
    total,
    ROUND(100.0 * total / SUM(total) OVER (PARTITION BY region), 1) AS pct_of_region
FROM rep_totals;

En East, Ana tiene 40.0%, Rosa 33.3% y Luis 26.7%. El 100.0 evita la división entera en bases de datos como SQL Server y PostgreSQL cuando las columnas son números enteros.

LAG y LEAD: compara con filas anteriores

LAG devuelve un valor de una fila anterior y LEAD de una fila posterior. Son perfectas para calcular cambios mes contra mes:

SELECT
    rep,
    month,
    amount,
    LAG(amount) OVER (PARTITION BY rep ORDER BY month)          AS prev_month,
    amount - LAG(amount) OVER (PARTITION BY rep ORDER BY month) AS change
FROM monthly_sales;

Para Ana: julio no tiene mes anterior (NULL), agosto muestra 500 y un cambio de -200, y septiembre muestra 300 y +100. Puedes indicar un desplazamiento y un valor por defecto, como LAG(amount, 12, 0), para mirar 12 filas atrás (un año en datos mensuales, si no faltan meses) y devolver 0 cuando no exista.

Próximos pasos

Practica recreando las dos tablas pequeñas de esta guía y ejecutando cada consulta hasta que puedas predecir el resultado. Después usa funciones de ventana con tus propios datos de ventas: una tabla de posiciones, un acumulado y un cambio mes contra mes. Además, son de los temas más frecuentes en las preguntas de entrevista de SQL, así que vale la pena dominarlas. Si usas Power BI, encontrarás las mismas ideas en funciones de DAX como RANKX y en las medidas de inteligencia de tiempo.

Mira: lecciones en video

▶

▶

Preguntas frecuentes