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

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

Дмитрий Богомолов

Встретил сегодня вопрос на SQL.RU "Как запросом получить произведение значений всех предыдущих строк?", и решил попробовать создать собственную функцию аггрегации. Задача на самом деле проще простого, но редко когда решаемая. Итак...

Вступление.

Все используют функции аггрегации, такие как MAX, MIN, AVG, SUM и прочие, которые выполняют действие над некоторым массивом данных. Однако время от времени возникает необходимость, скажем, не сложить, а перемножить все значения поля в выборке. В данном случае нам на помощь приходит интерфейс ODCIAggregate.

Краткое описание.

Интерфейс ODCIAggregate имеет 4 метода:

Initialize
Iterate
Merge
Terminate

Чем же они занимаются? По порядку:
1. Initialize. Данный метод выполняет определенные действия перед началом вычислений.
2. Iterate. Собственно метод, выполняющий операцию над очередным значением из массива.
3. Merge. Метод, необходимый в случае распараллеливания выполнения расчета, когда весь массив бьется на части, а потом расчитанные по отдельности они объединяются. Так вот этот метод выполняет объединение пары результатов.
4. Terminate. Метод, заканчивающий расчеты и выдающий результат.

Кроме того необходимо объявить функцию, которая будет вызывать расчет аггрегатной функции.

Пример.

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

-- Создаем тип для вычислений
create type MultiImpl as object
(
multires NUMBER, -- результат
static function ODCIAggregateInitialize(sctx IN OUT MultiImpl)
 return number,
member function ODCIAggregateIterate(self IN OUT MultiImpl,
 value IN number) return number,
member function ODCIAggregateTerminate(self IN MultiImpl,
 returnValue OUT number, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT MultiImpl,
 ctx2 IN MultiImpl) return number
);
/

-- Создаем тело типа

create or replace type body MultiImpl is
-- Функция, выполняющая инициализацию объекта
static function ODCIAggregateInitialize(sctx IN OUT MultiImpl)
return number is
begin
sctx := MultiImpl(1);
return ODCIConst.Success;
end;

-- Функция, выполняющая расчет
member function ODCIAggregateIterate(self IN OUT MultiImpl, value IN number)
return number is
begin
  -- умножаем текущий результат на очередное значение
  self.multires := self.multires*value;
  return ODCIConst.Success;
end;

-- Функция, заканчивающая расчет
member function ODCIAggregateTerminate(self IN MultiImpl, returnValue OUT
number, flags IN number) return number is
begin
  returnValue := self.multires; -- Передаем результат в выходной параметр
  return ODCIConst.Success;
end;

-- На случай распараллеливания
member function ODCIAggregateMerge(self IN OUT MultiImpl, ctx2 IN MultiImpl) return number IS
begin
  -- Просто перемножаем результаты
  self.multires := self.multires*ctx2.multires;
  return ODCIConst.Success;
end;
end;
/

-- Ну и создаем функцию, которая будет вызывать аггрегацию
CREATE FUNCTION Multi (input NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING MultiImpl;
/

Итог.
Теперь пробуем использовать то, что получилось:

SQL> SELECT multi(col)
2    FROM (SELECT 2 AS col
3            FROM dual
4          UNION ALL
5          SELECT 3 AS col
6            FROM dual
7          UNION ALL
8          SELECT 4 AS col
9            FROM dual
10          UNION ALL
11          SELECT NULL AS col
12            FROM dual
13          UNION ALL
14          SELECT 7 AS col
15            FROM dual
16          UNION ALL
17          SELECT 8 AS col FROM dual) t;

MULTI(COL)
----------
1344

Ссылки по теме