Настройка оконного фрейма с помощью ROWS и RANGE

Когда вы используете оконные функции, возникает вопрос: "Сколько строк внутри окна участвует в вычислении значения для текущей строки?" Ответ на этот вопрос зависит от оконного фрейма.

Оконный фрейм — это диапазон строк, который используется для вычисления результата оконной функции. Этот диапазон строится на основе текущей строки, а также дополнительных условий, заданных через ROWS или RANGE.

Простой пример: рассчитывая накопительную сумму, вы можете указать:

• Учитывать только текущую строку.
• Учитывать текущую строку и все строки выше.
• Учитывать текущую строку и фиксированное количество строк выше/ниже.

Именно ROWS и RANGE управляют тем, какие строки попадут в оконный фрейм.

Использование ROWS

ROWS определяет оконный фрейм на уровне физического расположения строк. Это означает, что оно считает строки сверху вниз в их порядке, независимо от значений в этих строках.

Синтаксис

оконная_функция OVER (
    ORDER BY столбец
    ROWS BETWEEN начало AND конец
)

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

• CURRENT ROW — текущая строка.
• число PRECEDING — определённое число строк выше текущей.
• число FOLLOWING — определённое число строк ниже текущей.
• UNBOUNDED PRECEDING — от начала окна.
• UNBOUNDED FOLLOWING — до конца окна.

Пример: накопительная сумма для текущей строки и 2 предыдущих

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Объяснение:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW означает: возьми текущую строку и две строки выше неё.
• Накопительная сумма будет рассчитываться только для этих трёх строк.

Результат:

Пример: анализ "скользящего окна" с фиксированным числом строк

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

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Результат:

Использование RANGE

RANGE строит оконный фрейм на основе значений, а не позиционного расположения строк. Это означает, что строки включаются в фрейм, если их значения в столбце ORDER BY попадают в указанный диапазон.

Синтаксис

оконная_функция OVER (
    ORDER BY столбец
    RANGE BETWEEN начало AND конец
)

Пример: накопительная сумма по диапазону значений

Задача: рассчитать накопительную сумму для строк, где зарплата отличается от текущей не более чем на 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Объяснение:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING означает: возьми строки, где значение salary находится в диапазоне ±2000 от текущей строки.

Результат:

Сравнение ROWS и RANGE

• ROWS работает с реальными строками и их количеством. Он не зависит от значений.
• RANGE работает с логическим диапазоном значений, заданным для столбца из ORDER BY.

Для сравнения приведем пример. Допустим, у нас есть таблица sales с данными:

Сравним запросы:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Результат:

Здесь каждую строку система добавляет к сумме по мере их реального появления.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Результат:

Здесь строки 1 и 2 объединились, так как их amount = 100. RANGE учитывает повторяющиеся значения в столбце amount.

Примеры реальных задач

1. Расчет прироста дохода

Задача: рассчитать изменение дохода по сравнению с предыдущей строкой.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Сравнение текущей строки со средним в группе

Задача: для каждого отдела рассчитать разницу зарплаты сотрудника со средней зарплатой по отделу.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Ошибки при использовании ROWS и RANGE

Неверно указанный порядок строки (ORDER BY): Если не указать порядок сортировки, PostgreSQL выдаст ошибку, так как не сможет определить текущую строку.

Смешивание подходов ROWS и RANGE в одной задаче: Выбирайте подход в зависимости от ваших данных. ROWS подходит для задач с фиксированным числом строк, а RANGE — для диапазонов значений.

Пропуск повторяющихся значений в RANGE: Помните, что RANGE учитывает все повторяющиеся значения, что может значительно поменять результат.