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 atualn PRECEDING- linhas anteriores à linha atualn 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 FOLLOWINGAqui, 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 FOLLOWINGAqui, 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_rowsUsando RANGE:
SELECT employee_name, salary,
AVG(salary) OVER (
ORDER BY salary
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) as avg_salary_rangeDesafio
FácilTabelas 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
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
4Mais palavras-chave
A palavra-chave INA palavra-chave BETWEENA palavra-chave LIKEA palavra-chave ASRecapitulação - Modelos de celulares2Condições
Noções básicas de condiçõesA palavra-chave ANDA palavra-chave ORA palavra-chave NOTCombinação de várias condiçõesParêntesesBooleanos3Formato de retorno específico
Valores nulosOrdenar resultados - Parte 1Ordenar resultados - Parte 2Recapitulação - Empresa de segurança cibernéticaLimitar o número de registrosRecapitulação - Fábrica de veículos6Desafios introdutórios
Revisão - Eleição parlamentarRevisão - Prisão criminal pela políciaRevisão - Recipiente de bebidas de barRevisão - Projetar novas colunas9Múltiplas tabelas
JOIN básico – Parte 1JOIN básico – Parte 2Revisão – JOINAuto-JOINRevisão – Auto-JOINUNIONSimplifique consultas com a palavra-chave WITHRevisão – Consultas com WITHRevisão – Prestador de serviços imobiliários12Funções de Janela parte 2
Funções RANK e DENSE_RANKRecapitulação - RANK e DENSE_RANKFunção NTILEFunções de agregaçãoCritério ROWS e RANGEPratique por conta própria: Playground de SQL