Critério ROWS e RANGE

Até o momento, não podemos ser flexíveis quanto à escolha de quantas linhas antes ou depois levar em conta. Agora isso é possível com os critérios ROWS e RANGE. Para usá-los, escrevemos:

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

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

E podemos especificar as seguintes opções:

• CURRENT ROW - a linha atual
• n PRECEDING - linhas antes da linha atual
• n FOLLOWING - linhas após a linha atual 

A diferença entre ROWS & RANGE é que o critério ROWS não se importa com os valores, apenas com as posições, enquanto RANGE define a janela em termos de intervalos de valores, em vez de posições das linhas.

Para RANGE, devemos especificar ORDER BY --column_name--, pois, caso contrário, ele não saberia como escolher a janela.

Por exemplo:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Aqui, ele cria uma janela que inclui a linha atual, a linha anterior e a linha seguinte.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Aqui, ele cria uma janela que inclui, para cada nível (ordenado em ordem crescente), o nível atual, um nível antes dele e um nível depois dele. Se o nível atual for 5, ele incluirá os níveis 4, 5 e 6.

Observação: O uso de RANGE BETWEEN pode resultar na inclusão de mais linhas na sua janela, pois inclui todas as linhas que compartilham os mesmos valores que aqueles no intervalo, enquanto ROWS BETWEEN sempre incluirá o mesmo número de linhas (desde que estejam disponíveis no conjunto de dados). 

Além disso, RANGE não oferece suporte a colunas de data.

Aqui está um exemplo simples 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