Errores típicos al usar funciones de ventana

Ahora toca hablar de las complicaciones que pueden surgir cuando trabajas con funciones de ventana. Como siempre, en programación (y en la vida) es mejor aprender de los errores de otros. Vamos a ver los errores más típicos que suelen cometer los principiantes y, a veces, incluso los desarrolladores con experiencia, y aprenderemos a evitarlos.

Error nº1: Uso incorrecto de PARTITION BY

Uno de los fallos más comunes es olvidarse o poner mal el parámetro PARTITION BY, sobre todo si quieres dividir los datos en grupos. Sin él, PostgreSQL va a tratar todas las filas como un solo grupo enorme, y los resultados pueden ser muy diferentes a lo que esperas.

Supón que tienes una tabla sales que contiene datos de ventas:

Quieres calcular la suma acumulada (SUM()) de ventas por mes para cada región. Puedes escribir una consulta así:

SELECT
    region,
    month,
    SUM(total) OVER (ORDER BY month) AS running_total
FROM 
    sales;

Resultado:

A primera vista parece que todo va bien. Pero el resultado claramente no es el esperado, porque la suma acumulada se calcula para todas las filas juntas, no por región. El problema es que nos olvidamos de poner PARTITION BY region.

Código corregido:

SELECT 
    region,
    month,
    SUM(total) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM 
    sales;

Resultado:

Ahora sí que funciona bien: los datos se agrupan por región y la suma acumulada se calcula por separado para cada una.

Error nº2: Orden incorrecto en ORDER BY

ORDER BY dentro de OVER() controla el orden de las filas dentro de la ventana. Si el orden está mal, los resultados pueden ser raros.

Quieres calcular las sumas acumuladas de ventas, ordenadas por mes descendente. Puedes escribir una consulta así:

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month DESC) AS running_total
FROM 
    sales;

Resultado:

Parece que todo está bien, pero fíjate: las filas están agrupadas por mes, pero la suma no se calcula correctamente por el orden descendente. Por eso los resultados son confusos.

Solución: reescribe la consulta usando el orden correcto en ORDER BY:

SELECT
    month,
    total,
    SUM(total) OVER (ORDER BY month ASC) AS running_total
FROM 
    sales;

Error nº3: Usar funciones de ventana sin índices

Las funciones de ventana suelen trabajar con grandes volúmenes de datos, y si no tienes índices en las columnas clave, el rendimiento puede caer en picado.

Ejemplo: tenemos una tabla large_sales con millones de filas y queremos calcular los rangos de ventas:

SELECT
    id,
    total,
    RANK() OVER (ORDER BY total DESC) AS rank
FROM 
    large_sales;

Con pocos datos la consulta va rápido, pero con muchos puede tardar una eternidad.

Solución: añade un índice en la columna que usas en ORDER BY:

CREATE INDEX idx_total ON large_sales(total DESC);

Ahora la consulta irá mucho más rápido.

Error nº4: No entender la ventana definida por ROWS o RANGE

Cuando usas ROWS y RANGE es importante saber cómo calculan la ventana de filas. Si no entiendes bien estas palabras clave, puedes tener resultados inesperados.

Ejemplo: quieres calcular la media móvil de ventas del mes actual y los dos anteriores:

SELECT
    month,
    AVG(total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

Si en vez de ROWS pones RANGE:

SELECT
    month,
    AVG(total) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales;

El resultado será diferente, porque RANGE trabaja con rangos de valores, no con un número concreto de filas.

Error nº5: Uso excesivo de funciones de ventana

Usar varias funciones de ventana en una sola consulta puede provocar cálculos duplicados y bajar el rendimiento.

Ejemplo:

SELECT 
    id,
    total,
    SUM(total) OVER (PARTITION BY region) AS region_total,
    SUM(total) OVER (PARTITION BY region) / COUNT(total) OVER (PARTITION BY region) AS region_avg
FROM 
    sales;

Aquí SUM(total) y COUNT(total) se calculan varias veces para cada fila.

Solución: reduce la consulta usando subconsultas o CTE:

WITH cte_region_totals AS (
    SELECT 
        region,
        SUM(total) AS region_total,
        COUNT(total) AS region_count
    FROM 
        sales
    GROUP BY 
        region
)
SELECT 
    s.id,
    s.total,
    t.region_total,
    t.region_total / t.region_count AS region_avg
FROM 
    sales s
JOIN 
    cte_region_totals t ON s.region = t.region;

Consejos para evitar errores

Revisa PARTITION BY y ORDER BY: asegúrate siempre de que la ventana está bien definida.

Pon índices en los datos: sobre todo si usas ordenación (ORDER BY) o filtrado.

Usa CTE para cálculos repetidos: así evitas pasos duplicados.

Mira el plan de ejecución: usa EXPLAIN y EXPLAIN ANALYZE para ver cómo PostgreSQL procesa tu consulta.

Haz pruebas con datos reales: asegúrate de que los resultados son los que esperas y que resuelves bien el problema.