SQL-Aggregatsfunktion "PRODUCT" fehlt ...? Kein Problem - wir bauen sie selbst!

Aggregate function for PRODUCT ...? No problem!

Auf einer Veranstaltung in München, während es um das Thema Analytische Funktionen ging, fragte mich jemand nach einer Aggregatsfunktion für die Bildung eines Produktes - also nicht das Aufsummieren der Werte wie bei SUM, sondern das multiplizieren - und in der Tat: eine solche Funktion gibt es in Oracle nicht.

Some days ago, at an event in Munich (the topic was about analytic functions I was asked whether there is an aggregation function for a "product" - that means not summing up all the values, but multiplying them. And indeed: such a function is not available within Oracle.

Nun kann man das mit PL/SQL natürlich auf einfachste Weise nachprogrammieren - aber eine solche Funktion lässt sich nicht in SQL-Abfragen mit GROUP BY nutzen. Berechnungen für Datengruppen in einer Tabelle müssen dann mühsam mit PL/SQL nachgebildet werden.

OK ... no problem ... this is an easy task for a PL/SQL developer. But wait: An own function could not be used in SQL GROUP BY clauses. If I wanted to calculate products for groups of table rows I'd have to implement this all by myself.

Aber das muss nicht sein. Es ist kaum bekannt, aber man kann eigene Aggregatsfunktionen in der Datenbank hinterlegen. Das habe ich in einem sehr frühen Blog-Posting schonmal genutzt: Mit der dort beschriebenen LIST-Funktion kann man VARCHAR2-Spalten auch von vor Oracle11g zusammenfassen (in Oracle11g gibt es mit LISTAGG ja eine eingebaute Funktion). Um ein User Defined Aggregate zu bauen, muss man eine Schnittstelle ausprogrammieren. Und hier ist der Code für AGG_PRODUCT.

But there are user defined aggregates: Virtually nobody seems to know them but they are so powerful. I already used this technology in an old blog posting: The LIST function aggregates VARCHAR2 values - in 10g and before (In Oracle11g the built-in function LISTAGG was introduced). To create an user defined aggregate, an interface must be implemented. This interface has cumbersome function names but is very simple to implement. Here we go for AGG_PRODUCT.

drop function agg_product;
drop type agg_product_t;

create type agg_product_t as object(
  v_agg_product number,

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

create or replace type body agg_product_t is

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

member function ODCIAggregateIterate(
  self IN OUT agg_product_t, 
  value IN number
) return number is
begin
  self.v_agg_product:=self.v_agg_product * nvl(value, 1);
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN agg_product_t, 
  returnValue OUT number, 
  flags IN number
) return number is
begin
  returnValue := self.v_agg_product;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT agg_product_t, ctx2 IN agg_product_t) return number is
begin
  if ctx2.v_agg_product is null and self.v_agg_product is not null then
    null;
  elsif ctx2.v_agg_product is not null and self.v_agg_product is not null then
    self.v_agg_product := self.v_agg_product * ctx2.v_agg_product;
  elsif ctx2.v_agg_product is not null and self.v_agg_product is null then
    self.v_agg_product := ctx2.v_agg_product * 1;
  elsif ctx2.v_agg_product is null and self.v_agg_product is null then
    null;
  end if;
  return ODCIConst.Success;
end;
end;
/
sho err

CREATE or replace FUNCTION agg_product (input number) RETURN number
PARALLEL_ENABLE AGGREGATE USING agg_product_t;
/
sho err

Die Funktion könnt Ihr danach sofort verwenden.

You can use the new function immediately.

SQL> select deptno, agg_product(sal) product_sal from emp
  2  group by deptno

    DEPTNO PRODUCT_SAL
---------- ------------------------------
        10 15925000000
        20 23562000000000000
        30 10153125000000000000

Auch als analytische Funktion kann sie verwendet werden.

It can also be used as an analytic function. This is very powerful.

select 
  ename, 
  agg_product(sal) over (order by sal desc rows between unbounded preceding and current row) product_sal 
from emp;

ENAME          PRODUCT_SAL
---------- ---------------
KING                  5000
FORD              15000000
SCOTT          45000000000
JONES      133875000000000
BLAKE      3,815437500E+17
CLARK      9,347821875E+20
:                        :

Das geht mit einer normalen PL/SQL-Funktion nun wirklich nicht; die ganze analytische "Power" von SQL kann so ausgenutzt werden. Nicht jeder braucht wirklich eine AGG_PRODUCT-Funktion; aber wenn Ihr Aggregate mit eigenen Funktionen bilden müsst, sind die User Defined Aggregates auf jeden Fall einen Blick wert.

A user defined aggregate has a big advantage over a plain PL/SQL function. Foremost it can be used in SQL GROUP BY and analytic clauses. All the analytic power of SQL can be used with your own code for aggregations.