Noch ein Szenario mit "User Defined Aggregates"

Another scenario for user-defined-aggregates

Im vorletzten Blog Posting hatte ich ja vorgestellt, wie man sich eigene Aggregatsfunktionen mit den User Defined Aggregates bauen kann. Heute greife ich das Thema nochmals auf, denn diese Funktionen haben mehr denkbare Einsatzgebiete, als man sich denken mag ...

In the previous but one blog posting I talked about user defined aggregates. Today I will again write about this topic ... since those functions are applicable in more situations than you might think.

Wenn man die Dokumentation liest, scheint es, als ob User Defined Aggregates nur einzelne skalare Werte aggregieren können - aber bei genauerer Betrachtung ist das nicht der Fall - man kann über den Umweg eines Objekttypen auch ganze "Datasets" aggregieren lassen. Als Beispiel habe ich das mal mit einem "Sparbuch" probiert - auf diesem Sparkonto gibt es Ein- und Auszahlungen - und mit einer Aggregatsfunktion sollen die Zinsen berechnet werden. Die hängen neben dem Betrag der Transaktion auch von deren Datum ab. Zuerst brauchen wir also unsere Tabelle für die Konten ...

When reading the documentation and creating some example functions it seems that user defined aggregates can only aggregate scalar values. But this is not true - with the utilization of object types we can aggregate even complex datasets. To illustrate this I have created the example of a savings account. On this account we have transactions (debit and credit). And now (at the end of the year) we have to calculate interest. And the interest depends on the transactions' amount as well as on its date. So, we create the transaction table first.

create table tab_buchungen(
 id       number,
 konto    number,
 datum    date,
 betrag   number
)
/

alter session set nls_date_format='DD.MM.YYYY'
/

/*
 * Konto I
 */

insert into tab_buchungen values (1, 4711, '01.01.2010', 500);  

insert into tab_buchungen values (2, 4711, '09.01.2010', 1500);
insert into tab_buchungen values (3, 4711, '15.02.2010', -912);
insert into tab_buchungen values (4, 4711, '25.03.2010', 2500);
insert into tab_buchungen values (5, 4711, '18.05.2010', 2000);
insert into tab_buchungen values (6, 4711, '27.07.2010', -1500);
insert into tab_buchungen values (7, 4711, '03.08.2010', 850);
insert into tab_buchungen values (8, 4711, '19.10.2010', 1350);
insert into tab_buchungen values (9, 4711, '28.11.2010', -2000);


/*
 * Konto II
 */

insert into tab_buchungen values (10, 4712, '01.01.2010', 500);

insert into tab_buchungen values (11, 4712, '09.02.2010', 1500);
insert into tab_buchungen values (12, 4712, '28.02.2010', -912);
insert into tab_buchungen values (13, 4712, '25.03.2010', 2500);
insert into tab_buchungen values (14, 4712, '18.04.2010', 2000);
insert into tab_buchungen values (15, 4712, '19.04.2010', 5000);
insert into tab_buchungen values (16, 4712, '27.05.2010', -3500);
insert into tab_buchungen values (17, 4712, '04.06.2010', 100);
insert into tab_buchungen values (18, 4712, '03.08.2010', 152);
insert into tab_buchungen values (19, 4712, '19.10.2010', 950);
insert into tab_buchungen values (20, 4712, '28.11.2010', 4000);


/*
 * Konto III
 */

insert into tab_buchungen values (21, 4713, '31.12.2009', 5000);


commit
/

Für die Zinsberechnung nehmen wir die Zinszahlen-Methode. Wer genaueres wissen möchte, schaut am besten im Wikipedia-Artikel nach. Die Aggregatsfunktion muss also während der Iteration die Zinszahlen "aggregieren" und zum Abschluß aus der Zinszahl die Zinsen rechnen.

To calculate intrerest we use the "interest number" method (german wikipedia article here - did not found an english one). The interest number aggregates the information about the transaction amount and date. From the interest number we can then calculate the amount of interest.

Um die Zinszahlen rechnen zu können, braucht man pro Transaktion eben den Betrag und das Datum, also zwei Werte. Das User Defined Aggregate kann aber nur einen Parameter entgegennehmen - später auf einer Spalte arbeiten. Diesen Gegensatz lösen wir auf, indem wir die beiden Werte in einen Objekttypen kapseln - den Objekttypen verwenden wir quasi als "Transportobjekt".

So we need the transaction amount and date in order to calculate the interest number - so we need two values. The user defined aggregate allows only one parameter. We solve this situation by encapsulating the two values in one object type as a "transport object".

create type t_buchung as object(
  buchungsdatum        date,
  betrag               number
)
/

Als nächstes brauchen wir noch globale Daten wie den Zinssatz und das Abrechnungsdatum (meist der 31.12., könnte natürlich aber auch jedes andere sein. Da diese Angaben für alle Tabellenzeilen gleich sind, packen wir sie in ein PL/SQL-Paket - direkt erstellt mit get- und set-Methoden.

Next we'll need some global data applicable for all table rows. This is the interest rate as well as the calculation date. Most often this will be the 31st of December - but any date is possible. To hold those global values we'll use a PL/SQL package. The following code creates it as well as some get- and set- methods.

create or replace package pkg_zinsen is
 function get_zinssatz return number;
 procedure set_zinssatz(p_zinssatz in number);
 function get_abrechnungsdatum return date;
 procedure set_abrechnungsdatum (p_datum in date);
end pkg_zinsen;
/

create or replace package body pkg_zinsen is
 g_zinssatz number;
 g_datum    date;

 function get_zinssatz return number is begin return g_zinssatz; end;
 procedure set_zinssatz(p_zinssatz in number) is begin g_zinssatz := p_zinssatz; end;
 function get_abrechnungsdatum return date is begin return g_datum; end;
 procedure set_abrechnungsdatum (p_datum in date) is 
 begin 
  g_datum := p_datum; 
  if extract(DAY from g_datum) = 31 then
    g_datum := to_date('30'||to_char(g_datum, 'MMYYYY'), 'DDMMYYYY');
  end if;
  if to_char(g_datum, 'DDMM') = '2802' or to_char(g_datum, 'DDMM') = '2902' then
    g_datum := to_date('30'||to_char(g_datum, 'MMYYYY'), 'DDMMYYYY');
  end if;
 end set_abrechnungsdatum;
end pkg_zinsen;
/

Nun kommt dann die Implementierung der Aggregatsfunktion - achtet auf die Verwendung des Objekttypen T_BUCHUNG. Die Funktion bekommt daher für jede Iteration eben nicht einen skalaren, sondern dadurch gleich zwei Werte übergeben. Die anderen Daten nimmt sie aus dem PL/SQL-Paket und hat somit alle Informationen beisammen. In der ODCIAggregateIterate-Methode werden die Zinszahlen berechnet und in der ODCIAggregateTerminate werden die Zinsen ermittelt.Ist ein Zinssatz von 0% angegeben, werden die Zinszahlen selbst zurückgegeben. Wie im Bankwesen üblich, wird ein Monat stets mit 30 Tagen gerechnet.

Now comes the user defined aggregate implementation. Note that the type BUCHUNG_T is used in the ODCIAggregateIterate method - so the method gets the amount as well as the date. The "global" data as the calculation date and the interest rate is being retrieved from the PL/SQL package. The ODCIAggregateIterate method aggregates the interest numbers and the ODCIAggregateTerminate method finally calculates interest using the aggregated interest number. If the interest rate is set to zero the function will return the interest number itself. And - as usual in the financial industry - every month has 30 days.

create type agg_zinsen_t as object(
  v_agg_zinsen number,

  static function ODCIAggregateInitialize(
    sctx IN OUT agg_zinsen_t
  ) return number,
  member function ODCIAggregateIterate(
    self IN OUT agg_zinsen_t, value IN t_buchung
  ) return number,
  member function ODCIAggregateTerminate(
    self IN agg_zinsen_t, returnValue OUT number, flags IN number
  ) return number,
  member function ODCIAggregateMerge(
    self IN OUT agg_zinsen_t, ctx2 IN agg_zinsen_t
  ) return number
 );
/
sho err

create or replace type body agg_zinsen_t is

static function ODCIAggregateInitialize(sctx IN OUT agg_zinsen_t)
return number is
begin
  sctx := agg_zinsen_t(0);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self IN OUT agg_zinsen_t, 
  value IN t_buchung
) return number is
  v_daysm number; 
  v_daysd number; 
  v_daysy number; 
begin
  v_daysy := (extract(YEAR from pkg_zinsen.get_abrechnungsdatum) - extract(YEAR from value.buchungsdatum)) * 360;
  v_daysm := (extract(MONTH from pkg_zinsen.get_abrechnungsdatum) - extract(MONTH from value.buchungsdatum)) * 30;
  if to_char(value.buchungsdatum, 'DD') = '31' or to_char(value.buchungsdatum, 'DDMM') in ('2802','2902') then
    v_daysd := 30;
  else 
    v_daysd := extract(DAY from value.buchungsdatum);
  end if;
  v_daysd := extract(DAY from pkg_zinsen.get_abrechnungsdatum) - v_daysd;
  self.v_agg_zinsen := self.v_agg_zinsen + round((value.betrag * (v_daysy + v_daysd + v_daysm) / 100)); 
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN agg_zinsen_t, 
  returnValue OUT number, 
  flags IN number
) return number is
begin
  if pkg_zinsen.get_zinssatz = 0 then 
    returnValue := self.v_agg_zinsen;
  else 
    returnValue := self.v_agg_zinsen / (360 / pkg_zinsen.get_zinssatz);
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT agg_zinsen_t, ctx2 IN agg_zinsen_t) return number is
begin
  self.v_agg_zinsen := self.v_agg_zinsen + ctx2.v_agg_zinsen;
  return ODCIConst.Success;
end;
end;
/
sho err

Nun kommt die Einrichtung der Aggregatsfunktion an sich - die auf der eben eingespielten Implementierung basiert - wiederum nimmt sie keinen skalaren Datentypen entgegen, sondern BUCHUNG_T.

Then we crate the top-level aggregate function based on the above implementation. Again, the input parameter is of type BUCHUNG_T.

CREATE or replace FUNCTION agg_zinsen (input t_buchung) RETURN number
PARALLEL_ENABLE AGGREGATE USING agg_zinsen_t;
/
sho err

Und das war's - nun kann man testen. Zuerst setzen wir die globalen Parameter im PL/SQL-Paket.

And that's it. Now you can test the function. First set the "global" values ...

begin
 pkg_zinsen.set_abrechnungsdatum(to_date('31122010', 'DDMMYYYY'));
 pkg_zinsen.set_zinssatz(1.5);
end;
/

Und dann können wir die Zinsen eines Kontos abrechnen - mit nichts als einer SQL-Abfrage. Achtet darauf, dass Ihr syntaktisch zuerst ein BUCHUNG_T-Objekt mit Buchungsdatum und Betrag erzeugt und dieses dann an die Aggregatsfunktion übergebt.

And then you can calculate the interest amount with just a SQL query. Note that you (syntactically) first create a BUCHUNG_T object using the transaction date and amount. This object is passed to the aggregate function.

select konto, agg_zinsen(t_buchung (datum, betrag)) zinsen from tab_buchungen
group by konto
/

     KONTO     ZINSEN
---------- ----------
      4711 61,5666667
      4712      96,95
      4713         75

Ein Zinssatz von 0% gibt die Zinszahl direkt zurück.

Set the interest rate to zero - and you will get the interest numbers.

begin
 pkg_zinsen.set_abrechnungsdatum(to_date('31122010', 'DDMMYYYY'));
 pkg_zinsen.set_zinssatz(0);
end;
/

select konto, agg_zinsen(t_buchung (datum, betrag)) zinsen from tab_buchungen
group by konto
/

     KONTO     ZINSEN
---------- ----------
      4711      14776
      4712      23268
      4713      18000

Es lassen sich also auch komplexere Dinge mit Benutzerdefinierten Aggregaten erledigen. Interessant sind hier viele Aufgaben aus der Finanzmathematik - Neben der einfachen Zinsrechnung ist auch jede Form der Renditeermittlung interessant. Und wenn es als Aggregatsfunktion bereitsteht, erhöhen sich die Nutzungsmöglichkeiten nochmals massiv - denn die Funktion steht per SQL-Abfrage bereit.

So we have seen that user defined aggregates can also do a bit more complex things: Financial math might be an interesting application area. Yield calculation might also be done with a user defined aggregate. The good but about a user defined aggregate is that the implemented functionality is being leveraged to the SQL layer - a simple SQL query with a GROUP BY or an analytic clause then does the trick.