Применение ключевого слова OVER

Ключевое слово OVER в SQL используется для определения области применения оконной функции, т.е. для того, чтобы указать, как именно вычисляется результат функции для каждой строки в пределах окна. Оно позволяет управлять тем, как строки группируются и упорядочиваются для выполнения вычислений оконных функций. Вот как это работает и какие есть возможности.

Синтаксис OVER

Основной синтаксис OVER для оконных функций выглядит следующим образом:

function_name(expression) OVER (
    [PARTITION BY partition_column]
    [ORDER BY order_column]
    [ROWS frame_specification]
)

• function_name(expression): Название оконной функции и выражение, к которому применяется функция.

• PARTITION BY partition_column: Определяет разделение данных на группы (окна), для которых будет применяться функция.

• ORDER BY order_column: Определяет порядок строк в каждом окне.

• ROWS frame_specification: Определяет размер окна относительно текущей строки (опционально).

Примеры использования OVER

Простое использование оконных функций

Пример с ROW_NUMBER()

SELECT employee_id, name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

• В этом примере ROW_NUMBER() нумерует строки в соответствии с порядком зарплат в убывающем порядке, без разделения на группы (PARTITION BY не используется).

Использование PARTITION BY для разделения на группы

Пример с RANK()

SELECT employee_id, name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

• Здесь RANK() присваивает ранги сотрудникам внутри каждого отдела (PARTITION BY department), упорядочивая их по зарплате.

Использование ORDER BY для определения порядка

Пример с SUM()

SELECT employee_id, name, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;

• В этом примере SUM() вычисляет кумулятивную сумму зарплат в пределах каждого отдела, упорядочивая строки по зарплате.

Определение размера окна с ROWS

Пример с AVG()

SELECT employee_id, name, salary,
       AVG(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

• Здесь AVG() вычисляет скользящее среднее зарплат, включая текущую строку и две предыдущие строки в пределах каждого отдела.

Дополнительные примеры и опции

Определение рамки окна

Оператор ROWS позволяет задавать размер окна относительно текущей строки. Основные параметры включают:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Все строки от начала окна до текущей строки.

• ROWS BETWEEN N PRECEDING AND CURRENT ROW: N строк перед текущей строкой до текущей строки.

• ROWS BETWEEN CURRENT ROW AND N FOLLOWING: Текущая строка и N строк после нее.

Пример с ROW_NUMBER() и ROWS BETWEEN

SELECT employee_id, name, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS row_num
FROM employees;

• В этом примере ROW_NUMBER() будет вычислен в рамках окна, включающего одну предыдущую строку и текущую строку в пределах каждого отдела.

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

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

Пример с RANGE

SELECT employee_id, name, salary,
       AVG(salary) OVER (PARTITION BY department ORDER BY salary RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

• Здесь AVG() вычисляет скользящее среднее зарплат, учитывая зарплаты за последние 30 дней до текущей строки в пределах каждого отдела.

Заключение

Ключевое слово OVER в SQL позволяет гибко управлять областью применения оконных функций, задавая порядок, разделение на группы и размер окна. Оно делает оконные функции мощным инструментом для выполнения сложной аналитики, вычисления рангов, сумм, скользящих средних и других вычислений, которые требуют учета контекста относительно других строк.