Расширяемость

Агрегатные и оконные функции

12

Авторские права

© Postgres Professional, 2020 год.

Авторы: Егор Рогов, Павел Лузанов

Обратная связь

Отзывы, замечания и предложения направляйте по адресу:

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за

любые повреждения и убытки, включая потерю дохода, нанесенные

прямым или непрямым, специальным или случайным использованием

материалов курса. Компания Postgres Professional не предоставляет

каких-либо гарантий на материалы курса. Материалы курса

предоставляются на основе принципа «как есть» и компания Postgres

состо-

яние

В PostgreSQL имеется достаточно много встроенных агрегатных

функций. Часть из них определена стандартом (такие, как min, max,

sum, count и т. п.), часть является расширением стандарта.

Имеется некоторое состояние, представленное значением какого-либо

типа данных, которое инициализируется в начале определенным

значением (например, тип numeric и начальное значение 0).

Для каждой строки агрегируемой выборки вызывается функция

перехода, которой передается значение из текущей строки, и которая

должна обновить состояние (например, функция сложения).

В конце вызывается функция финализации, которая преобразует

полученное в результате работы состояние в результат (в нашем

примере достаточно просто вернуть число — в итоге получается аналог

функции sum).

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). В этом случае для всех строк одной группы будет

получено одинаковое значение функции.

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

Например, если в качестве оконной функции используется sum, мы

получаем сумму «нарастающим итогом».

Конечно, для каждой группы строк, определяемых предложением

1 2 3 4 5

1 2 3 4 5

1 2 3 4 5

Рамку можно указать явно в предложении ROWS BETWEEN. В том

числе двигаться может не только «голова» рамки, но и ее «хвост».

Если в предыдущих примерах рамка только расширялась (в нее

добавлялись все новые значения), то теперь ранее добавленные

значения значения могут «уходить» из рамки.

Чтобы пользовательская агрегатная функция работала эффективно

в таком режиме, нужно реализовать функцию инверсии, которая

устраняет значение из состояния.