ROWS & RANGE Criterion

Parte de la sección Fundamentos del Journey de SQL de Coddy — lección 69 de 72.

Hasta ahora, no podíamos ser flexibles respecto a elegir cuántas filas antes o después tomar en cuenta. Ahora es posible con los criterios ROWS & 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 & RANGE es que el criterio ROWS no se preocupa por los valores, solo por las posiciones, mientras que RANGE define la ventana en términos de rangos de valores en lugar de posiciones de fila.

Para RANGE debemos especificar ORDER BY --column_name-- porque si no, 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 posterior.

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, uno antes de él y uno después de él. Si el nivel actual es 5, entonces incluirá los niveles 4, 5 y 6.

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

También RANGE no soporta columnas de fecha.

Aquí hay un ejemplo simple para ilustrar ROWS vs RANGE:

Usando ROWS:

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

Usando RANGE:

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

Desafío

Fácil

Tablas y columnas disponibles:
newspapers: date, num_newspapers

Para este desafío, tenemos periódicos para producción. Nos gustaría saber el promedio de periódicos que se imprimieron dos días antes de la fila actual y un día después. También, nos gustaría saber la diferencia entre el máximo y el mínimo número de periódicos impresos desde la fecha actual y los tres días anteriores. Llama a estas columnas avg_newspapers y diff_newspapers respectivamente.

Hoja de referencia

Usa ROWS y RANGE para definir marcos de ventana flexibles en funciones de ventana:

ROWS - define la ventana por posiciones de fila:

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

RANGE - define la ventana por rangos de valores (requiere ORDER BY):

OVER (ORDER BY column_name RANGE BETWEEN --START-- AND --END--)

Opciones de límites de ventana:

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

Ejemplos:

-- ROWS: incluye la fila actual + 1 antes + 1 después (por posición)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- RANGE: incluye valores dentro del rango (por valor)
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Diferencias clave: