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

Пользовательские агрегаты (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 в его примере совпадает с базовым типом агрегата.

Автор: pasha_golub

Источник