Урок №1. Базовые оконные функции

Введение

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

Таблица orders

Таблица orders

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

Основная часть

Чем оконные функции отличаются от агрегатных?

Главное различие — фильтрация исходного набора строк.

Агрегатные функции (GROUP BY): берут строки, объединяют их в группы и возвращают одно значение на группу, происходит фильтрация.

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

Код запроса для нашей задачи, который использует оконную функцию SUM по клиенту:

SQL 
1 SELECT *, SUM(amount) OVER(PARTITION BY customer_id) FROM orders

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

Что за синтаксис OVER() и PARTITION BY?

OVER() — таким синтаксисом определяется «окно», в рамках которого происходит расчёт. В скобках указываются его границы. Если скобки пустые, то вычисление происходит по всем данным запроса.

PARTITION BY — аналог GROUP BY, но в «окне». В примере для нашей задачи выше используется группировка по customer_id. Для каждой группы в окне значение рассчитывается единожды, но для каждой строки значение сохраняется в выгрузке. Поле для группировки может отсутствовать в списке полей для выгрузки, но тогда результат запроса труднее читать, обычно так не делают.

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

Заключение

Мы познакомились с фундаментальной концепцией оконных функций. Оконные функции позволяют совмещать детальные данные с вычислениями по группам, не теряя строк. Конструкция OVER(PARTITION BY) — ваш главный инструмент и ключ к повышению эффективности при работе с данными.

Примеры и практика

Вопрос 1. В чем ключевое отличие оконной функции SUM(amount) OVER(PARTITION BY customer_id) от обычной агрегатной SUM(amount) GROUP BY customer_id?

  • A. Оконная функция возвращает одно значение на категорию, агрегатная — на каждую строку.
  • B. Оконная функция возвращает значение для каждой строки, агрегатная схлопывает строки до одной на категорию.
  • C. Никакой разницы нет, это одно и то же.

Вопрос 2. Напишите запрос к таблице orders из урока с выводом всех строк таблицы, который вдобавок выведет средние значения суммы заказа клиента в отдельном столбце.

SQL 
1 SELECT *, AVG(amount) OVER(PARTITION BY customer_id) FROM orders