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

Что такое оконная функция?

Оконные функции были впервые введены в стандартный SQL в 2003 году. Согласно документации PostgresSQL:

“Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой…За кулисами функция window может получить доступ не только к текущей строке результата запроса.”

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

Обратите внимание, как ГРУППИРОВКА ПО агрегации в левой части рисунка группирует три строки в одну единственную строку. Функция window в правой части рисунка способна выводить для каждой строки значение агрегации. Это может избавить вас от необходимости выполнять присоединение после создания группы BY.

GROUP BY по сравнению с оконной функцией

Вот краткий пример, который даст вам представление о том, что делает оконная функция.

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

Слева - то, что вернула бы GROUP BY aggregation, а справа - то, что вернула бы функция window. Как вы можете видеть, group by объединяет наши данные всего в три строки. С помощью оконной функции мы сохраняем исходные 11 строк и получаем новый столбец с именем AVG_SALARY. Затем мы могли бы при желании сравнить заработную плату каждого отдельного человека со средней зарплатой.

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

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

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

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

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

Важное примечание:

Прежде чем мы начнем, важно отметить, что с точки зрения порядка операций в SQL оконные функции занимают шестое место в списке. Это важно, потому что, исходя из этого логического порядка, оконные функции разрешены в SELECT и ORDER BY, но они не разрешены в предложениях FROM, WHERE, GROUP BY или HAVING.

Примечание: Если вам действительно нужно, чтобы это было внутри предложения WHERE или GROUP BY, вы можете обойти это ограничение, используя подзапрос или запрос WITH.

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

Вот как выглядит общий синтаксис для оконной функции в предложении SELECT.

Здесь много слов, поэтому давайте рассмотрим некоторые определения:

window_function - это имя оконной функции, которую мы хотим использовать; например, sum, avg или row_number (мы узнаем больше о них позже)

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

OVER - это просто для обозначения того, что это оконная функция

PARTITION BY делит строки на разделы, чтобы мы могли указать, какие строки использовать для вычисления функции window

partition_list - это имя столбца(ов), который(которые) мы хотим разбить на разделы

ORDER BY используется для того, чтобы мы могли упорядочить строки внутри каждого раздела. Это необязательно и не обязательно указывать

order_list - это имя столбца (столбцов), по которому мы хотим упорядочить

СТРОКИ можно использовать, если мы хотим еще больше ограничить количество строк в нашем разделе. Это необязательно и обычно не используется

, frame_clause определяет, на сколько нужно отклониться от нашей текущей строки

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

Краткий пример

Чтобы помочь вам лучше понять, как на самом деле работает синтаксис, ниже приведен пример того, как оконная функция выглядела бы на практике.

Это запрос, который сгенерировал бы результат, который мы видели ранее, касающийся заработной платы в разбивке по названию должности.

Здесь AVG() - это имя оконной функции, ЗАРПЛАТА - это выражение, а JOB_TITLE - наш список разделов. Мы не использовали ORDER BY, поскольку в нем нет необходимости, и мы не хотим использовать строки, потому что не хотим еще больше ограничивать наш раздел.

Опять же, пока нет необходимости запоминать синтаксис. На данном этапе я хочу, чтобы вы поняли одну концепцию, заключающуюся в том, что функция window вычисляет значение для каждой строки в “окне” или “разделе”. Окно может быть одной из нескольких строк, и оно задается предложением PARTITION BY. В нашем примере мы разделили данные по названию должности. Как вы можете видеть во фрагменте выше, я выделил каждое название должности другим цветом. Каждый цвет представляет собой другое “окно” или другую “перегородку”. Функция window вычисляет одно среднее значение заработной платы для каждого раздела.

Список оконных функций

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

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

Вот краткий обзор того, для чего полезен каждый тип оконной функции.

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

Функции ранжирования: эти функции полезны для ранжирования строк внутри его раздела.

Функции значений: эти функции позволяют сравнивать значения из предыдущих или следующих строк в разделе или первое или последнее значение в разделе.

Вывод

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