Korrekte Implementierung der Aggregatsfunktion SUM

Heute geht es um die bekannte Aggregatsfunktion SUM und deren nicht ganz optimales Verhalten. Damit meine ich im Wesentlichen die Rückgabe der Funktion, im Falle einer leeren Tabelle.

Betrachten wir dazu das folgende Beispiel:

create table empty_tab
(
 val number
);

select sum(val) sum_val from empty_tab;

Das Ergebnis ist eine Tabelle, die eine Spalte mit dem Namen SUM_VAL enthält deren "Wert" NULL ist; in der Session wurde zuvor set null ? ausgeführt.

SUM_VAL               
-----------------------
?

Und genau an dieser Stelle befindet sich der Fehler in der Implementierung, denn korrekt wäre die Rückgabe von 0. Eine Summe ohne Summanden (die leere Summe) ergibt in der Mathematik 0.

Natürlich kann man z.B. die Funktion COALESCE verwenden, um das gewünschte Ergebnis zu erhalten.

select coalesce(sum(val),0) sum_val from empty_tab;

SUM_VAL               
-----------------------
0

Aber glücklicherweise kann man mithilfe von User-Defined-Aggregates eine eigene Funktion, die ich RSUM nenne, implementieren, die ein korrektes Verhalten zeigt.

Dazu zunächst der Objekt-Typ:

create or replace type sum_t as object
(
  v_sum number,

  static function ODCIAggregateInitialize(
   sctx IN OUT sum_t) return number,

  member function ODCIAggregateIterate(
   self IN OUT sum_t, 
   value IN number) return number,

  member function ODCIAggregateTerminate(
   self IN sum_t, 
   returnValue OUT number, 
   flags IN number) return number,

  member function ODCIAggregateMerge(
   self IN OUT sum_t, 
   ctx2 IN sum_t) return number
);

Die Variable v_sum dient der Speicherung der Summe. Der nun folgende Rumpf des Objek-Typs enthält dann die korrekte Initialisierung mit 0 (siehe Zeile 6 des folgenden Listings).

create or replace type body sum_t is

  static function ODCIAggregateInitialize(
   sctx IN OUT sum_t) return number is
  begin
   sctx := sum_t(0);
   return ODCIConst.Success;
  end;
  
  member function ODCIAggregateIterate(
   self IN OUT sum_t, 
   value IN number) return number is
  begin
   self.v_sum := self.v_sum + value;
   return ODCIConst.Success;
  end;
  
  member function ODCIAggregateTerminate(
   self IN sum_t, 
   returnValue OUT number, 
   flags IN number) return number is
  begin
   returnValue := self.v_sum;
   return ODCIConst.Success;
  end;
  
  member function ODCIAggregateMerge(
   self IN OUT sum_t, 
   ctx2 IN sum_t) return number is
  begin
   self.v_sum := self.v_sum + ctx2.v_sum;
   return ODCIConst.Success;
  end;

end;

Jetzt fehlt nur noch die Funktion, welche den Objekt-Typ verwendet.

create or replace function rsum (input number) return number
deterministic parallel_enable aggregate using sum_t;

Eine Ausführung der Funktion RSUM auf einer leeren Tabelle liefert dann das korrekte Ergebnis 0 zurück. Anmerkung: Da die Methode ODCIAggregateIterate nur für Nicht-NULL's aufgerufen wird, gilt dies auch für eine Tabelle, die in der entsprechenden Spalte nur NULL's enthält.

select rsum(val) rsum_val from empty_tab;                 

RSUM_VAL               
-----------------------
0

Ähnlich verhält es sich übrigens bei der Aggregatsfunktion AVG zur Berechnung des arithmetischen Mittels. Wendet man die Funktion auf eine leere Tabelle an erhält man als Rückgabe NULL. Eine Fehlermeldung der Art "divisor is equal to zero" erschien mir in diesem Falle schlüssiger.