Pivot – Zeilen in Spalten wandeln

Daten drehen – English „pivot“ – ist eine sehr häufige Problemstellung, die in verschiedenen Varianten auftritt. Der Kern der Anforderung ist jeweils, dass Daten die in verschiedenen Zeilen eine Tabelle gespeichert sind, als Spalten einer Zeile dargestellt werden sollen.

Diese Anforderung tritt bei der Erstellung von Berichten besonders häufig auf. Die folgenden Ausführungen basieren daher auf einer Abfrage, die monatliche Verkaufserlöse ausgibt:

SELECT EXTRACT(YEAR  FROM invoice_date) year
     , EXTRACT(MONTH FROM invoice_date) month
     , SUM(revenue)                     revenue
  FROM invoices
 GROUP BY EXTRACT(YEAR  FROM invoice_date)
        , EXTRACT(MONTH FROM invoice_date)

Diese Abfrage liefert die Daten rein vertikal – also einen Wert pro Zeile. Oft ist jedoch eine andere Darstellung gewünscht: zum Beispiel nur eine Zeile pro Jahr, dafür eine eigene Spalte für jeden Monat. Anders gesagt sollen die Zeilen eines Jahres in Spalten gewandelt werden.

Der erste Schritt zur Umsetzung dieser Anforderung ist, das Monat aus den group by und select-Klauseln zu entfernen, damit das Ergebnis nur noch eine Zeile pro Jahr enthält:

SELECT EXTRACT(YEAR FROM invoice_date) year
     , SUM(revenue)                    total_revenue
  FROM invoices
 GROUP BY EXTRACT(YEAR FROM invoice_date)

Natürlich liefert diese Abfrage keine monatliche Aufschlüsselung mehr. Dennoch ist dieser Schritt notwendig, um die gewünschte Reduktion auf eine Zeile pro Jahr zu erreichen.

Der nächste Schritt ist, zwölf Spalten zu definieren, die jeweils nur die Verkaufserlöse eines Monates aufsummieren. Um zum Beispiel den Umsatz für Jänner zu ermitteln, soll der Ausdruck sum(revenue) nur noch die Einträge aus Jänner berücksichtigen. Dafür bietet der SQL-Standard die filter-Klausel:

sum(revenue) FILTER (WHERE EXTRACT(MONTH FROM invoice_date) = 1)

Die filter-Klausel beschränkt die aggregierten Zeilen auf jene, die die Bedingung in der Klammer erfüllen. In diesem Beispiel auf die Rechnungen aus Jänner. Die Umsätze der anderen Monate können natürlich analog ermittelt werden.

Um die Abfrage übersichtlicher zu gestalten, kann man die extract-Ausdrücke natürlich zentralisieren. Dafür könnte man zum Beispiel generierte Spalten oder einen View verwenden, wenn andere Abfragen dieselben extract-Ausdrücke benötigen. Für dieses Beispiel soll es genügen, die extract-Ausdrücke innerhalb der Abfrage zu zentralisieren – entweder mit der with-Klausel oder als Unterabfrage:

SELECT year
     , SUM(revenue) FILTER (WHERE month =  1) jan_revenue
     , SUM(revenue) FILTER (WHERE month =  2) feb_revenue
     ...
     , SUM(revenue) FILTER (WHERE month = 12) dec_revenue
  FROM (SELECT invoices.*
             , EXTRACT(YEAR  FROM invoice_date) year
             , EXTRACT(MONTH FROM invoice_date) month
          FROM invoices 
       ) invoices
 GROUP BY year

Konforme Alternativen

Obwohl die filter-Klausel bereits mit SQL:2003 eingeführt wurde, wird sie bis dato kaum unterstützt. Das ist jedoch kein großes Problem, da sich die filter-Klausel sehr leicht mit case emulieren lässt. Der Trick ist, dass man die Werte jener Zeilen, die die Filterbedingung nicht erfüllen, durch einen neutralen Wert ersetzt, der das Ergebnis der Aggregation nicht ändert. Dafür bietet sich null an, weil es bei allen Aggregatfunktionen neutral wirkt – auch bei avg. Außerdem ist else null die Default-Klausel für case-Ausdrücke ohne else-Klausel – es genügt also, die else-Klausel einfach wegzulassen.

SELECT year
     , SUM(CASE WHEN month =  1 THEN revenue END) jan_revenue
     , SUM(CASE WHEN month =  2 THEN revenue END) feb_revenue
       ...
     , SUM(CASE WHEN month = 12 THEN revenue END) dec_revenue
  FROM (SELECT invoices.*
             , EXTRACT(YEAR  FROM invoice_date) year
             , EXTRACT(MONTH FROM invoice_date) month
          FROM invoices 
       ) invoices
 GROUP BY year

Der Ausdruck CASE WHEN month = 1 THEN revenue END liefert den Umsatz für Rechnungen aus Jänner. Für andere Rechnungen wird durch die implizite else null-Klausel der null-Wert geliefert, der das Ergebnis der Aggregierung mit sum nicht ändert. Mehr dazu in „Null in Aggregatfunktionen (count, sum, …)“ und „konforme Alternativen zu filter“.

Spezialfall EAV

Die größte Herausforderung des „Pivot“-Problemes ist, es zu erkennen, wenn man einem begegnet. Das trifft insbesondere auf dem Umgang mit dem sogenannten Entity-Attribute-Value (EAV) Modell zu: es sieht nicht aus wie ein Pivot-Problem, lässt sich aber dennoch mit der selben Methode lösen.

Das EAV-Modell treibt die Normalisierung auf die Spitze und verwendet Spalten nicht mehr im herkömmlichen Sinn. Stattdessen wird jeder Wert (Value) in einer eignen Zeile abgelegt. Zusätzlich wird gespeichert, welches Attribute dieser Wert representiert und zu welchem Datensatz (Entity) er gehört. Letzten Endes kann man in einer dreispaltigen Tabelle alles abbilden, ohne die Tabellendefinition ändern zu müssen. Das EAV-Modell wird daher oft genutzt, um dynamische Attribute abzuspeichern.

Das EAV-Modells bringt aber auch Nachteile mit sich: es ist damit zum Beispiel kaum möglich, Constraints zur Validierung der Daten einzusetzen. Besonders auffällig ist aber, dass eine spaltenweise Darstellung der Attribute oft durch einem (outer) Join pro Attribut umgesetzt wird. Das ist nicht nur mühsam umzusetzen, sondern auch noch sehr langsam in der Ausführung – ein echtes Anti-Pattern.

Die spaltenweise Darstellung der Attribute entspricht dem Pivot-Problem in seiner reinsten Form. Die Lösung erfolgt daher wieder in zwei Schritten: (1) Zeilen mit group by auf eine Zeile pro Datensatz reduzieren; (2) das gewünschte Attribut mit filter oder case pro Spalte auswählen.

SELECT submission_id
     , MAX(CASE WHEN attribute='name'    THEN value END) name
     , MAX(CASE WHEN attribute='email'   THEN value END) email
     , MAX(CASE WHEN attribute='website' THEN value END) website
  FROM form_submissions
 GROUP BY submission_id

Dabei ist die Verwendung der Funktion max zu beachten: Sie wird benötigt, um die Zeilen der Gruppe (alle Attribute) auf einen einzelnen Wert zu reduzieren. Dies ist eine rein Syntaktische Anforderung, die unabhängig von der tatsächlichen Anzahl der aggregierten Zeilen besteht.

Damit man trotz Aggregierung den ursprünglichen Wert jedes Attributes erhält, ist darauf zu achten, dass die entsprechende Filterlogik (case oder filter) maximal einen nicht-null Wert durchlässt. Im oberen Beispiel ist es also essentiell, dass jedes der genannten Attribute (name, email, website) nur einmal pro sumission_id vorkommt. Wenn es mehrere gibt, liefert die Abfrage nur einen dieser Werte zurück.

Die Voraussetzung, dass jedes Attribut nur einmal vorkommt, erzwingt man am besten mit einem Unique-Constraint0. Alternativ kann man in der Abfrage zählen, wie oft jedes Attribut pro Gruppe vorkommt. Einfach mit count(*) und dem entsprechenden case-Ausdruck (oder filter-Klausel) zählen. Das Ergebnis kann in der Anwendung oder in einer having-Klausel geprüft werden: having count(*) filter (...) <= 1.

Wenn die Voraussetzung erfüllt ist, und die Aggregatfunktion immer nur auf einen einzelnen nicht-null Wert angewandt wird, liefert jede Aggregatfunktion genau diesen Wert zurück. Min und max haben zusätzlich den Vorteil, dass sie auch auf Zeichenketten (char, varchar, …) anwendbar sind.

Grenzen der Umsetzbarkeit

SQL ist eine statisch typisierte Sprache: Die Abfrage muss die Ergebnisspalten im Vorfeld aufzählen. Um dynamische oder unbekannte Attribute zu transponieren kann man Multisets oder Dokumenttypen (XML, JSON) als assoziatives Datenfeld verwenden. Siehe „alternativen zu listagg: Dokumente“.

Kompatibilität

1. Das filter_plugin eines Fremdanbieters verwendet reguläre Ausdrücke, um Filter als Case umzuschreiben

Proprietäre Alternativen

pivot und unpivot (SQL Server, Oracle)

SQL Server unterstützt seit Version 2005 die pivot und unpivot-Klauseln (Dokumentation). Diese sind in der Oracle Datenbank ab Version 11g ebenfalls Verfügbar (Dokumentation).

model-Klausel (Oracle)

Die proprietäre model-Klausel, die mit der Oracle-Datenbank Version 10g eingeführt wurde, kann auch zum Lösen des Pivot-Problems genutzt werden (Dokumentation).

crosstab Tabellenfunktionen (PostgreSQL)

Die PostgreSQL-Datenbank wird mit einer Familie von crosstab Tabellenfunktionen ausgeliefert, um das Pivot-Problem zu lösen (Dokumentation).