Оконные функции SQL

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

Принцип работы

Возможно, вы зададитесь вопросом: «Что значит оконные?».

В стандартном SQL-запросе все наборы строк рассматриваются как один сплошной блок данных, для которого и вычисляются агрегатные значения.

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

Это окно, которое подаётся в оконную функцию, может быть:

• всей таблицей
• отдельными партициями таблицы, то есть группой строк на основе одного или нескольких полей
• или даже конкретным диапазоном строк в пределах таблицы или партиции. Например, мы можем определить окно, которое будет передаваться в оконную функцию, как предыдущая + текущая строка таблицы. И тогда для каждой строки значение агрегатной функции будет подсчитываться по-своему, так как данные, которые поступают в функцию будут динамически меняться от строке к строке. Окно будет как бы «скользить» по таблице.

Визуализация

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

Давайте рассмотрим как это может выглядеть. Для этого возьмём оконную функцию AVG для вычисления среднего значения и вот такую небольшую таблицу:

А теперь давайте посмотрим как оконная функция будет работать для разных переданных окон:

• Если в качестве окна указать всю таблицу, то для всех строк окно будет совпадать и на вход функции AVG будет поступать один и тот же набор данных, и, соответственно, результат будет одинаковый.

  

• Если в качестве окна указать партицию по полю home_type, то на вход функции AVG будет поступать набор жилых помещений с одинаковым типом, и, соответственно, в результате в новой колонке будет отображаться средняя стоимость по жилью, чей тип совпадает с типом у текущей строки таблицы.

  

• В качестве окна можно указать и более специфический набор строк. Например, окно можно определить как "предыдущая + текущая строка" таблицы. Тогда это будет выглядеть следующим образом:

  

  Стоит отметить, что для первой строки окно будет состоять только из 1-ой записи, так как предыдущей строки нет.

Синтаксис оконной функции

MySQL
SELECT <оконная_функция>(<поле_таблицы>)
OVER (
      [PARTITION BY <столбцы_для_разделения>]
      [ORDER BY <столбцы_для_сортировки>]
      [ROWS|RANGE <определение_диапазона_строк>]
)

Где:

• <оконная_функция>(<поле_таблицы>) — используемая оконная функция. Например AVG(price).
• Далее следует OVER, который определяет окно (группу строк), которое будет передаваться в оконную функцию. Если конструкцию OVER () оставить без параметров, то окном будет выступать вся таблица.

Далее внутри OVER следуют 3 необязательных параметра, с помощью которых можно гибко настраивать окно:

• с помощью PARTITION BY <столбцы_для_разделения> выборка делится на непересекающиеся подмножества, где каждое подмножество содержит строки с одинаковыми значениями в одном или нескольких столбцах, образуются партиции.
• с помощью ORDER BY <столбцы_для_сортировки> устанавливается порядок строк внутри окна, особо важную роль играет в оконных функциях ранжирования.
• с помощью ROWS|RANGE <определение_диапазона_строк> формируются диапазоны строк. С помощью этого параметра можно указать сколько строк брать до и после текущей в окно.

На каждом из этих параметров мы подробнее остановимся в следующих статьях.

Пример использования оконной функции

Давайте с помощью оконных функций попробуем получить список имён студентов и то, как много одноклассников у каждого их них.

Для начала давайте получим список студентов и идентификатор класса, в котором они учатся:

MySQL
SELECT
    Student.first_name,
    Student.last_name,
    Student_in_class.class
FROM
    Student_in_class
JOIN
    Student ON Student_in_class.student = Student.id;

А теперь, чтобы вычислить сколько учащихся учится в каждом из классов и вывести эту информацию в новую колонку, мы можем применить оконную функцию:

MySQL
SELECT
    Student.first_name,
    Student.last_name,
    Student_in_class.class,
    COUNT(*) OVER (PARTITION BY Student_in_class.class) AS student_count_in_class
FROM
    Student_in_class
JOIN
    Student ON Student_in_class.student = Student.id;

Что делает наша оконная функция

Выражение PARTITION BY Student_in_class.class разделяет все строки таблицы на партиции по полю class. Так, для каждой из строк в оконную функцию будут подаваться только те строки таблицы, где поле class совпадает с полем class в текущей строке.

Функция COUNT же возвращает количество переданных в неё строк, тем самым мы и получаем сколько учащихся учится в каждом из классов.

Порядок выполнения оконных функций в SELECT

При использовании оконных функций важно понимать в какой последовательности они будут исполняться. Так, как мы можем увидеть на схеме ниже, окна отрабатывают предпоследним шагом, уже после фильтрации и группировки, но перед финальной сортировкой результатов выборки.

Заключение

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

И на последок давайте проверим все ли мы поняли:

Какое ключевое отличие между оконными функциями и агрегатными функциями с группировкой в SQL?