Window functions avanzadas

Las window functions clásicas resuelven el 80 % de los problemas analíticos, pero hay un conjunto de funcionalidades menos conocidas que aparecen en informes financieros, métricas de salud y análisis de eventos. Dominarlas evita acudir a CTEs anidadas o subconsultas correlacionadas innecesarias.

El frame GROUPS frente a ROWS y RANGE

Las cláusulas ROWS y RANGE son las habituales: ROWS cuenta filas físicas, RANGE cuenta valores lógicos contiguos. PostgreSQL 11 introdujo el frame GROUPS, que cuenta grupos de filas con el mismo valor en la columna de orden.

SELECT
    fecha,
    ventas,
    SUM(ventas) OVER (
        ORDER BY fecha
        GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS suma_grupo_actual_y_anterior
FROM ventas_diarias;

Si varias filas comparten la misma fecha (peers), GROUPS las trata como un único grupo. ROWS las cuenta una a una y RANGE las trata como un rango contiguo de valores.

La diferencia clave: con ROWS BETWEEN 1 PRECEDING AND CURRENT ROW recorres dos filas físicas; con GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW recorres dos grupos completos, lo que puede sumar muchas más filas si los datos repiten valores ordenados.

La cláusula EXCLUDE

La cláusula EXCLUDE permite excluir filas concretas del frame ya definido:

• EXCLUDE CURRENT ROW: excluye la fila actual.
• EXCLUDE GROUP: excluye la fila actual y todos sus peers.
• EXCLUDE TIES: excluye los peers, pero mantiene la fila actual.
• EXCLUDE NO OTHERS: comportamiento por defecto, no excluye nada.

SELECT
    empleado_id,
    departamento,
    salario,
    AVG(salario) OVER (
        PARTITION BY departamento
        ORDER BY empleado_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        EXCLUDE CURRENT ROW
    ) AS salario_medio_resto
FROM empleados;

Este patrón calcula el salario medio del resto del departamento, sin contar al empleado actual. Es la forma más limpia de evitar que la propia fila contamine la métrica que se compara contra ella.

IGNORE NULLS en LAG y LEAD

PostgreSQL 16 añadió la cláusula IGNORE NULLS a LAG, LEAD, FIRST_VALUE, LAST_VALUE y NTH_VALUE. Cuando hay huecos en una serie temporal, te permite obtener el último valor no nulo:

CREATE TABLE lecturas_sensor (
    sensor_id INT,
    instante TIMESTAMPTZ,
    temperatura NUMERIC
);

SELECT
    sensor_id,
    instante,
    temperatura,
    LAG(temperatura) IGNORE NULLS
        OVER (PARTITION BY sensor_id ORDER BY instante) AS temp_anterior_real
FROM lecturas_sensor;

Sin IGNORE NULLS, LAG(temperatura) devuelve NULL cuando la fila anterior no tiene lectura. Con IGNORE NULLS, el motor recorre hacia atrás hasta encontrar el último valor no nulo. Esto reemplaza patrones complicados con CTEs y COALESCE repetido.

La sintaxis equivalente compatible es RESPECT NULLS (comportamiento por defecto). Conviene declararla explícitamente cuando la intención sea documentarla.

Percentiles continuos y discretos

Para calcular medianas y percentiles SQL ofrece dos funciones: PERCENTILE_CONT (continuo, interpola) y PERCENTILE_DISC (discreto, devuelve un valor real del dataset).

SELECT
    departamento,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario) AS mediana,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salario) AS mediana_discreta,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salario) AS p95,
    PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY salario) AS quartiles
FROM empleados
GROUP BY departamento;

La diferencia práctica: con un dataset [1, 2, 3, 4] el percentil 50:

• PERCENTILE_CONT(0.5) devuelve 2.5 (interpolación entre el 2 y el 3).
• PERCENTILE_DISC(0.5) devuelve 2 (un valor que existe realmente).

Ambas se pueden usar como window functions combinándolas con OVER, pero requieren la cláusula WITHIN GROUP (ORDER BY ...). Cuando se pasan arrays como argumento, devuelven un array con un valor por percentil, lo que evita repetir la consulta varias veces.

Agregaciones ordenadas con WITHIN GROUP

La cláusula WITHIN GROUP se asocia a agregaciones que dependen del orden: además de los percentiles, las funciones MODE, RANK, DENSE_RANK, PERCENT_RANK y CUME_DIST la usan en su forma de agregación, distinta de la versión window.

SELECT
    departamento,
    MODE() WITHIN GROUP (ORDER BY tipo_contrato) AS contrato_mas_comun,
    RANK(50000) WITHIN GROUP (ORDER BY salario) AS rank_de_50k
FROM empleados
GROUP BY departamento;

MODE() WITHIN GROUP (ORDER BY columna) devuelve el valor que más se repite. La forma RANK(valor) WITHIN GROUP calcula el ranking que tendría un valor hipotético si estuviera dentro del grupo, sin necesidad de insertarlo.

Caso práctico: análisis de cohortes con detección de gaps

Combinar varias técnicas avanzadas resuelve análisis de cohortes complejos. Imagina que quieres calcular el tiempo medio entre eventos consecutivos por usuario, ignorando registros marcados como inválidos:

WITH eventos_validos AS (
    SELECT user_id, evento_at, valido
    FROM eventos
)
SELECT
    user_id,
    evento_at,
    EXTRACT(EPOCH FROM (
        evento_at - LAG(evento_at) IGNORE NULLS
            OVER (PARTITION BY user_id ORDER BY evento_at)
    )) AS segundos_desde_anterior_valido,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY
        EXTRACT(EPOCH FROM (
            evento_at - LAG(evento_at) IGNORE NULLS
                OVER (PARTITION BY user_id ORDER BY evento_at)
        ))
    ) OVER (PARTITION BY user_id) AS mediana_segundos_user
FROM eventos_validos
WHERE valido = true;

Esta consulta combina IGNORE NULLS, LAG, PERCENTILE_CONT con OVER y WITHIN GROUP en una sola pasada. Sustituye varias subconsultas correlacionadas por código más rápido y legible.

Optimización de window functions

Las window functions con frames complejos pueden ser costosas. PostgreSQL las ejecuta en una fase aparte tras el ordenamiento, y la complejidad crece con el número de particiones y el tamaño del frame.

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    user_id,
    SUM(monto) OVER (
        PARTITION BY user_id
        ORDER BY fecha
        GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )
FROM pagos;

Buenas prácticas:

• 1. Reducir el dataset con un WHERE antes de la window function siempre que sea posible.
• 2. Indexar la columna de PARTITION BY y ORDER BY combinada para evitar el Sort previo: CREATE INDEX ON pagos (user_id, fecha).
• 3. Limitar el frame a lo estrictamente necesario en lugar de UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• 4. Usar work_mem suficiente para que el sort no derrame a disco.

flowchart LR
    A[Tabla pagos] --> B[WHERE filtra]
    B --> C[Sort por<br/>user_id, fecha]
    C --> D[WindowAgg<br/>frame GROUPS]
    D --> E[Resultado]

Las window functions avanzadas convierten en una sola consulta lo que antes requería múltiples pasadas. Combinadas con frames precisos y IGNORE NULLS, permiten resolver problemas de series temporales irregulares sin código procedural ni acumuladores de aplicación.