Criterios ROWS y RANGE
Parte de la sección Fundamentos del Journey de SQL de Coddy. Lección 69 de 72.
Por ahora, no podemos elegir con flexibilidad cuántas filas anteriores o posteriores tener en cuenta. Ahora es posible con los criterios ROWS y 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 actualn PRECEDING- filas anteriores a la fila actualn FOLLOWING- filas posteriores a la fila actual
La diferencia entre ROWS y RANGE es que el criterio ROWS no tiene en cuenta los valores, solo las posiciones, mientras que RANGE define la ventana en términos de rangos de valores en lugar de posiciones de filas.
Para RANGE debemos especificar ORDER BY --column_name-- porque, de lo contrario, no sabría cómo elegir la ventana.
Por ejemplo:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGAquí crea una ventana que incluye la fila actual, la fila anterior y la fila siguiente.
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWINGAquí crea una ventana que incluye, para cada nivel (ordenado de forma ascendente), el nivel actual, un nivel anterior y un nivel posterior. Si el nivel actual es 5, incluirá los niveles 4, 5 y 6.
Nota: El uso de RANGE BETWEEN podría dar como resultado que se incluyan más filas en tu ventana, porque incluye todas las filas que comparten los mismos valores que los del rango, mientras que ROWS BETWEEN siempre incluirá la misma cantidad de filas (siempre que estén disponibles en el conjunto de datos).
Además, en las fechas almacenadas como texto (como '2024-01-05'), un desplazamiento RANGE no realiza operaciones aritméticas con fechas: con 1 PRECEDING, la ventana contiene solo las filas con la misma fecha. Para contar días, ordena por un número, como julianday(date).
Aquí tienes un ejemplo sencillo para ilustrar ROWS frente a RANGE:
Usando ROWS:
SELECT employee_name, salary,
AVG(salary) OVER (
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as avg_salary_rowsUso de RANGE:
SELECT employee_name, salary,
AVG(salary) OVER (
ORDER BY salary
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) as avg_salary_rangeDesafío
FácilTablas y columnas disponibles:
newspapers:date,num_newspapers
Para este desafío, tenemos datos de producción de periódicos. Nos gustaría conocer el promedio de periódicos que se imprimieron desde dos días antes de la fila actual hasta un día después. También nos gustaría conocer la diferencia entre el número máximo y mínimo de periódicos impresos desde la fecha actual y los tres días anteriores. Llama a estas columnas avg_newspapers y diff_newspapers, respectivamente.
Pruébalo tú mismo
Esta lección incluye un breve cuestionario. Empieza la lección para responderlo y registrar tu progreso.
Todas las lecciones de Fundamentos
4Más palabras clave
La palabra clave INLa palabra clave BETWEENLa palabra clave LIKELa palabra clave ASRepaso: modelos de teléfonos móviles2Condiciones
Conceptos básicos de las condicionesLa palabra clave ANDLa palabra clave ORLa palabra clave NOTCombinación de múltiples condicionesParéntesisBooleanos5Operaciones aritméticas
Operadores matemáticosColumnas matemáticasLa operación móduloLa función ROUND()3Formato de retorno específico
Valores nulosOrdenar resultados Parte 1Ordenar resultados Parte 2Repaso: empresa de ciberseguridadLimitar el número de registrosRepaso: fábrica de vehículos6Retos introductorios
Repaso - Elecciones parlamentariasRepaso - Detención policial de un criminalRepaso - Envase de bebidas de barRepaso - Diseñar nuevas columnas9Varias tablas
JOIN básico, parte 1JOIN básico, parte 2Repaso: JOINAuto JOINRepaso: auto JOINUNIONSimplificar consultas: palabra clave WITHRepaso: consultas con WITHRepaso: contratista inmobiliario12Funciones de ventana, parte 2
Funciones RANK y DENSE_RANKRepaso: RANK y DENSE_RANKFunción NTILEFunciones de agregaciónCriterios ROWS y RANGEPractica por tu cuenta: Playground de SQL