Datenanalyse in SQL: Pivot-Tabellen

Wer als Data Scientist etwas auf sich hält, arbeitet heute meist mit Python oder R, um Daten zu analysieren. Was oft übersehen wird, ist die Tatsache, dass viele gängige Datenanalysen auch ganz klassisch unter Verwendung von SQL in relationalen Datenbanken möglich sind, ohne dass ein Export in andere Systeme notwendig wäre. Natürlich stehen nicht immer alle gewünschten Funktionen "out of the box" zur Verfügung, doch gewusst wie, lassen sich auch aus einer Datenbank viele interessante Analysen herauskitzeln.

Ein spannendes Beispiel dafür sind sicher die sogenannten Pivot-Tabellen (engl. to pivot = schwenken, drehen), die vor allem in Tabellenkalkulationen gerne genutzt werden, um Daten zu analysieren. Ich möchte im Folgenden kurz beschreiben, wie sich Pivot-Tabellen in SQL (getestet auf MySQL 5.7) umsetzen lassen. Alle notwendigen SQL-Befehle sind im Verlauf des Posts angegeben und können direkt in einen SQL-Client kopiert und ausprobiert werden.

Beginnen wir mit einer einfachen Tabelle, die einige Daten zu Mitarbeitern vorhalten kann:

  create table Mitarbeiter (
     id int primary key auto_increment, 
     Name varchar(20), 
     Geschlecht char(1),
     Abteilung varchar(10), 
     Standort varchar(20), 
     Gehalt int
  );

Befüllen wir sie im Anschluss mit einigen Beispieldaten. Die dafür notwendigen "Inserts" stehen am Ende des Posts, damit sie hier den Lesefluss nicht unnötig stören. 

Danach können wir uns den Inhalt der Tabelle (DB-Fiddle) wie gewohnt ausgegeben lassen:

  select * from Mitarbeiter;

+----+-----------------------+------------+-----------+------------+--------+

| id | Name                  | Geschlecht | Abteilung | Standort   | Gehalt |
+----+-----------------------+------------+-----------+------------+--------+
|  1 | Müller                | m          | DEV       | Mannheim   |  70000 |
|  2 | Mayer                 | m          | OPS       | Mannheim   |  65000 |
|  3 | Schulze               | m          | DEV       | Heidelberg |  60000 |
|  4 | Norris                | m          | MNMT      | Mannheim   | 100000 |
|  5 | Heinz                 | w          | TEST      | Heidelberg |  75000 |
|  6 | Steiger               | m          | TEST      | Mannheim   |  60000 |
|  7 | Müller-Lüdenscheidt   | m          | DEV       | Frankfurt  |  68000 |
|  8 | Young                 | m          | TEST      | Frankfurt  |  63000 |
|  9 | Stein                 | m          | MNMT      | Mannheim   |  80000 |
| 10 | Pape                  | w          | OPS       | Frankfurt  |  70000 |

| 11 | Müller                | w          | DEV       | Frankfurt  |  70000 |
+----+-----------------------+------------+-----------+------------+--------+

Histogramm

Eine erste interessante statistische Analyse könnte die Erstellung eines (textuellen) Histogramms sein, das uns die Anzahl der Mitarbeiter pro Abteilung ermittelt. Dies funktioniert in SQL noch ohne allzu große Klimmzüge, in dem wir nach der Abteilung gruppieren und die Anzahl der jeweiligen Einträge zählen lassen. Konkret also durch Verwendung von "group by" und "count":

  select Abteilung, count(*) as Anzahl from Mitarbeiter
                                            group by Abteilung;

+-----------+--------+

| Abteilung | Anzahl |

+-----------+--------+
| DEV       |      4 |
| MNMT      |      2 |
| OPS       |      2 |
| TEST      |      3 |
+-----------+--------+

Analog ließe sich eine solche Anfrage natürlich auch für die Standorte oder die Geschlechter erstellen. 

Pivot-Tabelle

Doch wie gehen wir vor, wenn wir beispielsweise die Verteilung der männlichen und weiblichen Mitarbeiter pro Abteilung ermitteln möchten? Dazu müssten die Einträge in der Spalte Geschlecht gezählt und "m" und "w" selbst zu Spaltenüberschriften werden, während die Namen der Abteilungen zu Zeilenbenennungen werden, wie in der folgenden Pivot-Tabelle gezeigt:

 +-----------+---+---+--------+
| Abteilung | m | w | Gesamt |
+-----------+---+---+--------+
| DEV       | 3 | 1 |      4 |
| MNMT      | 2 | 0 |      2 |
| OPS       | 1 | 1 |      2 |
| TEST      | 2 | 1 |      3 |
+-----------+---+---+--------+

Dieses Vorgehen verdeutlicht noch einmal die Herkunft des Namens "Pivot" und wirft vermutlich zeitgleich die Frage auf, ob und wie sich eine solche Tabelle in SQL erzeugen lässt? 

Wie wir gleich sehen werden, ist das gar nicht so kompliziert. Es reicht die geschickte Verwendung einer if-Abfrage, die nicht nur in Programmiersprachen, sondern eben auch in SQL zur Verfügung steht:

  select Abteilung, 
         count(if (Geschlecht = 'm', id, null)) as m, 
         count(if (Geschlecht = 'w', id, null)) as w, 
         count(*) as Gesamt 
  from Mitarbeiter group by Abteilung;

Wichtig ist wiederum das "group by" am Ende, um die Zuordnung der Werte zu den Abteilungen zu erhalten. Die farbliche Codierung soll verdeutlichen, welcher Teil der SQL-Abfrage für welches Ergebnis in der vorherigen Tabelle verantwortlich ist. 

Kurz gesagt prüft die Datenbank also, ob der gerade betrachtete Mitarbeiter männlich oder weiblich ist, um ihn oder sie bei der jeweiligen Abteilung zu zählen. Da Null-Werte von der Count-Funktion ignoriert werden, besteht der Kniff schlicht darin, über das If ein Null für den Zähler bei männlich zurückzugeben, wenn es sich um eine Mitarbeiterin handelt und umgekehrt.

Komplexere Pivot-Tabellen?

Diese Vorgehensweise ist für zwei vorab bekannte und sich nicht ändernde Geschlechter sicher praktisch und - einmal verstanden - auch nicht zu kompliziert in der Anwendung. Was aber, wenn wir uns die Verteilung der Mitarbeiter pro Standort wie folgt auflisten möchten? 

+-----------+-----------+------------+----------+--------+
| Abteilung | Frankfurt | Heidelberg | Mannheim | Gesamt |
+-----------+-----------+------------+----------+--------+
| DEV       |         2 |          1 |        1 |      4 |
| MNMT      |         0 |          0 |        2 |      2 |
| OPS       |         1 |          0 |        1 |      2 |
| TEST      |         1 |          1 |        1 |      3 |
+-----------+-----------+------------+----------+--------+

Momentan enthalten unsere Datensätze zwar nur drei verschiedene Standorte und diese ließen sich zur Not noch per Hand in eine Query schreiben, doch das wird mit zunehmender Zahl der Standorte unbestreitbar immer mühseliger.Vor allem wenn wir vorab vielleicht gar nicht wissen, welche Standorte in unseren Daten überhaupt vorkommen.

Variablen

Für diese Herausforderung bietet SQL abermals eine elegantere Lösung: Pivot-Tabellen lassen sich nämlich auch mit Hilfe von Variablen und Prepared Statements ohne allzu großen Aufwand dynamisch erzeugen. Daher zunächst zwei Sätze zu Variablen: diese können beliebige primitive Datentypen (also z.B. Zahlen oder Zeichenketten) aufnehmen und werden durch ein vorangestelltes @ gekennzeichnet. Definiert und belegt werden sie wie folgt:

  set @answer = 42;

Ihr Inhalt kann über ein einfaches Select ausgegeben werden oder auch an passender Stelle innerhalb eines "select ... from ... where":

  select @answer;

+---------+
| @answer |
+---------+
|      42 |
+---------+

Es ist zudem möglich, die Ergebnisse von Abfragen, die einen Einzelwert (also keine Tabelle mit mehreren Spalten) zurückliefern, per "into" in eine Variable zu speichern:

  select count(*) from Mitarbeiter into @rows;

Prepared Statements

Unter Verwendung von Variablen und sogenannten Prepared Statements können wir problemlos auch eine SQL-Abfrage zusammenzubauen, sie speichern und sie erst bei Bedarf ausführen. Auch das ist SQL-typisch sehr pragmatisch machbar:

  set @var =  'select * from Mitarbeiter;'; 

  prepare stmt from @var;

  execute stmt;

Dynamische Pivot-Tabellen

Zusammengemischt können diese Zutaten verwendet werden, um Pivot-Tabellen mit vielen Spalten dynamisch zu erzeugen. Das funktioniert mit der Concat-Funktion zur Verkettung von Zeichenketten wie folgt:

  select group_concat(
    distinct concat('count(
      if (Standort=''', Standort, ''', id, null)) as ', Standort)) 
  from Mitarbeiter into @ifs;

Betrachten wir uns den Inhalt der erzeugten Variable, erkennen wir, dass dieses Statement die "count-if"-Statements der Pivot-Tabelle für jeden Standort erzeugt und diese über die group_concat-Funktion wiederum miteinander verkettet:

  count(if (Standort='Frankfurt', id, null)) as Frankfurt,
  count(if (Standort='Heidelberg', id, null)) as Heidelberg,
  count(if (Standort='Mannheim', id, null)) as Mannheim

Zu beachten ist im grün markierten Teil des Statements noch die Verwendungen der Anführungszeichen: zwei ' hintereinander "escapen" ein Anführungzeichen, das in der Ergebniszeichenkette benötigt wird, das dritte ' schließt den String an dieser Stelle ab. D.h. die beiden fett markierten Standorte befinden sich außerhalb der Anführungszeichen und werden iterativ mit jedem Standortnamen befüllt. Entfernt man alle Concat-Operationen wird also nur die folgende einfache Query ausgeführt:

  select distinct Standort from Mitarbeiter;

Nun muss zum Abschluss die gerade in @ifs gespeicherte Zeichenkette in den Rest des Statements zur Erzeugung der Pivot-Tabelle eingesetzt werden...

  set @piv = concat(
                'select Abteilung,', @ifs, ', count(*) as Gesamt
                 from Mitarbeiter group by Abteilung;'
  );

... sowie das Prepared Statement erzeugt und ausgeführt werden:

  prepare stmt from @piv;
  execute stmt;

Das ergibt die oben bereits gezeigte Pivot-Tabelle mit einer Aufstellung der Mitarbeiter pro Standort:

+-----------+-----------+------------+----------+--------+
| Abteilung | Frankfurt | Heidelberg | Mannheim | Gesamt |
+-----------+-----------+------------+----------+--------+
| DEV       |         2 |          1 |        1 |      4 |
| MNMT      |         0 |          0 |        2 |      2 |
| OPS       |         1 |          0 |        1 |      2 |
| TEST      |         1 |          1 |        1 |      3 |
+-----------+-----------+------------+----------+--------+ 

Zum Abschluss sei noch eine etwas spannendere Analyse vorgestellt: um das Durchschnittsgehalt der Mitarbeiter pro Standort und Abteilung zu erhalten, müssten wir unsere Statements von oben nur an drei Stellen wie folgt abändern:

  select group_concat(
    distinct concat('avg(
      if (Standort=''', Standort, ''', Gehalt, null)) as ', Standort)) 
  from Mitarbeiter into @ifs;

und:

  set @piv = concat(
      'select Abteilung,', @ifs, ', avg(Gehalt) as Gesamt
       from Mitarbeiter group by Abteilung;'
  );


Mit ...

  prepare stmt from @piv;
  execute stmt;

... ergibt sich abschließend die folgende Tabelle mit den Durchschnittsgehältern pro Standort, und das ganz ohne Excel, R oder Python:

+-----------+------------+------------+------------+------------+
| Abteilung | Frankfurt  | Heidelberg | Mannheim   | Gesamt     |
+-----------+------------+------------+------------+------------+
| DEV       | 69000.0000 | 60000.0000 | 70000.0000 | 67000.0000 |
| MNMT      |       NULL |       NULL | 90000.0000 | 90000.0000 |
| OPS       | 70000.0000 |       NULL | 65000.0000 | 67500.0000 |
| TEST      | 63000.0000 | 75000.0000 | 60000.0000 | 66000.0000 |
+-----------+------------+------------+------------+------------+

Verwendete Beispiel-Daten:

insert into Mitarbeiter values (null,'Müller','m','DEV','Mannheim',70000);

insert into Mitarbeiter values (null,'Mayer','m','OPS','Mannheim',65000);

insert into Mitarbeiter values (null,'Schulze','m','DEV','Heidelberg',60000);

insert into Mitarbeiter values (null,'Norris','m','MNMT','Mannheim',100000);

insert into Mitarbeiter values (null,'Heinz','w','TEST','Heidelberg',75000);

insert into Mitarbeiter values (null,'Steiger','m','TEST','Mannheim',60000); 

insert into Mitarbeiter values (null,'Müller-Lüdenscheidt','m','DEV','Frankfurt',68000);

insert into Mitarbeiter values (null,'Young','m','TEST','Frankfurt',63000);

insert into Mitarbeiter values (null,'Stein','m','MNMT','Mannheim',80000);

insert into Mitarbeiter values (null,'Pape','w','OPS','Frankfurt',70000);

insert into Mitarbeiter values (null,'Müller','w','DEV','Frankfurt',70000);