Критерии ROWS и RANGE
Часть раздела Основы путешествия по SQL на Coddy. Урок 69 из 72.
До сих пор мы не могли гибко выбирать, сколько строк до или после учитывать. Теперь это возможно с критериями ROWS и RANGE. Чтобы использовать их, мы пишем:
OVER (ROWS BETWEEN --START-- AND --END--)
OVER (RANGE BETWEEN --START-- AND --END--)
И мы можем указать следующие параметры:
CURRENT ROW— текущая строкаn PRECEDING— строки перед текущей строкойn FOLLOWING— строки после текущей строки
Разница между ROWS и RANGE заключается в том, что критерий ROWS не учитывает значения, а только позиции, тогда как RANGE определяет окно диапазонами значений, а не позициями строк.
Для RANGE мы обязательно должны указать ORDER BY --column_name--, потому что иначе система не будет знать, как выбрать окно.
Например:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGЗдесь создаётся окно, которое включает текущую строку, предыдущую строку и следующую строку.
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWINGЗдесь создаётся окно, которое для каждого уровня (отсортированного по возрастанию) включает текущий уровень, уровень перед ним и уровень после него. Если текущий уровень равен 5, то в него войдут уровни 4, 5 и 6.
Примечание: Использование RANGE BETWEEN может привести к включению в окно большего количества строк, поскольку оно включает все строки с теми же значениями, что и значения в диапазоне, тогда как ROWS BETWEEN всегда включает одинаковое количество строк (если они доступны в наборе данных).
Кроме того, для дат, сохранённых как текст (например, '2024-01-05'), смещение RANGE не выполняет вычисления с датами: при 1 PRECEDING окно содержит только строки с той же датой. Чтобы считать дни, сортируй по числу, например по julianday(date).
Вот простой пример, иллюстрирующий различие между ROWS и RANGE:
Используя ROWS:
SELECT employee_name, salary,
AVG(salary) OVER (
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as avg_salary_rowsИспользование RANGE:
SELECT employee_name, salary,
AVG(salary) OVER (
ORDER BY salary
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
) as avg_salary_rangeЗадание
ЛегкоДоступные таблицы и столбцы:
newspapers:date,num_newspapers
В этом задании у нас есть данные о производстве газет. Мы хотим узнать среднее количество газет, напечатанных за два дня до текущей строки и за один день после неё. Также мы хотим узнать разницу между максимальным и минимальным количеством газет, напечатанных в текущую дату и за все три предшествующих дня. Назовите эти столбцы avg_newspapers и diff_newspapers соответственно.
Попробуйте сами
В этом уроке есть небольшой тест. Начните урок, чтобы ответить на вопросы и сохранить прогресс.
Все уроки раздела Основы
4Больше ключевых слов
Ключевое слово INКлючевое слово BETWEENКлючевое слово LIKEКлючевое слово ASПовторение - Модели мобильных телефонов2Условия
Основы условийКлючевое слово ANDКлючевое слово ORКлючевое слово NOTКомбинирование нескольких условийСкобкиБулевы значения5Арифметические операции
Математические операторыМатематические столбцыОперация moduloФункция ROUND()3Специальный формат возврата
Значения NULLСортировка результатов, часть 1Сортировка результатов, часть 2Повторение - Фирма по кибербезопасностиОграничение количества записейПовторение - Автозавод6Вводные вызовы
Повторение - Парламентские выборыПовторение - Арест преступника полициейПовторение - Ёмкость для напитков в бареПовторение - Создание новых столбцов9Несколько таблиц
Базовый JOIN, Часть 1Базовый JOIN, Часть 2Повторение - JOINСамосоединениеПовторение - СамосоединениеОбъединениеУпрощение запросов, ключевое слово WITHПовторение - Запросы WITHПовторение - Подрядчик по недвижимости12Оконные функции, часть 2
Функции RANK и DENSE_RANKПовторение - RANK и DENSE_RANKФункция NTILEАгрегатные функцииКритерии ROWS и RANGEПотренируйтесь самостоятельно: Песочница SQL