Пользовательские агрегатные функции в Oracle

СУБД Oracle, начиная с версии 8, предоставляет ряд программных интерфейсов для создания расширений (extensibility interfaces). Реализации этих интерфейсов могут быть написаны пользователями СУБД. В частности, это интерфейсы для создания расширений индексирования, расширений стоимостного оптимизатора, пользовательских агрегатных функций.

На базе этих программных интерфейсов компанией Oracle созданы и распространяются вместе с СУБД такие функциональные расширения, как Oracle Text, Oracle Multimedia и Oracle Spatial. Эти расширения Oracle именует data cartridges, а соответствующая технология называется Oracle Data Cartriges (ODC).

Теперь, обрисовав контекст, приступаю к созданию пользовательской агрегатной функции.

Специфика агрегатной функции в том, что она "накапливает" свой единственный результат, будучи вызванной многократно, по одному разу для каждой строки в промежуточной выборке команды SELECT. Для более эффективного выполнения вся выборка может быть разбита на несколько частей, которые будут обработаны параллельно и независимо друг от друга, после чего частичные реузультаты будут интегрированы в окончательный результат.

Для определения пользовательской агрегатной функции нужно определить объектный тип, реализующий интерфейс расширения, и PL/SQL функцию-обертку для этого типа. Интерфейc, который должен быть реализован пользовательским объектным типом, включает четыре метода:

• ODCIAggregateInitialize, вызывается один раз в начале работы агрегатной функции, инициализирует необходимые переменные;
• ODCIAggregateIterate, вызывается для каждой строки промежуточной выборки команды SELECT и "накапливает" в переменных частичный результат на основе полученных на вход значений;
• ODCIAggregateMerge, вызывается для слияния накопленных частичных результатов при распараллеленном выполнении запроса;
• ODCIAggregateTerminate, вызывается один раз в конце работы агрегатной функции, и формирует окончательный результат.

Например, для расчета среднего арифметического нужно "накопить" сумму S и количество N просуммированных чисел (ODCIAggregateIterate), а в конце разделить S на N (ODCIAggregateTerminate). Для накопления суммы S и количества чисел N в самом начале нужно инициализировать соответствующие переменные (ODCIAggregateInitialize), установив их равными 0.

Если чисел, для которых нужно рассчитать среднее арифметическое, очень много, то расчет можно провести быстрее, распараллелив вычисления. Нужно разбить последовательность чисел на несколько частей, параллельно и независимо друг от друга накопить суммы S0,...,Sm и количества чисел N0,...,Nm в каждой части (ODCIAggregateIterate), затем просуммировать накопленные суммы и количества чисел, соответственно (ODCIAggregateMerge), и, наконец, поделить полную сумму на полное количество чисел (ODCIAggregateTerminate).

Надеюсь, разобранный пример достаточно прояснил ситуацию. Если нет, то попробуйте вручную рассчитать среднее арифметическое некоторой небольшой выборки первым и вторым (с распараллеливанием) способами и убедитесь, что результаты совпадают.

Не всякий агрегирующий алгоритм можно распараллелить.

А теперь реализуем агрегатную функцию, далекую от статистики. Функция cat будет конкатенировать значения столбца в строку VARCHAR2, формируя список значений, разделенных запятой. То есть, из многострочной выборки вида

один
два
три
четыре

функция сформирует одну строку VARCHAR2

один, два, три, четыре

Создадим пользовательский объектный тип, реализующий интерфейс агрегирования:

CREATE OR REPLACE TYPE t_cat AS OBJECT
(
    the_string VARCHAR2(32000),
    STATIC FUNCTION ODCIAggregateInitialize(
        sctx IN OUT t_cat) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(
        self IN OUT t_cat,
        val IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(
        self IN OUT t_cat,
        ctx2 IN t_cat) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(
        self IN OUT t_cat,
        returnvalue OUT VARCHAR2,
        flags IN NUMBER) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_cat IS
  
    STATIC FUNCTION ODCIAggregateInitialize(
        sctx IN OUT t_cat) RETURN NUMBER
    IS
    BEGIN
        sctx := t_cat(NULL);
        RETURN ODCIConst.Success;
    END ODCIAggregateInitialize;
    
    MEMBER FUNCTION ODCIAggregateIterate(
        self IN OUT t_cat,
        val IN VARCHAR2) RETURN NUMBER
    IS
    BEGIN
        self.the_string := self.the_string || ', ' || val;
        RETURN ODCIConst.Success;
    END ODCIAggregateIterate;

    MEMBER FUNCTION ODCIAggregateMerge(
        self IN OUT t_cat,
        ctx2 IN t_cat) RETURN NUMBER
    IS
    BEGIN
        self.the_string := self.the_string || ctx2.the_string;
        RETURN ODCIConst.Success;
    END ODCIAggregateMerge;
    
    MEMBER FUNCTION ODCIAggregateTerminate(
        self IN OUT t_cat,
        returnvalue OUT VARCHAR2,
        flags IN NUMBER) RETURN NUMBER
    IS
    BEGIN
        IF self.the_string IS NOT NULL THEN
            self.the_string := substr(self.the_string, 3);
        END IF;
        returnvalue := self.the_string;
        RETURN ODCIConst.Success;
    END ODCIAggregateTerminate;
  
END;
/

Назначение функции ODCIAggregateInitialize - создать экземпляр данного объектного типа и инициализировать переменные, если необходимо. Созданный экземпляр будет передаваться в качестве параметра self при вызове других функций ODCIAggregate*.

Функция ODCIAggregateIterate, вызываемая для каждой строки выборки, в качестве второго параметра получает значение столбца, переданного в агрегатную функцию. Значение столбца конкатенируется со строкой self.the_string, в которой и происходит "накопление" результата агрегатной функции. Функция не проверяет значение столбца на NULL, поскольку Oracle не вызывает ODCIAggregateIterate с NULL значениями (мы убедимся в этом ниже).

Функция ODCIAggregateMerge принимает в качестве второго параметра другой экземпляр данного объектного типа, и выполняет слияние накопленных во втором экземпляре данных с данными, накопленными в экземпляре self. Для типа t_cat это означает конкатенирование строк the_string из двух экземпляров и сохранение результата в экземпляре self.

Функция ODCIAggregateTerminate возвращает итоговый результат через параметр returnvalue.

Теперь, когда у нас есть объектный тип, реализующий интерфейс агрегирования, можно создать функцию и воспользоваться ей:

SQL> CREATE OR REPLACE FUNCTION cat(val VARCHAR2) RETURN VARCHAR2
     PARALLEL_ENABLE
     AGGREGATE USING t_cat;
     /
Function created
 
SQL> SELECT cat(username) FROM all_users;
 
CAT(USERNAME)
---------------------------------------------------------------------------------------------------------------------------
XS$NULL, AY, APEX_040200, APEX_040000, APEX_PUBLIC_USER, FLOWS_FILES, HR, MDSYS, ANONYMOUS, XDB, CTXSYS, OUTLN, SYSTEM, SYS

Мы получили неупорядоченный список имен пользователей. Получить упорядоченный список можно так:

SQL> SELECT cat(username) FROM (SELECT username FROM all_users ORDER BY username);
 
CAT(USERNAME)
---------------------------------------------------------------------------------------------------------------------------
ANONYMOUS, APEX_040000, APEX_040200, APEX_PUBLIC_USER, AY, CTXSYS, FLOWS_FILES, HR, MDSYS, OUTLN, SYS, SYSTEM, XDB, XS$NULL

Но всегда ли этот прием приведет к получению упорядоченного списка? Этого гарантировать нельзя, поскольку выполнение нашей агрегатной функции может быть распрараллелено, а склейка частичных результатов в итоговую строку может произойти в произвольном порядке. Поэтому, если мы заинтересованы в получении упорядоченных списков нужно либо отказаться от распараллеливания алгоритма, либо усложнить логику слияния и завершения агрегатной функции.

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

SQL> SELECT UNIQUE cat(username) OVER (ORDER BY username ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM all_users;
 
CAT(USERNAME)OVER(ORDERBYUSERN
---------------------------------------------------------------------------------------------------------------------------
ANONYMOUS, APEX_040000, APEX_040200, APEX_PUBLIC_USER, AY, CTXSYS, FLOWS_FILES, HR, MDSYS, OUTLN, SYS, SYSTEM, XDB, XS$NULL

SQL> SELECT UNIQUE cat(username) OVER (ORDER BY created DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM all_users;
 
CAT(USERNAME)OVER(ORDERBYCREAT
---------------------------------------------------------------------------------------------------------------------------
AY, APEX_040200, APEX_040000, APEX_PUBLIC_USER, FLOWS_FILES, HR, MDSYS, XS$NULL, ANONYMOUS, XDB, CTXSYS, OUTLN, SYS, SYSTEM

А вот демонстрация того, что Oracle отбрасывает NULL значения при выполнении пользовательской агрегатной функции:

SQL> SELECT cat(q) FROM (
        SELECT 'hello' q FROM dual
        UNION ALL
        SELECT '' FROM dual
        UNION ALL
        SELECT 'world' FROM dual
        UNION ALL
        SELECT NULL FROM dual
        UNION ALL
        SELECT 'etc' FROM dual
     );

CAT(Q)
----------------------------------------
hello, world, etc

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

SQL> CREATE OR REPLACE FUNCTION cat(val VARCHAR2) RETURN VARCHAR2
     AGGREGATE USING t_cat;
     /
Function created

SQL> SELECT cat(username) FROM (SELECT username FROM all_users ORDER BY username);
 
CAT(USERNAME)
---------------------------------------------------------------------------------------------------------------------------
ANONYMOUS, APEX_040000, APEX_040200, APEX_PUBLIC_USER, AY, CTXSYS, FLOWS_FILES, HR, MDSYS, OUTLN, SYS, SYSTEM, XDB, XS$NULL