Критерии ROWS и RANGE

На данный момент мы не можем гибко выбирать, сколько строк до или после учитывать. Теперь это возможно с критериями 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