ROWS & RANGE Criterion

Parte da seção Fundamentos do Journey de SQL da Coddy — lição 69 de 72.

Até agora, não podemos ser flexíveis em relação à escolha de quantas linhas antes ou depois levar em conta. Agora é possível com os critérios ROWS & 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 considera os valores, apenas as posições, enquanto RANGE define a janela em termos de intervalos de valores em vez de posições de linhas.

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

Por exemplo:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Aqui, cria uma janela que inclui a linha atual, a linha anterior a ela e a linha posterior a ela.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Aqui, cria-se 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, então incluirá os níveis 4, 5 e 6.

Nota: O uso de RANGE BETWEEN pode resultar em mais linhas sendo incluídas na sua janela, porque inclui todas as linhas que compartilham os mesmos valores que aquelas no intervalo, enquanto ROWS BETWEEN sempre incluirá o mesmo número de linhas (contanto que estejam disponíveis no conjunto de dados).

Além disso, RANGE não suporta 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

Desafio

Fácil

Tabelas e colunas disponíveis:
newspapers: date, num_newspapers

Para este desafio, temos jornais para produção. Gostaríamos de saber a média de jornais impressos dois dias antes da linha atual e um dia depois. Além disso, gostaríamos de saber a diferença entre o máximo e o mínimo número de jornais impressos a partir da data atual e de todos os três dias anteriores. Chame essas colunas de avg_newspapers e diff_newspapers, respectivamente.

Folha de consulta

Use ROWS e RANGE para definir frames de janela flexíveis em funções de janela:

ROWS - define a janela por posições de linhas:

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

RANGE - define a janela por intervalos de valores (requer ORDER BY):

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

Opções de limites de janela:

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

Exemplos:

-- ROWS: includes current row + 1 before + 1 after (by position)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- RANGE: includes values within range (by value)
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Principais diferenças: