SQL-Ex blog

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

Документация PostgreSQL дает хорошее введение в эту концепцию:

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

Сравнение оконных и агрегатных функций

Агрегатные функции работают с набором значений и возвращают единственное скалярное значение. Некоторые примеры агрегатных функций SQL:

• AVG() - возвращает среднее значений указанного столбца.


• SUM() - возвращает сумму всех значений.


• MAX(), MIN() - возвращают максимальное и минимальное значения.


• COUNT() - возвращает общее число значений

Агрегатные функции используются с предложением GROUP BY, которое вычисляет агрегатное значение для нескольких групп в одном запросе. Давайте поясним это на примере, использующем данные транзакций продаж для San Francisco и New York.



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

SELECT date, city, AVG(amount) AS avg_transaction_amount_for_city

FROM transactions

GROUP BY date, city;

Результат этого запроса показан ниже.



Агрегатная функция AVG() и GROUP BY дают нам среднее значение, сгруппированное по дате и городу. Если посмотреть на строки за 2-е ноября, то мы имеем две транзакции в New York, а 3-го ноября - две транзакции в San Francisco. В результирующем наборе отдельные строки свернуты в единственную строку, представляющую агрегатные значения для каждой группы.

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

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

SELECT id, date, city, amount,  

AVG(amount) OVER (PARTITION BY date, city) AS  avg_daily_transaction_amount_for_city

FROM transactions

ORDER BY id;

Результат:


Обратите внимание, что строки не сворачиваются. Присутствует по одной строке на каждую транзакцию и вычисленные средние значения в avg_daily_transaction_amount_for_city.

На диаграмме ниже показана разница между агрегатными и оконными функциями.

Сходство и различие между оконными и агрегатными функциями

Характерно как для оконных, так и агрегатных функций:

• Работают с множеством строк


• Вычисляют агрегатные величины


• Группируют или секционируют данные по одному или нескольким столбцам

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

• Использование GROUP BY для определения множества строк для агрегации


• Группировка строк на основе значений столбца


• Сворачивание строк в единственную строку для каждой определенной группы

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

• Использование OVER() вместо GROUP BY для определения множества строк


• Использование большего числа функций в дополнение к агрегатным, например: RANK(), LAG(), LEAD()


• Могут группировать строки по их рангу, процентилю и т.д. в дополнение к значениям столбца


• Не сворачивают строки в единственную строку на группу


• Могут использовать скользящую рамку окна на основе текущей строки

Зачем использовать оконные функции?

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

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

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



Мы хотим вычислить накопительные итоги транзакций за каждый день в каждом городе. Запрос ниже делает это.

SELECT id, city,

       date,

       SUM(amount) OVER

         (PARTITION BY city ORDER BY date)

         AS running_total

  FROM transactions

Первая часть агрегата выше, SUM(amount), выглядит подобно любой другой агрегации. Добавление OVER означает, что это оконная функция. PARTITION BY сужает окно со всего набора данных до отдельных групп в рамках этого набора данных. Вышеприведенный запрос группирует данные по городу (city) и упорядочивает их по дате (date). Внутри каждой группы города данные упорядочиваются по дате и накопительные итоги суммируются от текущей строки и всех предыдущих строк группы. При изменении значения города можно заметить, что значение накопительных итогов (running_total) начинается заново для этого города. Вот результаты этого запроса:



ORDER BY и PARTITION BY определяют то, что является окном - упорядоченный набор данных над которым выполняются вычисления.

Типы оконных функций

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

• Агрегатные функции: Эти функции вычисляют единственное значение для множества строк
  
  • SUM(), MAX(), MIN(), AVG(), COUNT()


• Ранжирующие функции: Эти функции присваивают ранг каждой строке в разбиении
  • RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()


• Аналитические функции: Эти функции вычисляют значения на базе движущегося окна строк
  • LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()


• Функции смещения: Эти функции позволяют пользователю извлекать значения из другой строки в секции
  • FIRST_VALUE() and LAST_VALUE().

Еще примеры использования оконных функций

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

Рассмотрим несколько примеров. Таблица ниже, которая называется train_schedule содержит train_id, станцию (station) и время (time) прибытия поездов в районе залива Сан-Франциско. Нам нужно вычислить время до следующей станции для каждого поезда в расписании.



Это можно вычислить как разность времен прибытия для каждой пары соседних станций для каждого поезда. Вычисление без использования оконных функций может оказаться более сложным. Большинство разработчиков считывали бы таблицу в память и использовали логику приложения для вычисления значений. Оконная функция LEAD здорово упрощает эту задачу.

SELECT

    train_id,

    station,

    time as "station_time",

    lead(time) OVER (PARTITION BY train_id ORDER BY time) - time

        AS time_to_next_station

FROM train_schedule

ORDER BY 1 , 3;

Мы создаем наше окно СЕКЦИОНИРОВАНИЕМ по train_id и сортируя секцию по time (времени прибытия на станцию). Оконная функция LEAD() получает значение столбца из следующей строки в окне. Мы вычисляем время до следующей станции вычитанием из времени, полученного посредством оконной функции LEAD, времени из столбца time текущей строки. Результаты показаны ниже.



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

SELECT     

    train_id,

    station,

    time as "station_time",

    time - min(time) OVER (PARTITION BY train_id ORDER BY time)     

                                AS elapsed_travel_time,

    lead(time) OVER (PARTITION BY train_id ORDER BY time) - time

                                AS time_to_next_station

FROM train_schedule;

Наше окно не изменилось. Мы по-прежнему выполняем разбиение по train_id и упорядочиваем окно по времени прибытия на станцию. Изменились только вычисления, которые мы выполняем для каждой строки в окне. В запросе выше мы вычитаем из времени текущей строки самое раннее время в окне, которое имеет место, когда поезд отходит от первой станции. Это дает нам время поездки для каждой станции по ходу поезда.

Вот результат:



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

Ссылки по теме
1. Накопительные итоги

2. Оконные функции T-SQL и производительность

3. Функция LAG в SQL Server

4. Пересмотр производительности оконных агрегатов в SQL Server 2019

5. Оконные функции или GROUP BY?

6. Индексирование и оконные функции