Это особенно полезно, когда вам нужно вычислить агрегирование или ранжирование групп строк без фактического свертывания всего набора результатов.
Присоединяйтесь к нам в этом уроке, и мы узнаем все, что нужно знать, чтобы начать работать с предложением OVER.
Требования:
Прежде чем мы углубимся в функциональность и работу предложения OVER, убедитесь, что вы знакомы с основами SQL. Мы также предполагаем, что у вас есть доступ к базе данных, которую вы можете использовать для проверки своих знаний.
В нашем случае мы будем использовать базу данных MySQL с образцом базы данных Sakila. Просто убедитесь, что у вас есть достаточные разрешения и что ваша база данных поддерживает оконные функции.
Синтаксис:
Как мы упоминали ранее, в большинстве случаев мы в основном используем предложение OVER вместе с оконными функциями.
Таким образом, мы можем выразить синтаксис предложения следующим образом:
<оконная функция>(выражение) OVER ([РАЗДЕЛ ПО выражению_раздела, ...]
[ORDER BY sort_expression [ASC | DESC], ...]
[спецификация_фрейма]
)
В данном синтаксисе мы можем разбить каждый компонент следующим образом:
-
— относится к оконной функции, которую мы хотим применить к определенному окну строк, например SUM(), AVG(), ROW_NUMBER(), RANK и т. д. - Выражение — указывает столбец или выражение, к которому применяется оконная функция.
- PARTITION BY – это необязательное предложение, которое делит результирующий набор на разделы, где каждый раздел представляет собой отдельный блок, к которому применяется функция. Строки в одном разделе имеют одинаковые значения в указанных столбцах.
- ORDER BY – определяет порядок обработки строк в каждом разделе.
- Спецификация_фрейма — это необязательное предложение, определяющее рамку строк внутри раздела. Общие спецификации кадра включают ROWS BETWEEN
AND или RANGE BETWEEN AND
Разобравшись с этим, давайте рассмотрим несколько практических примеров его использования.
Пример:
Давайте продемонстрируем, как использовать это предложение, используя образец базы данных Sakila. Рассмотрим пример, где нам нужно определить общий доход по каждой категории фильмов.
Мы можем использовать функцию окна суммы с предложением OVER и набором операторов соединения, как показано в следующем примере:
ВЫБИРАТЬкатегория.имя КАК имя_категории,
фильм.название КАК название_фильма,
фильм.прокат_ставка,
СУММА(платеж.сумма) БОЛЬШЕ (РАЗДЕЛЕНИЕ ПО категории.название) КАК общий_доход
ОТ
фильм
ПРИСОЕДИНИТЬСЯ
фильм_категория ВКЛ.
Film.film_id = Film_category.film_id
ПРИСОЕДИНИТЬСЯ
категория ВКЛ.
Film_category.category_id = Category.category_id
ПРИСОЕДИНИТЬСЯ
инвентарь включен
Film.film_id = Inventory.film_id
ПРИСОЕДИНИТЬСЯ
аренда ПО
инвентарь.inventory_id = rent.inventory_id
ПРИСОЕДИНИТЬСЯ
оплата включена
rent.rental_id = Payment.rental_id
СОРТИРОВАТЬ ПО
категория.имя,
фильм.название;
В данном запросе мы начинаем с выбора названия фильма, ставки проката и используем выражение суммы (платеж.сумма по разделу по категории.имя), чтобы определить сумму каждого раздела категории по имени категории.
Мы должны использовать предложение PARTITION BY, чтобы гарантировать перезапуск расчета суммы для каждой уникальной категории.
Полученный результат выглядит следующим образом:
Вот оно!
Заключение
В этом примере мы рассмотрели основы работы с предложением OVER в SQL. Это не базовое предложение, требующее предварительного знакомства с другими функциями SQL.