Простенький пользовательский агрегат

Пользовательские агрегаты (custom aggregates) являются одной из тех уникальных особенностей PostgreSQL, понять которую кажется не под силу никому. Однако как только создать хотя бы один по-настоящему работающий пример, тучи разверзнуться, и вы будете удивляться, как же раньше жили без такого матёрого функционала. Так давайте создадим такой простенький агрегат. Он будет возвращать состояние, то есть наиболее часто встречающееся значение, для логического (булевого) поля.

Кто и зачем может захотеть странного? Ну, представьте, что вы денно и нощно мониторите несколько ваших веб-серверов, и хотите иметь почасовую картину аптайма (uptime). Допустим каждые 30 секунд состояние сервера заноситься в таблицу: TRUE — сервер работает, FALSE — сервер лежит. Тогда если большую часть времени сервер работал мы хотим вернуть TRUE, а ежели по большей части сервер стоял, то соответственно FALSE. А если и сама система мониторинга лежит и, следовательно, нет никаких данных, будем возвращать NULL.

Конечно же, всё это можно провернуть и с помощью множества разных других механизмов, например используя механизм WINDOW. Однако, представьте, что в одном запросе нужно будет работать и с другой накопленной статистикой, например, временем простоя или работы сервера. В таком случае PostgreSQL предлагает вам изящный механизм.

Для начала нам потребуется статистическая функция, которая будет накапливать данные о поле Boolean. Обычно такая функция имеет два входных параметра:

• параметр, в котором будут храниться вычисленные значения (ведь для каждой строки будет вызываться эта функция);
• параметр типа столбца, в котором будет значение для текущей строки.

Допустим, мы хотим хранить количество показаний UP and DOWN для сервера. Для этого можно использовать целочисленный массив. С таким же успехом это можно сделать и с помощью composite типа, прим. переводчика. Такую функцию легко написать даже на чистом SQL:

CREATE OR REPLACE function mode_bool_state(int[], boolean) RETURNS int[] LANGUAGE sql as $body$ SELECT CASE $2 WHEN TRUE THEN     array[ $1[1] + 1, $1[2] ] WHEN FALSE THEN     array[ $1[1], $1[2] + 1 ] ELSE     $1 END; $body$; 

Заметьте, что результат функции int[] будет подан в качестве первого параметра на вход этой же функции при вызове для следующей строки, прим. переводчика.

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

CREATE OR REPLACE FUNCTION mode_bool_final(INT[]) RETURNS boolean LANGUAGE sql as $body$ SELECT CASE WHEN ( $1[1] = 0 AND $1[2] = 0 ) THEN NULL ELSE $1[1] >= $1[2] END; $body$; 

Дело за малым — объявить агрегат:

CREATE AGGREGATE mode(boolean) (     SFUNC = mode_bool_state,     STYPE = INT[],     FINALFUNC = mode_bool_final,     INITCOND = '{0,0}' ); 

Здесь SFUNC и FINALFUNC — имена наших функций, STYPE — тип данных для сбора статистики, INITCOND — начальные условия.

Посмотрим как это работает!

SELECT server_name,     sum(CASE WHEN server_up THEN 0.5 ELSE 0 END) as minutes_up,     mode(server_up) as mode FROM servers  WHERE montime BETWEEN '2013-04-01' and '2013-04-01 01:00:00'; 

server_name      minutes_up       mode web1             56.5             TRUE web2             0.0              FALSE web3             48.0             TRUE web4             11.5             FALSE 

PS Статья на английском Тома Брауна также повествует о том, как создать пользовательский агрегат. В ней автор не использует завершающую необязательную функцию FINALFUNC, так как тип для сбора данных STYPE в его примере совпадает с базовым типом агрегата.