Расширяемость
Агрегатные и оконные функции
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Создание пользовательских агрегатных функций
Механизм работы оконных функций и их создание
Параллельное выполнение агрегатных функций
3
Агрегатные функции
Построчная обработка агрегируемой выборки
состояние
функции перехода и финализации
состо-
яние
начальное
состояние
функция
финализации
функция перехода
( текущее значение )
В PostgreSQL имеется достаточно много встроенных агрегатных
функций. Часть из них определена стандартом (такие, как min, max,
sum, count и т. п.), часть является расширением стандарта.
Но иногда может оказаться полезным создать собственную агрегатную
функцию. Такая функция работает очень просто.
Имеется некоторое состояние, представленное значением какого-либо
типа данных, которое инициализируется определенным значением
(например, тип numeric и начальное значение 0).
Для каждой строки агрегируемой выборки вызывается функция
перехода, которая получает значение из текущей строки и должна
обновить состояние (например, прибавляет текущее значение
к состоянию).
В конце вызывается функция финализации, которая преобразует
полученное в результате работы состояние в результат агрегатной
функции (в нашем примере достаточно просто вернуть число — в итоге
получается аналог функции sum).
5
Оконные функции
Окно определяет рамку (агрегируемую выборку)
для каждой строки
OVER () OVER (PARTITION BY)
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
Оконные функции работают подобно агрегатным, вычисляя значение
на основе некоторой выборки. Эта выборка называется рамкой.
Но, в отличие от агрегатных функций, строки не сворачиваются в одну
общую, а вместо этого значение вычисляется для каждой строки
выборки.
Окно задается в предложении OVER после имени функции.
Если окно указано как OVER(), то оконная функция вычисляется
на основе всех строк — одинаково для каждой строки выборки.
Если определение окна включает фразу PARTITION BY, то оконная
функция вычисляется на основе групп строк (аналогично группировке
GROUP BY). В этом случае для всех строк одной группы будет
получено одинаковое значение функции.
7
Нарастающий итог
«Голова» рамки может двигаться
OVER (ORDER BY) OVER (PARTITION BY ORDER BY)
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
1 2 3 4 5 1 2 3 4 5
Как только в определении окна мы указываем предложение ORDER BY,
упорядочивающее строки выборки, предполагается, что рамка окна
охватывает строки от самой первой до текущей. Это можно записать
и явным образом: ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW.
Например, если в качестве оконной функции используется sum, мы
получаем сумму «нарастающим итогом».
Конечно, для каждой группы строк, определяемых предложением
PARTITION BY, рамка будет располагаться в пределах этой группы.
9
Скользящая рамка
Могут двигаться «голова» и «хвост» рамки одновременно
OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
значение
надо убрать
из состояния
Рамку можно указать явно в предложении ROWS BETWEEN. В том
числе двигаться может не только «голова» рамки, но и ее «хвост».
Если в предыдущих примерах рамка только расширялась (в нее
добавлялись все новые значения), то теперь ранее добавленные
значения значения могут «уходить» из рамки.
Чтобы пользовательская агрегатная функция работала эффективно
в таком режиме, нужно реализовать функцию инверсии, которая
устраняет значение из состояния.
11
Параллелизм
Объединение состояний параллельных процессов
состо-
яние
начальное
состояние
функция
финализации
функция перехода
состо-
яние
начальное
состояние
функция перехода
состо-
яние
объединение
состояний
Агрегатные функции могут выполняться в параллельном режиме.
Основной процесс, выполняющий запрос, может создать несколько
фоновых рабочих процессов, каждый из которых будет выполнять
параллельную обработку части данных в узле плана. Затем
полученные результаты передаются в основной процесс, который
собирает их и формирует общий результат.
Чтобы пользовательские агрегатные функции могли работать
параллельно, нужно реализовать функцию объединения двух
состояний в одно общее.
13
Итоги
PostgreSQL позволяет создавать агрегатные и оконные
функции
Агрегатные и оконные функции дают возможность
использовать процедурную обработку в стиле SQL
14
Практика
1. Напишите отчет, выводящий складские остатки по каждой
книге в денежном выражении (используя закупочную, а не
розничную цену). Оформите отчет как фоновое задание.
Учтите, что поступления происходят разными партиями
по разной цене, а продажи никак не привязаны к партиям.
Поэтому считайте, что в первую очередь продаются книги
из более старых партий.
2. Дополните расширение bookfmt функциями min и max для
формата издания. Обновите версию расширения и убедитесь,
что функции появились в базе данных.
1. Начните с запроса к таблице operations, выводящего только
поступления книг, но добавьте столбец, показывающий общее
количество проданных книг данного поступления.
В качестве примера книги, для которой на складе остались экземпляры
из нескольких партий, можно взять книгу с book_id = 15.
Напишите оконную функцию, вычисляющую при использовании
в режиме «нарастающего итога» остаток книг данного поступления на
складе (или решите задачу, используя имеющиеся оконные функции).
2. Расширение bookfmt было написано в практике к теме «Создание
расширений».
15
Практика+
1. Напишите агрегатную функцию, вычисляющую
средневзвешенное значение для совокупности элементов
нескольких типов. Веса для типов элементов передаются
функции в виде массива.
2. Бизнес-центр сдает офисы компаниям. В конце месяца
администрации БЦ приходит общий счет за электроэнергию,
который надо распределить между арендаторами
пропорционально площади занимаемых помещений.
Выставляемые арендаторам счета необходимо округлить
до копеек, но так, чтобы их сумма совпала со значением,
указанным в общем счете.
1. Средневзвешенное значение совокупности элементов — среднее
значение, учитывающее вес, или важность, каждого элемента.
Значение вычисляется по формуле
(x
1
w(t
1
) + x
2
w(t
2
) + … + x
n
w(t
n
)) / (w(t
1
) + w(t
2
) + … + w(t
n
)), где
x
i
— значение элемента, t
i
— тип элемента, w(t
i
) — вес элемента
данного типа.
Требуется написать агрегатную функцию следующего вида:
w_agv(x float, t integer, w float[]).
2. Помещения бизнес-центра можно представить таблицей:
rent (
renter text PRIMARY KEY, -- арендатор
area integer -- площадь, м^2
);
Пример, показывающий проблему с обычным округлением:
INSERT INTO rent VALUES ('A',100), ('B',100), ('C',100);
SELECT round(1000.00 * area / sum(area) OVER ()), 2)
FROM rent;
