Источник: oraclemaniacs
Дмитрий Богомолов
Встретил сегодня вопрос на SQL.RU "Как запросом получить произведение значений всех предыдущих строк?", и решил попробовать создать собственную функцию аггрегации. Задача на самом деле проще простого, но редко когда решаемая. Итак...
Вступление.
Все используют функции аггрегации, такие как MAX, MIN, AVG, SUM и прочие, которые выполняют действие над некоторым массивом данных. Однако время от времени возникает необходимость, скажем, не сложить, а перемножить все значения поля в выборке. В данном случае нам на помощь приходит интерфейс ODCIAggregate.
Краткое описание.
Интерфейс ODCIAggregate имеет 4 метода:
Чем же они занимаются? По порядку:
1. Initialize. Данный метод выполняет определенные действия перед началом вычислений.
2. Iterate. Собственно метод, выполняющий операцию над очередным значением из массива.
3. Merge. Метод, необходимый в случае распараллеливания выполнения расчета, когда весь массив бьется на части, а потом расчитанные по отдельности они объединяются. Так вот этот метод выполняет объединение пары результатов.
4. 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