Exceptionfault

Ein mächtiges Feature im Bereich “Reporting” ist das sogenannte Pivoting (http://de.wikipedia.org/wiki/Pivot-Tabelle). Was bisher ein schlagkräftiges Argument für Excel war, oder umständlich im Client programmiert werden musste ist nun erstmals als neue SQL Erweiterung in Oracle 11g integriert. Wie mächtig und praxistauglich die Funktion wirklich ist soll ein kleines Beispiel zeigen.

Zum Test dient eine einfache Tabelle mit Umsatzdaten imaginärer Kunden die mit 50000 Zeilen willkürlichen Daten gefüllt wird.

create table umsatz
(
   kundennr   number(10)   not null,
   datum      date         not null,
   umsatz     number(10)   not null
);

begin
   for i in 1 .. 50000 loop
      insert into umsatz values (
         dbms_random.value( 1, 100 ),
         sysdate - dbms_random.value( 1, 365 ),
         dbms_random.value( 200, 1000 ) );
   end loop
   commit;
end;
/

Insgesamt werden Kunden mit einer Nummer zwischen 1 und 100 erzeugt, die an zufälligen Tagen zwischen gestern und gestern – 1 Jahr einen Umsatz von 200 bis 1000 Euro generieren. Unser Ziel ist eine Quartalsauswertung des Umsatzes. Hierfür dient uns z.B. das folgende Statement:

SELECT   to_char(datum, 'YYYY-Q') quarter,
         sum(umsatz)
FROM     umsatz
GROUP    BY to_char(datum, 'YYYY-Q')
ORDER    BY 1;

QUARTE     SUM(UMSATZ)
------ ---------------
2008-1         6192186
2008-2         7383247
2008-3         7619613
2008-4         7617330
2009-1         1125020

Natürlich ist die Ausgabe brauchbar und übersichtlich. Jedoch schon wenn im Client jedes Jahr in einer Zeile mit Quartalen dargestellt werden soll, muss das Ergebnis aus der Datenbank gedreht und weiterverarbeitet werden. Das kostet Entwicklungszeit, Performance bei der Ausführung und erhöhten Aufwand beim Test. Abhilfe schafft die neue PIVOT Klausel.

SELECT *
FROM   (
   SELECT   to_char(datum, 'YYYY-Q') quarter, umsatz
   FROM     umsatz
)
PIVOT
(
   SUM( UMSATZ ) FOR QUARTER IN ( '2008-1', '2008-2',
                                  '2008-3', '2008-4', '2009-1' )
)

 '2008-1'  '2008-2'  '2008-3'  '2008-4'  '2009-1'
--------- --------- --------- --------- ---------
  6192186   7383247   7619613   7617330   1125020

Sieht hübsch aus, ist aber nicht ganz das erwünschte Ergebnis. Natürlich liegt es daran, dass Jahr und Quartal in einer Spalte zusammengefasst sind. Daher wird auch die IN Klausel so unschön, da hier fixe Werte eingetragen werden müssen. Gleich Vorweg: Die PIVOT Klausel setzt voraus, dass die Anzahl der zurückgelieferten Spalten fix und bekannt ist. Ist dies nicht der Fall, und möchte ich eine Spaltenliste z.B. per Subselect generieren, dann hilft das Schlüsselwort “XML” weiter. In diesem Fall findet die Ausgabe dann allerdings auch nur in XML statt:

SELECT *
FROM   (
   SELECT   to_char(datum, 'YYYY-Q') quarter, umsatz
   FROM     umsatz
)
PIVOT XML
(
   SUM( UMSATZ ) FOR QUARTER IN (
      SELECT DISTINCT to_char(datum, 'YYYY-Q') FROM UMSATZ
   )
)

QUARTER_XML
---------------------------------------------------
<PivotSet>
  <item>
    <column name = "QUARTER">2008-1</column>
    <column name = "SUM(UMSATZ)">6192186</column>
  </item>
  <item>
    <column name = "QUARTER">2008-2</column>
    <column name = "SUM(UMSATZ)">7383247</column>
  </item>
  ...

Zurück zu unserem ersten Versuch. Durch die Trennung des Jahres und des Quartals kann auch die IN Liste fix mit den 4 Quartalen angegeben werden:

SELECT * FROM
(
   SELECT extract(year from datum) year,
          to_number(to_char(datum, 'Q')) quarter,
          umsatz
   FROM   umsatz
)
PIVOT
(
   sum(umsatz) for quarter in (1,2,3,4)
)
ORDER BY year;

 YEAR        1        2        3        4
----- -------- --------  ------- --------
 2008  6192186  7383247  7619613  7617330
 2009  1125020

Auch analytische Funktionen lassen sich wunderbar mit der PIVOT Klausel verbinden, egal ob nun zuvor oder danach angewandt. Das folgende Beispiel errechnet zum Beispiel die Jahressumme als zusätzliche Spalte per SUM() OVER() Funktion. Natürlich hätte dafür auch einfach eine “virtuelle” Spalte in der äußeren SELECT Liste genügt, die eine Summe über Spalten 1 bis 4 bildet. Mit Hilfe dieser Methode wird in diesem Beispiel ein durchschnittlicher Quartalsumsatz pro Jahr errechnet. Außerdem ist gut zu erkennen, wie den PIVOT Spalten durch die IN Liste sprechende Namen vergeben werden können, um sie im Äußeren SELECT wieder anzusprechen.

SELECT year,
       jahressumme,
       qrt1,
       qrt2,
       qrt3,
       qrt4,
       (nvl(qrt1,0) + nvl(qrt2,0) +
        nvl(qrt3,0) + nvl(qrt4,0))/4 as durchschnitt
FROM
(
   SELECT extract(year from datum) year,
          sum(umsatz) over (partition by extract(year from datum)) as jahressumme,
          to_number(to_char(datum, 'Q')) quarter,
          umsatz
   FROM   umsatz
)
PIVOT
(
   sum(umsatz) for quarter in (1 as qrt1,2 as qrt2,3 as qrt3,4 as qrt4)
)
ORDER BY year;

 YEAR JAHRESSUMME     QRT1     QRT2     QRT3     QRT4  DURCHSCHNITT
----- ----------- -------- -------- -------- -------- -------------
 2008    28812376  6192186  7383247  7619613  7617330       7203094
 2009     1125020  1125020                                   281255