Criterio ROWS y RANGE

Hasta ahora, no podemos ser flexibles al elegir cuántas filas anteriores o posteriores tener en cuenta. Ahora es posible con los criterios ROWS y RANGE. Para usarlos, escribimos:

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

Y podemos especificar las siguientes opciones:

• CURRENT ROW - la fila actual
• n PRECEDING - filas antes de la fila actual
• n FOLLOWING - filas después de la fila actual 

La diferencia entre ROWS y RANGE es que el criterio ROWS no tiene en cuenta los valores, solo las posiciones, mientras que RANGE define la ventana en términos de rangos de valores en lugar de posiciones de filas.

Para RANGE debemos especificar ORDER BY --column_name-- porque, de lo contrario, no sabría cómo elegir la ventana.

Por ejemplo:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Aquí crea una ventana que incluye la fila actual, la fila anterior y la fila siguiente.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Aquí crea una ventana que incluye, para cada nivel (ordenado en orden ascendente), el nivel actual, un nivel anterior y un nivel posterior. Si el nivel actual es 5, incluirá los niveles 4, 5 y 6.

Nota: El uso de RANGE BETWEEN podría incluir más filas en tu ventana, porque incluye todas las filas que comparten los mismos valores que los del rango, mientras que ROWS BETWEEN siempre incluirá el mismo número de filas (siempre que estén disponibles en el conjunto de datos). 

Además, RANGE no admite columnas de fecha.

Aquí tienes un ejemplo sencillo para ilustrar ROWS frente a RANGE:

Uso de ROWS:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

Uso de RANGE:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range