Оконные функции — это функции, которые работают с выделенным набором строк, окном или партицией, и выполняют вычисления для набора строк в отдельном столбце.
Пока абстрагируемся от оконных функций и представим себе таблицу с продажами. У нас есть продажи по дням, по чекам, по суммам — именно так мы привыкли видеть их в нашей базе данных. Но таблица может храниться не на одном сервере, а разделена на партиции. *Партиция** — это объект СУБД, доля таблицы. Когда мы говорим, что эта таблица партиционированная, мы имеем в виду, что она физически на серверах расположена на разных дисках, на разных серверах.
Чем это удобно? Представим, что таблица sales партиционированная, а партиция накладывается на поле или набор полей — допустим, по sale_date. Иначе говоря, что в одной партиции продажи за январь, в другой - за февраль и так далее. То есть мы бы искали продажу по указанной дате не во всей таблице, а только в конкретной ее партиции. Так партиции ускоряют операции с данными.
Соответственно, в оконных функциях это работает точно так же, только это деление не физическое, а логическое. То есть мы указываем в запросе какую партицию сделать. При этом мы не разрываем таблицу как объект, как это делает обычная партиция. Потому они и называются окном, потому что именно в этом разрезе мы будем искать в дальнейшем наши значения.
Итак, оконная функция нужна, чтобы посчитать какие-то значения для конкретной строки в разрезе каких-то условий.
Оконные функции работают на саму строку. На каждую строку применяется вся логика оконных функций. Синтаксис оконных функций выглядит так:
SELECT ProductId, ProductName, CategoryId, Price,
SUM(Price) OVER (PARTITION BY CategoryId) AS [SUM],
AVG(Price) OVER (PARTITION BY CategoryId) AS [AVG],
COUNT(Price) OVER (PARTITION BY CategoryId) AS [COUNT],
MIN(Price) OVER (PARTITION BY CategoryId) AS [MIN],
MAX(Price) OVER (PARTITION BY CategoryId) AS [MAX]
FROM TestTable
В запросе выше составляется окно по CategoryId. Для каждой строки в этом окне считаются сумма, среднее, количество, минимальное и максимальное значение цены. То есть для каждой строки в разрезе категории мы считаем эти пять значений.
Синтаксис оконных функций
В оконных функциях есть три параметра. Одно ключевое слово — OVER(), то есть партиции в оконной функции начинают работать со слова OVER(). Если мы не написали OVER(), то у нас просто агрегирующая функция SUM(Price), AVG(Price) и так далее.
После слова OVER() нам нужно указать параметры окна: PARTITION BY — по какой партиции искать и ORDER BY — в каком направлении сортировать или искать.
Что делает оконная функция? По сути, она ищет. То есть мы ищем цену в разрезе категории для каждого продукта.
Мы поставили агрегирующую функцию, слово OVER() и написали еще два ключевых оператора, отвечающие за разрез и направление. Направление работает так же как ORDER BY в конце запроса - в двух направлениях по убыванию и по возрастанию и имеет те же расширения, что обычный ORDER BY — IGNORE NULLS, NULLS FIRST, NULLS LAST и так далее.
PARTITION BY имеет просто значение тех полей, которые нам нужны, но они также могут быть и вычисляемыми.
Расширения оконных функций
У оконных функций, как у остальных, есть свои расширения:
- Шаги окна (для поиска на несколько шагов назад и вперед)
- Дефолтные значения (заглушки для
NULL) - Игнорирование
NULLпо аналогии сORDER BY - «Форточки»
В курсе мы будем часто использовать шаги окна для поиска на несколько шагов вперед или назад - LEAD и LAG. То есть мы можем сказать, на какое число шагов это смещение произвести.
Оконные функции хорошо работают с дефолтными значениями. В них автоматически встроена обработка значений NULL. Это могут быть как дефолтные значения и это может быть игнорирование значений NULL.
Также существуют форточки. Это немного другой запуск оконной функции. Например, мы указываем не «Посчитай или просуммируй конверсию», а «От текущей строки смотри все наверх». Про них отдельно поговорим позже.
Применение оконных функций
Ну и самое важное, для чего мы это все проходим — для чего же оконные функции существуют. Есть шесть применений.
Вычисления над набором строк с единым признаком
Допустим, нам нужно посчитать среднюю цену продажи конкретного товара. Она может различаться от чека к чеку (например, у покупателя есть скидка на этот продукт). Мы можем применить оконную функцию, где в качестве окна мы будем использовать product_id и product_name товара.
Отличие от группировки — расчет для каждой строки
В случае группировки мы не могли бы сравнивать без применения подзапроса.
Допустим, нам нужно вывести все чеки, в которых цена товара была выше средней. То есть кто-то купил за 1, кто — за 2, кто-то — за три, в итоге средняя цена — 2. Если делать группировку, AVG(), то мы считали бы отдельно и сравнивали с этим.
В случае с оконными функциями мы от этого всего избавлены, потому что агрегатная функция будут применена к каждому окну. Для каждой строки, где этот товар есть, мы как раз считаем его среднюю цену и выводим отдельным столбцом. То есть у нас получится дополнительный столбец, в котором все значения среднему значению. И мы можем вот эти два столбца просто друг с другом сравнить и вывести только те, которые нам подходят по условию.
Построение аналитических отчетов
Оконные функции помогают нам строить аналитический отчет. Например, мы можем показать не все чеки, а только чеки больше среднего — и таких образом решить более сложную задачу.
Например, мы можем сделать анализ по двум показателям - отдельно партиционировали клиентов и отдельно товар. И в их разрезе выводим только те значения, которые там больше среднего или меньше среднего, в зависимости от задач.
Расчеты скользящих средних
С помощью оконных функций можно считать скользящие средние. Что такое скользящие средние? Это средняя N предыдущих значений. Скользящие средние считаются с помощью LEAD и LAG, у которых мы чуть попозже поговорим.
Расчеты нарастающих итогов и процентилей
Расчеты нарастающих применяются для визуализации, когда нам нужно построить динамический график изменения или pie chart. Для круговых диаграмм очень важны процентили — то есть какую долю какой продукт занимает.
Ранжирование данных
Последнее применение оконных функций - ранжирование данных. Ранжирование данных позволяет находить дублированные записи или совершать статистические расчеты для нечисловых данных.
Для полного доступа к курсу нужен базовый план
Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.
