Menu
CoddyTech

Критерии 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
challenge icon

Задание

Легко

Доступные таблицы и столбцы:

  • newspapers: date, num_newspapers

В этом задании у нас есть данные о производстве газет. Мы хотим узнать среднее количество газет, напечатанных за два дня до текущей строки и за один день после неё. Также мы хотим узнать разницу между максимальным и минимальным количеством газет, напечатанных в текущую дату и за все три предшествующих дня. Назовите эти столбцы avg_newspapers и diff_newspapers соответственно.

Попробуйте сами

quiz iconПроверьте себя

В этом уроке есть небольшой тест. Начните урок, чтобы ответить на вопросы и сохранить прогресс.

Все уроки раздела Основы

Потренируйтесь самостоятельно: Песочница SQL