Создаём свою агрегатную функцию

Мы можем разрабатывать собственные агрегатные функции.
Расскажу на реальном на примере:

Задача: посчитать суммарную годовую инфляцию по годам на основании месячных данных.
Статистические данные по инфляции приводятся в процентах по отношению к прошлому месяцу.

Штатные агрегатные функции не подходят, SUM -- суммирует, AVG -- суммирует и делит, нам нужна фукция MUL (multiplication), чтоб перемножить проценты.

Пишем функцию сами:

1. Создаем тип

create type MulImpl as object (
AccValue number,
static function ODCIAggregateInitialize (sctx in out MulImpl)
return number, -- инициализирует значение агрегата перед началом обработки

member function ODCIAggregateIterate (self in out MulImpl, value in number)
return number, -- обновляет значение агрегата для новой строки данных

member function ODCIAggregateTerminate (self in MulImpl, returnValue out number, flags in number)
return number, -- возвращает значение агрегата и завершает обработку

member function ODCIAggregateMerge (self in out MulImpl, ctx2 in MulImpl)
return number -- используется для поддержки параллельных вычислений агрегата, если параллелизма нет -- без неё всё равно не работает
);

2. Создаем тело типа

create or replace type body MulImpl is
static function ODCIAggregateInitialize(sctx in out MulImpl)
return number is
begin
  sctx := MulImpl(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self in out MulImpl, value in number) return number is
begin
  self.AccValue := self.AccValue * value;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self in MulImpl, returnValue out number, flags in number) return number is
begin
  returnValue := self.AccValue;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self in out MulImpl, ctx2 in MulImpl) return number is
begin
  self.AccValue := self.AccValue * ctx2.AccValue;
  return ODCIConst.Success;
end;

end;
/
Цветом выделены строки, выполняющие собственно расчёт

3. Создаем собственно функцию

create or replace function MUL (input number) return number deterministic aggregate using MulImpl;

Слово DETERMINISTIC означает, что результат функции зависит только от её аргументов и не зависит ни от чего вне функции (не обращается к системной дате, последовательностям, не читает БД и пр.) -- при одних и тех-же аргументах будет один и тот-же результат. Oracle попытается прокешировать эту функцию -- в некоторых случаях результат будет не вычислять, я брать из кэша.

Можно включить фразу PARALLEL_ENABLE (очень актуально для хранилищ), но сейчас у меня нет возможности протестировать массивный параллелизм.

4. Для тестирования создаём таблицу:

create table INFL (MON date, INFL_VAL number)
/

5. Заполняем
(данные взяты отсюда http://ukrstat.gov.ua/operativ/operativ2006/ct/cn_rik/isc/isc_u/isc_m_u.htm):

insert into INFL (MON, INFL_VAL) values (to_date ('01.01.2010', 'DD.MM.YYYY'), 101.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.02.2010', 'DD.MM.YYYY'), 101.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.03.2010', 'DD.MM.YYYY'), 100.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.04.2010', 'DD.MM.YYYY'), 99.7);
insert into INFL (MON, INFL_VAL) values (to_date ('01.05.2010', 'DD.MM.YYYY'), 99.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.06.2010', 'DD.MM.YYYY'), 99.6);
insert into INFL (MON, INFL_VAL) values (to_date ('01.07.2010', 'DD.MM.YYYY'), 99.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.08.2010', 'DD.MM.YYYY'), 101.2);
insert into INFL (MON, INFL_VAL) values (to_date ('01.09.2010', 'DD.MM.YYYY'), 102.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.10.2010', 'DD.MM.YYYY'), 100.5);
insert into INFL (MON, INFL_VAL) values (to_date ('01.11.2010', 'DD.MM.YYYY'), 100.3);
insert into INFL (MON, INFL_VAL) values (to_date ('01.12.2010', 'DD.MM.YYYY'), 100.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.01.2011', 'DD.MM.YYYY'), 101); 
insert into INFL (MON, INFL_VAL) values (to_date ('01.02.2011', 'DD.MM.YYYY'), 100.9);
insert into INFL (MON, INFL_VAL) values (to_date ('01.03.2011', 'DD.MM.YYYY'), 101.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.04.2011', 'DD.MM.YYYY'), 101.3);
insert into INFL (MON, INFL_VAL) values (to_date ('01.05.2011', 'DD.MM.YYYY'), 100.8);
insert into INFL (MON, INFL_VAL) values (to_date ('01.06.2011', 'DD.MM.YYYY'), 100.4);
insert into INFL (MON, INFL_VAL) values (to_date ('01.07.2011', 'DD.MM.YYYY'), 98.7);
insert into INFL (MON, INFL_VAL) values (to_date ('01.08.2011', 'DD.MM.YYYY'), 99.6);
insert into INFL (MON, INFL_VAL) values (to_date ('01.09.2011', 'DD.MM.YYYY'), 100.1);
insert into INFL (MON, INFL_VAL) values (to_date ('01.10.2011', 'DD.MM.YYYY'), 100); 
insert into INFL (MON, INFL_VAL) values (to_date ('01.11.2011', 'DD.MM.YYYY'), 100.1);
insert into INFL (MON, INFL_VAL) values (to_date ('01.12.2011', 'DD.MM.YYYY'), 100.2);
commit;

6. Проверяем функцию как агрегатную

select '31.12.' || to_char (trunc (MON, 'Year'), 'YYYY') as YEAR_NUM, to_char (round (MUL (INFL_VAL / 100) * 100, 1), '999G999D0') as ACCUM_INFLATION
from INFL
group by trunc(MON, 'Year')

YEAR_NUM   ACCUM_INFLATION
---------- ---------------
31.12.2010      109.1    
31.12.2011      104.6    

7. Проверяем функцию как аналитическую

select to_char (last_day(MON), 'DD.MM.YYYY') as RMON
, to_char (INFL_VAL, '999G999D0') as MONTH_INFLATION
, to_char (round ((MUL (INFL_VAL / 100) over (order by MON range unbounded preceding) )* 100, 1), '999G999D0') as ACCUMULATED_INFLATION
from INFL
order by MON

Во второй колонке инфляция текущего месяца, в третьей -- накопленная с начала выборки.

RMON       MONTH_INFL ACCUMULATE
---------- ---------- ----------
31.01.2010      101.8      101.8
28.02.2010      101.9      103.7
31.03.2010      100.9      104.7
30.04.2010       99.7      104.4
31.05.2010       99.4      103.7
30.06.2010       99.6      103.3
31.07.2010       99.8      103.1
31.08.2010      101.2      104.3
30.09.2010      102.9      107.4
31.10.2010      100.5      107.9
30.11.2010      100.3      108.2
31.12.2010      100.8      109.1
31.01.2011      101.0      110.2
28.02.2011      100.9      111.2
31.03.2011      101.4      112.7
30.04.2011      101.3      114.2
31.05.2011      100.8      115.1
30.06.2011      100.4      115.6
31.07.2011       98.7      114.1
31.08.2011       99.6      113.6
30.09.2011      100.1      113.7
31.10.2011      100.0      113.7
30.11.2011      100.1      113.8
31.12.2011      100.2      114.1


Если в таблицу загрузить все данные с сайта, то при подсчёте итогов за год мы увидим расхождения в долях процентов с нашей функцией по некоторым годам. Это может быть связано со следующими причинами:
1. Возможно, нам надо в расчётные строки (которые выделил синим)  включить функцию TRUNC или ROUND, чтобы не считать слишком точно.
2. Потому, что на сайте укрстата последняя строка -- это не перемножение месяцев, а декабрь к прошлому декабрю.
3. Менялась методика расчёта.

Подробно -- Oracle® Database Data Cartridge Developer's Guide
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/toc.htm
Главы 11 и 22
Также использовалась книга "Oracle9/R2. Разработка и эксплуатация хранилищ баз данных", Хоббс, Хилсон, Лоуенд