Configurando el frame de ventana con ROWS y RANGE

Cuando usas funciones de ventana, surge la pregunta: "¿Cuántas filas dentro de la ventana participan en el cálculo del valor para la fila actual?" La respuesta depende del frame de ventana.

Frame de ventana — es el rango de filas que se usa para calcular el resultado de la función de ventana. Este rango se construye a partir de la fila actual y condiciones adicionales que defines con ROWS o RANGE.

Un ejemplo sencillo: al calcular una suma acumulativa, puedes indicar:

• Considerar solo la fila actual.
• Considerar la fila actual y todas las filas anteriores.
• Considerar la fila actual y un número fijo de filas arriba/abajo.

Justo ROWS y RANGE controlan qué filas entran en el frame de ventana.

Uso de ROWS

ROWS define el frame de ventana a nivel físico de las filas. O sea, cuenta las filas de arriba a abajo según su orden, sin importar los valores en esas filas.

Sintaxis

función_de_ventana OVER (
    ORDER BY columna
    ROWS BETWEEN inicio AND fin
)

Expresiones clave:

• CURRENT ROW — la fila actual.
• número PRECEDING — un número definido de filas arriba de la actual.
• número FOLLOWING — un número definido de filas debajo de la actual.
• UNBOUNDED PRECEDING — desde el inicio de la ventana.
• UNBOUNDED FOLLOWING — hasta el final de la ventana.

Ejemplo: suma acumulativa para la fila actual y las 2 anteriores

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Explicación:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa: toma la fila actual y las dos filas anteriores.
• La suma acumulativa se calculará solo para esas tres filas.

Resultado:

Ejemplo: análisis de "ventana móvil" con número fijo de filas

Tarea: calcular el salario promedio para la fila actual y las dos siguientes.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Resultado:

Uso de RANGE

RANGE construye el frame de ventana según los valores, no la posición de las filas. O sea, las filas se incluyen en el frame si sus valores en la columna de ORDER BY caen dentro del rango indicado.

Sintaxis

función_de_ventana OVER (
    ORDER BY columna
    RANGE BETWEEN inicio AND fin
)

Ejemplo: suma acumulativa por rango de valores

Tarea: calcular la suma acumulativa para filas donde el salario difiere del actual en no más de 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Explicación:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING significa: toma las filas donde el valor de salary está en el rango de ±2000 respecto a la fila actual.

Resultado:

Comparación de ROWS y RANGE

• ROWS trabaja con filas reales y su cantidad. No depende de los valores.
• RANGE trabaja con el rango lógico de valores definido para la columna de ORDER BY.

Para comparar, veamos un ejemplo. Supón que tenemos una tabla sales con estos datos:

Comparemos las consultas:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Resultado:

Aquí cada fila se suma según su aparición real.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Resultado:

Aquí las filas 1 y 2 se agrupan porque su amount = 100. RANGE tiene en cuenta valores repetidos en la columna amount.

Ejemplos de tareas reales

1. Cálculo del incremento de ingresos

Tarea: calcular el cambio de ingresos respecto a la fila anterior.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Comparar la fila actual con el promedio del grupo

Tarea: para cada departamento, calcular la diferencia entre el salario del empleado y el salario promedio del departamento.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Errores al usar ROWS y RANGE

Orden de filas (ORDER BY) mal especificado: Si no pones el orden de las filas, PostgreSQL dará error porque no puede determinar la fila actual.

Mezclar enfoques de ROWS y RANGE en una misma tarea: Elige el enfoque según tus datos. ROWS va bien para tareas con número fijo de filas, y RANGE — para rangos de valores.

Ignorar valores repetidos en RANGE: Recuerda que RANGE incluye todos los valores repetidos, lo que puede cambiar mucho el resultado.