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