Синтаксис OVER() и его ключевые особенности

OVER() — это инструкция, которая говорит SQL, над каким набором строк нужно применить оконную функцию. Можно сказать, это способ, который определяет "окно" данных для применения оконной функции. Представьте, что у нас есть комната, полная людей, и мы хотим подсчитать, сколько людей стоят на каждом квадратном метре пола. OVER() укажет, в какой именно части комнаты мы сосредоточим свои усилия. Другими словами, он задаёт, над каким набором строк будет работать функция.

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

Синтаксис:

оконная_функция() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Где:

• PARTITION BY — разбивает набор данных на логические группы
• ORDER BY — задает порядок строк внутри каждой группы
• ROWS/RANGE — уточняет размер "окна" (например, текущая строка + 1 следующая)

Пример: OVER() без параметров

Когда OVER() используется без дополнительных параметров, это означает, что функция, указанная перед ним, будет работать по всему набору данных.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() будет применена ко всем строкам результата
FROM employees;

Что происходит?

1. ROW_NUMBER() назначает уникальный номер каждой строке.
2. Поскольку в OVER() не указано никаких параметров, все строки из таблицы employees обрабатываются как единое целое.

Результат:

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

Отлично, теперь представьте, что нас интересует нумерация сотрудников не по всей компании, а внутри каждого отдела. Здесь на арену выходит PARTITION BY.

PARTITION BY внутри OVER() разбивает данные на группы (или "разделы"). Для каждой группы функция вычисляет значение отдельно. То есть, если бы ROW_NUMBER() был официантом, он бы начинал нумерацию заново для каждого "стола" (раздела).

Пример: используем PARTITION BY

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id) AS row_num
FROM employees;

Что происходит?

1. Данные из таблицы employees разделены на группы по значению department_id.
2. В каждой группе строкам назначается порядковый номер через ROW_NUMBER().

Результат:

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

Теперь давайте добавим немного структуры. Представьте, что нам нужно не просто нумеровать строки, а делать это в определённом порядке, например, начиная с самой высокой зарплаты. Эта задача решается с помощью ORDER BY.

ORDER BY определяет порядок, в котором строки будут обрабатываться оконной функцией.

Пример: используем ORDER BY внутри OVER()

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

Что происходит?

1. Данные разделяются на группы (PARTITION BY department_id).
2. Внутри каждой группы строки сортируются по убыванию зарплаты (ORDER BY salary DESC).
3. Каждой строке присваивается ранг с учётом сортировки.

Результат:

Комбинирование оконных функций

SQL позволяет использовать несколько оконных функций в одном запросе, причём каждая из них может работать по своему уникальному набору правил. Это всё равно что в одной комнате одновременно играет музыка и ведётся подсчёт людей — каждый процесс независим!

Пример: несколько оконных функций

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

Что происходит?

1. ROW_NUMBER() нумерует строки в каждой группе по убыванию зарплаты.
2. AVG() вычисляет среднюю зарплату в каждой группе.

Результат:

Примеры из реальной жизни

Оконные функции с OVER() используются во множестве реальных сценариев. Вот лишь несколько примеров:

• Аналитика продаж: ранжирование товаров по количеству продаж в каждой категории.
• Рейтинги: определение позиций студентов в каждой группе по среднему баллу.
• Временные ряды: накопительная сумма продаж в разрезе времени.

Пример из аналитики продаж:

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

Распространённые ошибки при работе с оконными функциями

1. Отсутствие PARTITION BY

Если вы не используете PARTITION BY, оконная функция применяется ко всей таблице. Это может привести к неожиданным результатам, особенно если вы ожидали разбивку по группам.

2. Некорректные типы данных в ORDER BY

ORDER BY внутри оконной функции чувствителен к типам данных. Если вы сортируете по полю с датой, сохранённой как текст (VARCHAR), порядок может оказаться алфавитным, а не хронологическим.

3. Неправильное использование ROWS BETWEEN

По умолчанию оконные функции работают с рамками, определяемыми ROWS BETWEEN. Если вы явно не укажете рамку, может применяться поведение RANGE, которое ведёт себя по-другому и может возвращать больше строк, чем вы ожидали.

4. Неправильная работа с NULL

Оконные функции могут по-разному обрабатывать NULL. Например, RANK() и DENSE_RANK() будут считать NULL как значение и присвоят ему отдельный ранг.

5. Применение агрегатных оконных функций вместо обычных

Иногда используют оконные агрегатные функции (SUM() OVER(...)) там, где достаточно обычных агрегатов с GROUP BY, что усложняет запрос и замедляет выполнение.

2

Задача

SQL SELF, 29 уровень, 2 лекция

Недоступна

Нумерация строк в таблице

Нумерация строк в таблице

Открыть