Разбираем магию оконных функций (на примере PostgreSQL)

Всем привет! Рассмотрим очень полезный и невероятно интересный функционал реляционных БД – оконные функции.

Примеры работают в PostgreSQL, однако мы основное внимание уделим логике работы, которая заложена в сам принцип работы оконных функций и применяется в других SQL-диалектах – поэтому вы без труда сможете применять полученные знания практически в любой БД, делая поправку на синтаксис используемого диалекта. Также отметим — так как это вводная статья, мы решили ограничиться описанием базовых оконных функций, которые, вероятно, покроют 90% задач, в которых эти функции необходимы. Во второй статье углубимся в код, рассмотрим оконные функции с фреймами, а также познакомимся с другими оконными конструкциями, нередко помогающими в работе аналитику.

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

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

Здесь мы выделили атрибуты, относящиеся к источнику данных (первоначальной таблице, блок «Исходная таблица»), а также атрибуты, которые рассчитываются с помощью базовых оконных функций (блок «Оконные функции»). Мы умышленно в каждое последующее окно поместили на один элемент больше, чтобы можно было невооруженным взглядом понять суть оконной функции, то, как изменяется ее значение. Зависимости показали красными линиями – то есть на результат оконной функции sum() влияет только атрибут «PRICE», а оконные конструкции count() и row_number() используют количество строк (для примера мы сослались на атрибут «ID»).

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

Оконная конструкция SUM()

Сразу пишем код, потом разбираем, что делает каждый символ:

Результат:

Обратите внимание, что после применения оконной функции количество строк не изменилось, однако в столбце «SUM()» появился агрегат. Поэтому внимательнее пишите код дальше, чтобы случайно не построить агрегат на атрибуте-результате оконной функции. То есть конструкция

скорее всего, ошибочна, так как здесь «агрегат-на-агрегате».

Возвращаемся к разбору функции SUM(). Конструкция over (partition by TYPE) в данном случае формирует окно/partition, в которое помещает типы устройств, и в них считает sum(PRICE), то есть цену всех устройств. Обратите внимание, что если в атрибуте TYPE у вас есть, например, значения «Ноутбук» и «Ноутбукъ», то корректного окна у вас не получится. Точнее у вас будут 2 окна с ноутбуками. Поэтому сначала рекомендуем посмотреть на группы товаров, например, с использованием такой проверки:

Select TYPE, count(*) from orders group by TYPE

Результат может неприятно вас удивить, и вместо:

Вы получите

С этим разобрались – идем дальше.

Оконная конструкция COUNT()

Функция count() в классическом понимании считает количество строк таблицы. В данном случае она считает количество строк в окне, или количество устройств (product, строк таблицы) в каждой группе (TYPE):

Оконная конструкция ROW_number()

Функция нумерует строки (устройств, product) в окне (TYPE). Однако, когда мы нумеруем строки в таблице — нам необходимо определить порядок подсчета, то есть какой продукт будет номером 1, какой – номером 2 и т.д. Как это выглядит в коде:

Здесь мы добавили сортировку через классическую конструкцию order by, указав, какой атрибут будем сортировать (product), а также направление сортировки (asc — по возрастанию, или desc – по убыванию). Если не указывать направление сортировки – данные будут отсортированы по умолчанию – asc:

На этом все – мы разобрали базовые оконные конструкции, которые считают агрегаты внутри групп (окон). Чтобы закрепить знания – предлагаем вам самостоятельно посчитать максимальную и минимальную стоимость (PRICE) устройства (product) в рамках групп устройств (TYPE) – это две оконные функции, которые мы здесь не рассматривали.

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