PostgreSQL
Window functions
Funciones de ventana para calcular rankings, acumulados, comparaciones y agregados sin colapsar las filas originales del resultado.
- Última actualización
- Actualizada
- Nivel
- Aplicación
PostgreSQL
Funciones de ventana para calcular rankings, acumulados, comparaciones y agregados sin colapsar las filas originales del resultado.
GROUP BY transforma muchas filas en una fila por grupo. Una window function conserva cada fila y calcula un valor respecto a otras filas:
filas originales
↓
se define una partición, un orden y un frame
↓
se calcula un valor por cada fila
↓
las filas siguen existiendo individualmenteEjemplo:
SELECT
id,
branch_id,
created_at,
total_amount,
sum(total_amount) OVER (
PARTITION BY branch_id
ORDER BY created_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;Cada order conserva sus columnas. running_total añade el acumulado de su branch hasta esa fila.
Sin funciones de ventana, tareas como estas suelen requerir self joins, subqueries correlacionadas o procesamiento en aplicación:
Esas alternativas pueden ser más difíciles de leer y, según el caso, ejecutar trabajo repetido.
Una window function tiene tres decisiones principales:
PARTITION BY
→ qué filas pertenecen al mismo universo de cálculo
ORDER BY
→ en qué secuencia se observan dentro de la partición
FRAME
→ qué subconjunto relativo a la fila actual participaNo todas las funciones usan las tres partes de la misma forma. row_number necesita orden, pero no utiliza un frame como una aggregate window. sum y last_value sí dependen fuertemente del frame.
Las window functions se evalúan después de WHERE, GROUP BY y HAVING, pero antes del ORDER BY final de la consulta.
Por eso esto no funciona directamente:
SELECT
id,
row_number() OVER (ORDER BY created_at) AS position
FROM orders
WHERE position <= 10;El alias todavía no existe durante WHERE. Usa una subquery o CTE:
WITH ranked AS (
SELECT
id,
created_at,
row_number() OVER (ORDER BY created_at DESC, id DESC) AS position
FROM orders
)
SELECT *
FROM ranked
WHERE position <= 10;sum(total_amount) OVER (PARTITION BY branch_id)Cada branch obtiene su propio total. Sin PARTITION BY, toda la entrada forma una sola partición.
Una partición no agrupa físicamente la salida. Solo define el conjunto de referencia de la función.
row_number() OVER (
PARTITION BY branch_id
ORDER BY created_at, id
)El orden debe ser determinista cuando el resultado depende de posición. Si dos filas comparten created_at y no existe desempate, PostgreSQL puede asignar posiciones distintas entre ejecuciones.
Añadir una columna única como id produce un orden total.
Para aggregates utilizadas como window functions, el frame define qué filas participan en el cálculo de cada fila.
Ejemplo acumulado:
sum(total_amount) OVER (
ORDER BY created_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)Ejemplo total completo:
sum(total_amount) OVER ()Ejemplo media móvil de siete filas:
avg(total_amount) OVER (
ORDER BY created_at, id
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)Cuenta filas físicas en el orden de la ventana.
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWIncluye hasta siete filas.
Trabaja con valores del ORDER BY y peers. Puede incluir varias filas con el mismo valor aunque conceptualmente esperabas una sola.
Cuenta grupos de peers, es decir, conjuntos con el mismo valor de orden.
La elección cambia el resultado. Para acumulados fila a fila, ROWS explícito suele ser más fácil de razonar.
Asigna un número distinto a cada fila:
row_number() OVER (
PARTITION BY branch_id
ORDER BY total_amount DESC, id
)Los empates reciben el mismo rango y dejan huecos:
100 → 1
100 → 1
90 → 3Los empates no dejan huecos:
100 → 1
100 → 1
90 → 2Expresan posición relativa. Revisa su definición exacta antes de usarlas en contratos de negocio.
Requisito: tres productos con mayores ventas por categoría.
WITH ranked_products AS (
SELECT
product_id,
category_id,
revenue,
row_number() OVER (
PARTITION BY category_id
ORDER BY revenue DESC, product_id
) AS position
FROM product_sales
)
SELECT product_id, category_id, revenue
FROM ranked_products
WHERE position <= 3;Flujo:
row_number asigna posición.Si los empates deben incluirse, usa rank y acepta que una categoría pueda devolver más de tres filas.
lag observa una fila anterior; lead, una posterior:
SELECT
product_id,
recorded_at,
quantity,
lag(quantity) OVER (
PARTITION BY product_id
ORDER BY recorded_at, id
) AS previous_quantity
FROM inventory_snapshots;Diferencia:
quantity - lag(quantity) OVER (...) AS quantity_changeLa primera fila de cada partición no tiene anterior y devuelve NULL salvo default explícito.
first_value(status) OVER (
PARTITION BY order_id
ORDER BY changed_at, id
)last_value suele sorprender porque el frame por defecto termina en la peer row actual, no necesariamente en el final de la partición.
Para obtener el último estado completo:
last_value(status) OVER (
PARTITION BY order_id
ORDER BY changed_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)Cualquier aggregate compatible puede utilizarse con OVER:
SELECT
id,
total_amount,
sum(total_amount) OVER () AS global_total,
total_amount / NULLIF(sum(total_amount) OVER (), 0) AS share
FROM orders
WHERE status = 'paid';Aquí sum no colapsa las orders; añade el total global a cada fila.
Cuando varias funciones comparten configuración:
SELECT
id,
sum(total_amount) OVER branch_window AS running_total,
avg(total_amount) OVER branch_window AS running_average
FROM orders
WINDOW branch_window AS (
PARTITION BY branch_id
ORDER BY created_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);Reduce duplicación y evita pequeñas diferencias accidentales.
SELECT
product_id,
occurred_at,
quantity_delta,
sum(quantity_delta) OVER (
PARTITION BY branch_id, product_id
ORDER BY occurred_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS calculated_stock,
lag(quantity_delta) OVER (
PARTITION BY branch_id, product_id
ORDER BY occurred_at, id
) AS previous_delta
FROM inventory_movements
WHERE branch_id = $1;La ventana calcula stock histórico por branch/product. Esto sirve para auditoría o reconstrucción, pero no reemplaza una estrategia de concurrencia para el stock actual.
La ventana opera sobre las filas que recibe. Si un join multiplicó orders por items y payments, el acumulado también estará inflado.
Antes de aplicar una ventana, pregunta:
¿Cuál es la granularidad de una fila en este punto?Puede ser necesario agregar cada lado primero.
Funciones como lag pueden devolver NULL porque:
Esas situaciones no son equivalentes. Un default explícito puede ocultar la diferencia.
Las window functions suelen necesitar ordenar por partición y orden. Costes relevantes:
Un índice alineado con filtros y orden puede reducir trabajo, pero no garantiza eliminar todos los sorts.
Ejemplo de índice potencial:
CREATE INDEX orders_branch_created_idx
ON orders(branch_id, created_at, id)
INCLUDE (total_amount);La utilidad depende de selectividad, visibilidad y plan.
El ranking es no determinista entre peers.
Una única branch con millones de filas puede requerir sort y memoria significativa.
Un last_value o acumulado produce resultados inesperados.
Filtrar después de calcular cambia el conjunto visible, pero la ventana ya se calculó sobre la subquery completa.
Pueden requerir varios sorts.
Una window como count(*) OVER () calcula el total sobre todas las filas coincidentes aunque solo devuelvas una página; puede ser costosa.
La partición define universo; el frame, subconjunto relativo.
Produce rankings y lag inestables.
Devuelve el último valor del frame actual, no necesariamente de la partición.
Los cálculos reflejan duplicados.
La fase lógica no lo permite.
Calcula información; no impide estados inválidos concurrentes.
ROWS frente a RANGE.GROUP BY puede ser más directo.last_value requiere revisar el frame.GROUP BY y una window function no son intercambiables?rank y dense_rank?last_value puede devolver el valor de la fila actual?ROWS sobre RANGE?rank deja huecos después de empates; dense_rank no.Funciones, operadores y sargabilidad conecta expresiones SQL con tipos, volatilidad e índices.