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

Здесь создается окно, которое включает текущую строку, строку перед ней и строку после нее.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Здесь создается окно, которое для каждого уровня (отсортированных по возрастанию) включает текущий уровень, один уровень перед ним и один уровень после него. Если текущий уровень равен 5, то оно включит уровни 4, 5 и 6.

Примечание: Использование RANGE BETWEEN может привести к включению большего количества строк в ваше окно, поскольку оно включает все строки, которые имеют те же значения, что и строки в диапазоне, в то время как ROWS BETWEEN всегда будет включать одинаковое количество строк (при условии, что они доступны в наборе данных).

Также RANGE не поддерживает столбцы дат.

Вот простой пример, иллюстрирующий разницу между 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 соответственно.

Шпаргалка

Используйте ROWS и RANGE для определения гибких рамок окон в оконных функциях:

ROWS — определяет окно по позициям строк:

OVER (ROWS BETWEEN --START-- AND --END--)

RANGE — определяет окно по диапазонам значений (требует ORDER BY):

OVER (ORDER BY column_name RANGE BETWEEN --START-- AND --END--)

Варианты границ окна:

CURRENT ROW — текущая строка
n PRECEDING — n строк/значений перед текущей
n FOLLOWING — n строк/значений после текущей

Примеры:

-- ROWS: includes current row + 1 before + 1 after (by position)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- RANGE: includes values within range (by value)
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Ключевые различия:

ROWS — на основе позиций строк, всегда одинаковое количество строк
RANGE — на основе значений, может включать больше строк с одинаковыми значениями
RANGE не поддерживает столбцы с датами

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