Menu
CoddyTech

Critério ROWS e RANGE

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

Até agora, não podemos escolher com flexibilidade 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 anteriores à linha atual
  • n FOLLOWING - linhas posteriores à 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 das posições das linhas.

Para RANGE, é obrigatório 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.

ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Aqui, ele cria uma janela que inclui, para cada nível (ordenados 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 ela 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, porque inclui todas as linhas que compartilham os mesmos valores das linhas 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, em datas armazenadas como texto (como '2024-01-05'), um deslocamento RANGE não faz cálculos com datas: com 1 PRECEDING, a janela contém apenas as linhas com a mesma data. Para contar dias, ordene por um número, como julianday(date).

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
challenge icon

Desafio

Fácil

Tabelas e colunas disponíveis:

  • newspapers: date, num_newspapers

Para este desafio, temos dados de produção de jornais. 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 número máximo e mínimo de jornais impressos desde a data atual até os três dias anteriores. Chame essas colunas de avg_newspapers e diff_newspapers, respectivamente.

Experimente você mesmo

quiz iconTeste seus conhecimentos

Esta lição inclui um quiz rápido. Comece a lição para respondê-lo e acompanhar seu progresso.

Todas as lições de Fundamentos

Pratique por conta própria: Playground de SQL