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
“.
Hinweis in eigener Sache
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
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
- Das
filter_plugin
eines Fremdanbieters verwendet reguläre Ausdrücke, umFilter
alsCase
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).