Можно ли создать пользовательскую агрегатную функцию?

Пользовательские агрегатные функции (UDAF) — это мощный механизм, позволяющий разработчикам определять собственную логику агрегации данных поверх наборов строк в SQL. Они расширяют стандартные возможности СУБД, когда встроенных функций (например, SUM, COUNT, MAX) недостаточно для решения конкретной бизнес- или аналитической задачи.

Как это работает

Агрегатная функция обрабатывает группу строк и возвращает одно результирующее значение. При создании пользовательской функции необходимо определить несколько обязательных компонентов:

• Начальное состояние: начальное значение аккумулятора.
• Функция накопления: вызывается для каждой строки в группе, обновляя состояние аккумулятора.
• Функция завершения: вызывается после обработки всех строк, чтобы преобразовать итоговое состояние в конечный результат.
• Иногда требуется функция объединения (для параллельного выполнения) и функция сериализации/десериализации.

Пример в PostgreSQL

Создадим простую агрегатную функцию для вычисления среднего геометрического. В PostgreSQL это можно сделать, комбинируя встроенные функции или определяя собственные на PL/pgSQL.

-- 1. Создаём функцию накопления (state и value)
CREATE OR REPLACE FUNCTION geom_accum(state numeric, value numeric)
RETURNS numeric AS $$
BEGIN
    RETURN state * value;
END;
$$ LANGUAGE plpgsql;

-- 2. Создаём функцию завершения
CREATE OR REPLACE FUNCTION geom_final(state numeric, count integer)
RETURNS numeric AS $$
BEGIN
    RETURN power(state, 1.0 / count);
END;
$$ LANGUAGE plpgsql;

-- 3. Создаём сам агрегат
CREATE AGGREGATE geometric_mean(numeric) (
    sfunc = geom_accum,      -- функция накопления
    stype = numeric,         -- тип состояния
    finalfunc = geom_final,  -- функция завершения
    initcond = 1             -- начальное состояние (1 для умножения)
);

-- Использование
-- SELECT geometric_mean(price) FROM products WHERE category_id = 5;

Где применяется

Пользовательские агрегаты широко используются в аналитических отчётах, data science и бизнес-приложениях, где требуются нестандартные метрики. Примеры:

• Статистические расчёты: медиана, мода, стандартное отклонение.
• Работа со строками: конкатенация уникальных значений с сортировкой.
• Бизнес-логика: расчёт взвешенного среднего, агрегация по иерархии.
• Обработка массивов или JSON: свёртка сложных структур.

Поддержка и синтаксис сильно различаются между СУБД. В Microsoft SQL Server используются сборки на .NET, в MySQL — на C/C++, в SQLite — виртуальные таблицы или расширения.

Вывод: Создание пользовательских агрегатных функций целесообразно, когда встроенные агрегаты SQL не покрывают требуемую логику расчёта, и необходимо выполнить сложную агрегацию непосредственно на стороне базы данных для повышения производительности и согласованности.