Logo Oracle Deutschland   Application Express Community
Daten zusammenfassen mit Aggregatsfunktionen
Erscheinungsmonat APEX-Version Datenbankversion
April 2012 alle ab 9.2

Die Verwendung von Berichten und damit von SQL-Abfragen aller Art ist für den APEX-Entwickler alltäglich. Das Zusammenfassen von Daten mit Hilfe von Aggregatsfunktionen wie AVG, SUM, MIN oder MAX ist dabei nichts Besonderes. Dieser Tipp stellt verschiedene Aggregatsfunktionen und deren Anwendung in SQL-Abfragen vor. Wir beginnen mit einfachen GROUP BY-Varianten, leiten über zu weniger bekannten Funktionen wie der linearen Regression und schließen mit einem Abschnitt über analytische Funktionen, also solchen, bei denen das "Datenfenster" zur Anwendung der Aggregatsfunktion dynamisch definiert werden kann.

Die Beispiele gehen von folgender Tabelle aus. Das SQL-Skript verkauf.sql legt die Tabelle in Ihrem Datenbankschema an. 

    JAHR    MONAT UMSATZ_EUR WERBUNG_TV_EUR WERBUNG_ZEITG_EUR ANZAHL_VERKAEUFER
-------- -------- ---------- -------------- ----------------- -----------------
    2004       11     130000          14000               400                10
    2004       12     140000          13200               700                10
    2005        1     100000           9000              1000                10
    2005        2     103000           9500               900                10
    2005        3     112000          10000              1500                10
    2005        4      90000          12000              1000                 8
    2005        5      98000          13000              1000                 8
    2005        6      70000           4000              5000                 9
    2005        7     109010          12000              2000                15
    2005        8     120000          10000               500                15
    2005        9     130000           9000              1000                15
    2005       10     150000          15000              1000                15
    2005       11     170000          18000              1000                15
    2005       12     200000          18000              1000                15
    2006        1     160000          10000              1000                10
    2006        2     140000          10200              1500                10

GROUP BY und mehr ...

Wir beginnen mit einem sehr einfachen Beispiel. Für jedes Jahr soll die Summe, der Durchschnitt, der minimale und der maximale Umsatz (Spalte UMSATZ_EUR) ermittelt werden. Das erledigt eine ganz normale SQL-Abfrage mit Aggregatsfunktion und GROUP BY. 

select jahr, sum(umsatz_eur), avg(umsatz_eur), min(umsatz_eur), max(umsatz_eur)
from verkauf group by jahr
order by 1

          JAHR SUM(UMSATZ_EUR) AVG(UMSATZ_EUR) MIN(UMSATZ_EUR) MAX(UMSATZ_EUR)
-------------- --------------- --------------- --------------- ---------------
          2004        270000,0        135000,0        130000,0        140000,0
          2005       1452010,0        121000,8         70000,0        200000,0
          2006        300000,0        150000,0        140000,0        160000,0

So weit, so gut. Allerdings erlaubt GROUP BY wesentlich mehr als nur das: Mit der Erweiterung ROLLUP wird der Ergebnismenge ein Aggregat "über alles" hinzugefügt. 

select jahr, sum(umsatz_eur), avg(umsatz_eur), min(umsatz_eur), max(umsatz_eur)
from verkauf group by rollup (jahr)
order by 1

          JAHR SUM(UMSATZ_EUR) AVG(UMSATZ_EUR) MIN(UMSATZ_EUR) MAX(UMSATZ_EUR)
-------------- --------------- --------------- --------------- ---------------
          2004        270000,0        135000,0        130000,0        140000,0
          2005       1452010,0        121000,8         70000,0        200000,0
          2006        300000,0        150000,0        140000,0        160000,0
                     2022010,0        126375,6         70000,0        200000,0

Ohne ROLLUP bräuchte es hierfür eine zusätzliche SQL-Abfrage, deren Ergebnisse mit UNION ALL angefügt werden. Im Vergleich dazu ist diese Variante wesentlich effizienter; gerade auf großen Datenmengen. Nimmt man auch die Spalte MONAT in die ROLLUP-Klausel, so bekommt man die Zeilen selbst, Aggregate für das einzelne Jahr und "über alles". 

select jahr, monat, sum(umsatz_eur), avg(umsatz_eur), min(umsatz_eur), max(umsatz_eur)
from verkauf group by rollup (jahr,monat)
order by jahr, monat

 JAHR MONAT SUM(UMSATZ_EUR) AVG(UMSATZ_EUR) MIN(UMSATZ_EUR) MAX(UMSATZ_EUR)
----- ----- --------------- --------------- --------------- ---------------
 2004    11        130000,0        130000,0        130000,0        130000,0
 2004    12        140000,0        140000,0        140000,0        140000,0
 2004              270000,0        135000,0        130000,0        140000,0
 2005     1        100000,0        100000,0        100000,0        100000,0
    :     :               :               :               :               :
 2005    12        200000,0        200000,0        200000,0        200000,0
 2005             1452010,0        121000,8         70000,0        200000,0
 2006     1        160000,0        160000,0        160000,0        160000,0
 2006     2        140000,0        140000,0        140000,0        140000,0
 2006              300000,0        150000,0        140000,0        160000,0
                  2022010,0        126375,6         70000,0        200000,0

Es ist auch möglich, nur die Zwischenaggregate pro Jahr zu berechnen und das Aggregat über alles zu unterdrücken. Dazu braucht es aber die Klausel GROUPING SETS: Mit dieser können Sie gewünschten Gruppen explizit anfordern. GROUP BY ROLLUP (JAHR, MONAT) bedeutet das gleiche wie GROUP BY GROUPING SETS (JAHR, MONAT), (JAHR), (). Wir brauchen ein GROUPING SET pro Monat (JAHR, MONAT) und eins pro Jahr (JAHR). Die Gruppe "über alles" () brauchen wir nicht. 

select jahr, monat, sum(umsatz_eur), avg(umsatz_eur), min(umsatz_eur), max(umsatz_eur)
from verkauf group by grouping sets (jahr,monat), (jahr)
order by jahr, monat

 JAHR MONAT SUM(UMSATZ_EUR) AVG(UMSATZ_EUR) MIN(UMSATZ_EUR) MAX(UMSATZ_EUR)
----- ----- --------------- --------------- --------------- ---------------
 2004    11        130000,0        130000,0        130000,0        130000,0
 2004    12        140000,0        140000,0        140000,0        140000,0
 2004              270000,0        135000,0        130000,0        140000,0

 2005     1        100000,0        100000,0        100000,0        100000,0
    :     :               :               :               :               :
 2005    12        200000,0        200000,0        200000,0        200000,0
 2005             1452010,0        121000,8         70000,0        200000,0

 2006     1        160000,0        160000,0        160000,0        160000,0
 2006     2        140000,0        140000,0        140000,0        140000,0
 2006              300000,0        150000,0        140000,0        160000,0

Liegen noch mehr "Dimensionsspalten" vor, so lassen sich mit ROLLUP und den anderen GROUP BY-Klauseln CUBE und GROUPING SETS Aggregate für alle möglichen Spaltenkombinationen berechnen. Mehr dazu und weiterführende Links finden Sie in der Oracle-Dokumentation: SQL Language Reference.

Weitere Aggregatsfunktionen

Die verwendeten Aggregatsfunktionen SUM, AVG, MIN und MAX sind sicherlich die am häufigsten verwendeten. Aber die Oracle-Datenbank bietet noch mehr an: Im folgenden sind zwei Beispiele vorgestellt ...

Ab Oracle11g Release 2 können Zeichenketten mit der LISTAGG-Funktion zusammengefasst werden; das ist ein für den Entwickler sehr nützliches und häufig benötigtes Feature. 

select deptno, listagg(ename,',') within group (order by ename) emplist 
from emp 
group by deptno
/

DEPTNO EMPLIST
------ --------------------------------------------------
    10 ADAMS,BLAKE,FORD,KING,MILLER
    20 JONES,SCOTT,SMITH
    30 ALLEN,JAMES,MARTIN,TURNER,WARD
    40 CLARK

Die Funktionen zur einfachen linearen Regression zeigen, wie mächtig der Standardumfang der Datenbank bereits ist. Sie ermitteln den absoluten Betrag und die Steigung einer linearen Funktion, die anhand der Daten mit der "Methode der kleinsten Quadrate" gebildet wurde. 

select
  regr_intercept(umsatz_eur, werbung_tv_eur) as abs_betrag,
  regr_slope(umsatz_eur, werbung_tv_eur) as steigung
from verkauf
/

    ABS_BETRAG       STEIGUNG
-------------- --------------
       46341,7            6,9

Die Regressionsfunktion für den Umsatz anhand der Ausgaben für TV-Werbung lautet also: 

UMSATZ_EUR := 46341.7 + 6.9 * WERBUNG_TV_EUR

Und natürlich kann dies - direkt in der gleichen SQL-Abfrage - auch angewendet werden. So macht das folgende Beispiel (anhand des Regressionsmodells) eine Prognose für den Umsatz bei Ausgaben von 200.000 für TV-Werbung. 

with regr as (
 select
  regr_intercept(umsatz_eur, werbung_tv_eur) as abs_betrag,
  regr_slope(umsatz_eur, werbung_tv_eur) as steigung
 from verkauf
)
select abs_betrag + (steigung * 200000) 
from regr
/


ABS_BETRAG+(STEIGUNG*200000)
----------------------------
                   1416638,3

Abbildung 1 zeigt die Anwendung dieser SQL-Funktion im Zusammenspiel mit einem APEX-Diagramm. Die grüne Kurve stellt das Ergebnis der angewendeten Regressionsfunktion dar. Liegen konkrete Zahlen für den Umsatz vor, so kann man die "Prognose" mit den tatsächlichen Daten vergleichen. Neben REGR_SLOPE und REGR_INTERCEPT stehen noch andere SQL-Funktionen zur Verfügung, mit denen unter anderem statistische Werte zur Güte der gefundenen Funktion abgerufen werden können.

Regressionsfunktionen in einer APEX-Anwendung

Abbildung 1: Regressionsfunktionen in einer APEX-Anwendung

In der Oracle Dokumentation - SQL Language Reference finden Sie eine vollständige Übersicht über die vorhandenen Aggregatsfunktionen.

Eigene Aggregatsfunktionen erstellen

Wie in der Dokumentation erkennbar ist, stellt die Datenbank eine ganze Menge Aggregatsfunktionenen bereit - aber es fehlt auch was: So gibt es keine Aggregatsfunktion für das "Produkt". Man könnte sich nun zwar eine einfache PL/SQL-Funktion schreiben, die wäre aber nicht in jeder SQL-Abfrage im Zusammenspiel mit GROUP BY nutzbar. Doch auch hier bietet die Oracle-Datenbank Abhilfe: Hält man sich an ein bestimmtes Programmierschema, so kann man sich eigene Aggregatsfunktionen bauen - und diese Aggregatsfunktionen können genauso genutzt werden, wie die eingebauten Pendants. Das Programmierschema ist im kaum bekannten, aber dafür hochinteressanten Handbuch Data Cartridge Developers' Guide beschrieben. Einige fertige Implementierungen für benutzerdefinierte Aggregatsfunktionen finden Sie im Blog von Thomas Uhren und im Blog SQL und PL/SQL in Oracle.

Hat man, zum Beispiel, die Aggregatsfunktion AGG_PRODUCT eingespielt, so lässt sich diese ganz normal mit GROUP BY nutzen. Auch die beschriebenen GROUP BY-Klauseln ROLLUP, GROUPING SETS und CUBE können verwendet werden. 

select jahr, agg_product(zinssatz)
from zinsen_jahr 
group by rollup (jahr)

   JAHR AGG_PRODUCT(ZINSSATZ)
------- ---------------------
   2008      0,03000000000000
   2009      0,02050000000000
   2010      0,02400000000000
   2011      0,02750000000000
   2012      0,02450000000000
             0,00000000994455

Basiernd darauf ließen sich bspw. viele Kennzahlen aus der Finanzmathematik recht einfach in benutzerdefinierte Aggregatsfunktionen überführen.

Dynamische Aggregatsfenster: Analytische Funktionen

Das mit der GROUP-BY-Klausel definierte "Fenster", über welches die Aggregate gebildet werden, ist eher statischer Natür: Zur Abfragezeit werden die Daten in feste Abschnitte, eben die Gruppen, aufgeteilt. Manche Aufgaben, wie das Berechnen eines gleitenden Durchschnitts, erfordern jedoch mehr Möglichkeiten: Hier helfen die analytischen Varianten weiter - jede Aggregatsfunktion steht in einer analytischen Variante bereit - und darüber hinaus gibt es noch zusätzliche analytische Funktionen. Eine vollständige Übersicht findet sich wiederum in der Oracle Dokumentation.

Ein wichtiges Merkmal der analytischen Funktionen ist, dass die Aggregate für jede Zeile der Ergebnismenge berechnet werden - im Gegensatz zu den "klassischen" Aggregatsfunktionen, welche das Aggregat nur einmal pro Gruppe ausrechnen und für jede Gruppe nur noch eine Zeile ausgeben. Allen analytischen Funktionen ist die Klausel OVER gemein - in dieser wird das Aggregats-"Fenster" definiert (analytische Funktionen werden manchmal auch als Window-Funktionen) bezeichnet. Die OVER-Klausel besteht wiederum aus drei Teilen:

  • Mit PARTITION BY werden die Daten in Gruppen unterteilt. Dies ähnelt sehr stark dem klassischen GROUP BY
  • Mit ROWS BETWEEN wird das Aggregatsfenster über eine bestimmte Menge an Zeilen, ausgehend vom "über die Ergebnismenge laufenden Cursor" gebildet.
  • Mit ORDER BY wird die Sortierung innerhalb des Aggregatfensters festgelegt.

Diese doch eher theoretischen Beschreibungen werden nun anhand eines Beispiels deutlich gemacht. Über die Umsatzzahlen (UMSATZ_EUR) der Tabelle VERKAUF soll ein gleitender Durchschnitt gebildet werden. Die Periode soll dabei drei Monate sein - achten Sie auf das ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING. Diese Klausel legt fest, dass das Aggregatsfenster für jede Zeile immer von der Zeile davor bis zur Zeile danach gehen soll. Eine solche Klausel erfordert natürlich eine Sortierung - das wird mit dem ORDER BY innerhalb der OVER-Klausel erledigt. 

select 
 JAHR, 
 MONAT, 
 UMSATZ_EUR,
 AVG(UMSATZ_EUR) over (order by JAHR, MONAT rows between 1 preceding and 1 following) as GL_DS_UMSATZ
from VERKAUF
order by jahr, monat

      JAHR      MONAT UMSATZ_EUR GL_DS_UMSATZ
---------- ---------- ---------- ------------
      2009         11     130000   135000,000
      2009         12     140000   123333,333
      2010          1     100000   114333,333
         :          :          :            :
      2010         12     200000   185000,000
      2011          1              200000,000
      2011          2

Analog dazu bilden wir die laufende Summe - für jedes Jahr sollen die Umsatzzahlen fortlaufend aufsummiert werden; allerdings soll die Summierung im neuen Jahr neu beginnen. Dazu benötigen wir alle drei Elemente der OVER-Klausel wie folgt. 

select 
 JAHR, 
 MONAT, 
 UMSATZ_EUR,
 SUM(UMSATZ_EUR) over (partition by jahr order by MONAT rows between unbounded preceding and current row) as SUM_UMSATZ
from VERKAUF
order by jahr, monat
/

      JAHR      MONAT UMSATZ_EUR SUM_UMSATZ
---------- ---------- ---------- ----------
      2009         11     130000     130000
      2009         12     140000     270000

      2010          1     100000     100000
      2010          2     103000     203000
         :          :          :          :
      2010         12     200000    1452010

      2011          1      50000      50000
      2011          2     150000     200000

Zunächst werden die Daten mit der PARTITION BY-Klausel nach Jahren gruppiert. Jede Gruppe wird intern nach dem Monat sortiert und das "Aggregatsfenster" läuft vom Anfang (UNBOUNDED PRECEDING) bis zur aktuellen Zeile.

Für häufige Aufgabenstellungen gibt es eigene analytische Funktionen. So berechnet RATIO_TO_REPORT für jede Zeile den prozentualen Anteil an der Gesamtmenge. Angewendet auf das Umsatzbeispiel sähe die Abfrage wie folgt aus: 

select 
 JAHR, 
 MONAT, 
 UMSATZ_EUR,
 RATIO_TO_REPORT(UMSATZ_EUR) over (partition by jahr) * 100 as ANTEIL_UMSATZ
from VERKAUF
order by jahr, monat
/

      JAHR      MONAT UMSATZ_EUR ANTEIL_UMSATZ
---------- ---------- ---------- -------------
      2009         11     130000         48,15
      2009         12     140000         51,85

      2010          1     100000         06,89
         :          :          :             :
      2010         12     200000         13,77

      2011          1      50000         25,00
      2011          2     150000         75,00

Analytische Funktionen lassen sich auch mit den klassichen Pendants kombinieren. So wäre es nett, wenn diesem Bericht noch eine Zeile mit den Summen für das jeweilige Jahr hinzugefügt würde. Das geht mit der am Anfang besprochenen Funktion ROLLUP. 

with zahlen as (
 select 
  JAHR, 
  MONAT, 
  UMSATZ_EUR,
  RATIO_TO_REPORT(UMSATZ_EUR) over (partition by jahr) * 100 as ANTEIL_UMSATZ
 from VERKAUF
)
select
 jahr,
 monat,
 sum(umsatz_eur) umsatz_eur,
 sum(anteil_umsatz) anteil_umsatz
from zahlen
group by grouping sets(jahr, monat), (jahr)
order by jahr, monat
/

      JAHR      MONAT UMSATZ_EUR ANTEIL_UMSATZ
---------- ---------- ---------- -------------
      2009         11     130000         48,15
      2009         12     140000         51,85
      2009                270000        100,00

      2010          1     100000         06,89
      2010          2     103000         07,09
         :          :          :             :
      2010         12     200000         13,77
      2010               1452010        100,00

      2011          1      50000         25,00
      2011          2     150000         75,00
      2011                200000        100,00

Hätten Sie vorher gedacht, dass ein solcher Report nur mit SQL möglich ist oder hätten Sie eine PL/SQL Table Function geschrieben ...?

Fazit

Aggregatsfunktionen sind in der Oracle Datenbank mehr als nur einfaches SUM, AVG und GROUP BY. Mit den GROUP BY-Klauseln ROLLUP, CUBE und GROUPING SETS lassen sich sehr elegant zusätzliche Aggregate entlang der vorhandenen "Dimensionen" berechnen. Analytische Funktionen erlauben das sehr flexible Bilden von Aggregatsfenstern. Und wenn das alles nicht reicht, können sogar eigene Aggregatsfunktionen erstellt werden. Besser geht es eigentlich nicht.

Zurück zur Community-Seite