Pivot-Tabellen und unsere Diskrepanzen

TL; DR. Pivot-Tabellen sollten weder ein Standard für das Abbilden von Erkenntnissen sein, noch als erste Anlaufstelle der Informationsvermittlung gelten.

Es ist ein ewiger Kampf. Die Menge an Daten wächst und plötzlich sind analytische Denkfähigkeit und ein generelles technisches Verständnis von Abfragesprachen wie SQL eine Mindestanforderung, um aus dem ganzen Haufen an Daten sinnvolle Schlüsse oder erste Erkenntnisse zu ziehen. Unter dem Deckmantel von Business Intelligence und Business Analytics verbergen sich derart viele Fachbereiche und Disziplinen, dass diese Begriffe kaum klar differenziert werden können oder in einen Topf geworfen werden sollten, da es ein typisches Buzz Word ist, genauer genommen schlichtweg irgendwas mit Daten. Es müssen nicht immer statistische oder hochkomplexe mathematische Modelle oder Algorithmen angewandt werden, manchmal reicht eine Summe auf eine gefilterte Ansicht und alle sind glücklich und konnten die relevanten Schlüsse daraus ziehen. Die Komplexität liegt somit in der Formulierung der Fragestellung und diese setzt voraus, dass du genau formulieren kannst, was du wissen willst. Häufig kommen wir aber an einen Punkt, dass wir nicht wissen, was wir wissen wollen oder sich die Frage erst aus dem erblicken der Antwort ergibt. Das gesamte Thema ist so weit gefasst, dass ich es nicht in einem Artikel durchgehen werde. Ein Sachverhalt ist jedoch besonders zu untermalen. Die Darstellung der Ergebnisse ist einer der wichtigsten Aspekte, denn leider sind Entscheider außerhalb der großen Technologiekonzerne aus der Übung oder haben den Umgang mit den Datenmengen nie gelernt und haben durch das Design der Karrierestufen oder dem mangelndem Fortschritt der Digitalisierung der eigenen Firma keine Gelegenheit gehabt, einen Fokus dafür zu entwickeln. Das führt dazu, dass bereits Zwischenergebnisse menschlich lesbar gemacht werden müssen, da das Verständnis für die maschinelle Darstellung häufig nicht dem Drang der einfachen, verständlichen Darstellung genügt. Bevor sich jemand hier angegriffen fühlt, gehe ich auf meine persönliche Diskrepanzen mit einem Darstellungswerkzeug ein, nämlich den Pivot-Tabellen.

Das erste Mal, dass ich einer Pivot-Tabelle begegnet bin und allgemein Datenverarbeitung in Excel betreiben musste, war beim Eintritt in die Arbeitswelt. Schnell wurde mir klar, dass das in einem großen Teil der Wirtschaft, insbesondere bei Beratungsunternehmen ein häufig verwendetes Werkzeug darstellt. Das klassische Beispiel für eine Pivot-Tabelle lässt sich einfach darstellen.

Wie immer ist das ein sehr vereinfachtes Beispiel und alle Erkenntnisse, die du aus der Pivot-Tabelle ablesen könntest, siehst du bereits in den Rohdaten. Meistens beschränken sich die Rohdaten nicht auf ein paar Dutzend Zeilen, sondern bewegen sich beispielsweise bei meinem aktuellen Steckenpferd in die Millionen. Wenn du dir das länger überlegst, kommst du bereits sehr schnell in diese Größenordnungen, denn angenommen du betrachtest 300.000 Menschen und ihre Top 5 Lieblingsspeisen und willst daraus erschließen, was du in deiner Restaurantkette als nächstes anbietest. Dann kannst du zwar über die 1,5 Millionen Zeilen summieren, jedoch lässt sich in einer Pivot-Tabelle zum Beispiel zusätzlich ablesen, ob gewisse Gerichte immer zeitgleich die Lieblingsgerichte zu sein scheinen und das ermöglicht dir weiterführende Strategien zu entwickeln. Diese Frage kannst du dir jedoch auch ohne Pivot-Darstellung beantworten, wenn du diese Frage bereits vorher formulierst und deine Abfrage entsprechend stellst. An dieser Stelle kommen die ersten beiden Punkte auf, warum ich kein großer Fan von Pivot-Tabellen bin.

1. Pivot-Tabellen werden erstellt um relevante Informationen hervorzuheben oder zu präsentieren, da die Fragestellung schwammig formuliert ist und der Ansatz nach einer Antwort zu suchen und im Nachhinein die Frage nachträglich anzupassen, bevorzugt wird. Mindeststandards für Studiendesigns diverser statistisch angehauchter Wissenschaftsbereiche empfehlen jedoch die Fragestellung und eine Hypothese vorab festzulegen, um zu überprüfen, ob sie stimmt oder nicht. Wenn keine Hypothese formuliert werden kann, musst du dir die Daten anderweitig visualisieren oder gar nicht-parametrische Methoden anwenden, um Erkenntnisse zu gewinnen. Eine Pivot-Tabelle scheint mir gänzlich ungeeignet dafür, womit ich zum zweiten Punkt komme.
2. Pivot-Tabellen werden als Darstellungsform tabellarischer Daten verwendet, sind jedoch selbst wieder tabellarische Daten und somit ist zwar ein minimaler Mehrwert erzeugt worden, aber die Domäne der Darstellungsart ist dieselbe und unterliegt somit den domänenspezifischen Beschränkungen, nämlich dem Auffassungsvermögen großer Tabellen durch den Menschen. Schaubilder, Diagramme und weitere graphische Darstellungsformen erscheinen hierbei die deutlich naheliegendere Option zu sein, um Erkenntnisse aus Daten abzulesen. Graphische Darstellungen sind zusätzlich nicht starr auf zwei Dimensionen eingeschränkt, sondern können mit verschiedenen Tricks mehrere Dimensionen abbilden. Dieser Punkt lässt sich ein wenig unter dem Mantra zusammenfassen, dass du nicht versuchen solltest, mit einem Werkzeug alles abzubilden, sondern für dein Ziel das entsprechende Werkzeug wählst.

Persönliche Präferenzen sind natürlich immer eine schlechte Argumentationsbasis und so bin ich dazu gewillt, meine Abneigung gegen Pivot-Tabellen abzulegen und sie feinsäuberlich zu erstellen und Zellen, auf die ich hinweisen möchte, zu markieren. Eine Abwägung, die du nämlich stets treffen solltest ist die Zeit, die du benötigst, um entsprechende Kernaspekte, die du bereits in den Daten durch programmatische Überprüfungen entdeckt hast, möglichst kompakt zu übermitteln. Pivot-Tabellen tauchen meist im Excel-Kontext auf, was dich nicht verwundern sollte, denn von 1994-2020 war PivotTable in den USA eine eingetragene Marke von Microsoft. Als Erstnutzer war ich anfangs etwas überfordert, doch wenn dir das Konzept bewusst wird, kannst du relativ schnell diverse Aggregationen zusammenklicken und Informationen auf dem Silbertablett servieren. Mit Datensätzen, die weit in die Millionen gehen, kommt Excel weit an seine Grenzen, da es maximal 1.048.576 Zeilen unterstützt. Die Erfahrung zeigt, dass bereits beim arbeiten mit 100.000 Zeilen die gefühlte Grenze von Excel erreicht ist. Das nächstgelegene Werkzeug für eine Verarbeitung von tabellarischen Daten ist somit eine Abfragesprache, eine Structured Query Language oder kurz SQL. Es gibt verschiedene SQL-Dialekte und auf meiner Suche nach der passenden Datenbanktechnologie bin ich auf PostgreSQL gestoßen, welches sogar im NoSQL Bereich den ehemaligen Vorreiter MongoDB aussticht. Das schien mir also der perfekte Dialekt zu sein, um zukünftig größere Probleme zu bearbeiten. Hier geht es insbesondere um viele Tabellen, die Relationen zwischen einander besitzen und die Menge der Daten ist ebenfalls noch nicht so groß, dass ich auf andere Lösungen wie Apache Cassandra zurückgreifen müsste.

Je mehr ich darüber nachdenke, bin ich von meiner ersten Erfahrung sehr traumatisiert. Hierbei war die Aufgabe innerhalb der Datenbank eine Pivot-Tabelle zu erstellen. Es musste über die Datenbank geschehen, da die Datenmenge in der entsprechenden Größenordnung lag. Es ist unfassbar einfach, die obige Tabelle und ihr Pivot-Pendant zu erzeugen.

-- Lösche die Tabelle, falls sie bereits existiert
drop table if exists pivot_example;

-- Erstelle die Tabelle
create table pivot_example (
	"name" varchar,
	"augenfarbe" varchar,
	"anzahl" int
);

-- Befülle die Tabelle mit unseren Beispielwerten
insert into pivot_example values
	('Alex', 'Blau', 3),
	('Alex', 'Grün', 2),
	('Patricia', 'Braun', 1),
	('Patricia', 'Blau', 5),
	('Eva', 'Grün', 3);
	
-- Importiere tablefunc für die crosstab Funktion
create extension if not exists tablefunc;

-- Erzeuge die Pivot-Tabelle
select * 
from crosstab('select name, augenfarbe, anzahl from pivot_example order by 1, 2', -- Datten vernünftig anordnen
			  'select distinct augenfarbe from pivot_example order by 1' -- Zu verwendende Werte. Ohne dieses Argument würde die Tabelle falsch aggregiert werden, da nicht jeder Name mit jeder Augenfarbe auftaucht
)
as ct ("name" varchar, "Blau" int, "Braun" int, "Grün" int)

Ein besonderes Augenmerk möchte ich hierbei auf Zeile 27 legen. Hier muss nämlich die Typisierung der Spalten geschehen. crosstab liefert die Tabelle, aber um den Rückgabewert, also unsere Pivot-Tabelle ausgeben zu können, müssen wir vorab Typen und Spaltennamen definieren und hier kommt mein Frust ins Spiel. Gehen wir davon aus, dass wir nicht nur drei Augenfarben haben, sondern 500 und dann nicht in Worten aufgeschrieben, sondern einfach in ihrer Hexadezimaldarstellung. Dann kannst du zwar relativ simple die Pivot-Tabelle erzeugen, indem du die Abfrage aus Zeile 25 ausführst, im Text-Editor deiner Wahl den entsprechenden Typ, die benötigten Anführungszeichen und ein Komma ergänzt und zurück in die Abfrage einfügst, aber die Abfrage ist dann keineswegs dafür geeignet generell verwendet zu werden, denn wenn im nächsten Durchlauf eine Augenfarbe fehlt, musst du den Eintrag entfernen, da die Abfrage sonst in einen Fehler läuft. Selbiges gilt für neu hinzugekommene Farben. Schlimmer wird es in dem Fall, dass die Anzahl an Farben gleich bleibt, die Farben sich jedoch inhaltlich ändern. Dann geht die Abfrage durch, jedoch ist die Spalte falsch betitelt. So würde die Abfrage

-- Erzeuge die Pivot-Tabelle
select * 
from crosstab('select name, augenfarbe, anzahl from pivot_example order by 1, 2', -- Datten vernünftig anordnen
			  'select distinct augenfarbe from pivot_example order by 1' -- Zu verwendende Werte. Ohne dieses Argument würde die Tabelle falsch aggregiert werden, da nicht jeder Name mit jeder Augenfarbe auftaucht
)
as ct ("name" varchar, "Blau" int, "Braun" int, "Grau" int)

in keinen Fehler laufen, jedoch die Spalte, die zuvor mit „Grün“ betitelt war, mit „Grau“ betiteln. Das kann in stressigen Phasen gerne übersehen werden. Dieser Umstand sorgt dafür, dass ich meine sowieso tiefe Abneigung gegen Pivot-Tabellen noch ein klein wenig ausgebaut habe. Der natürlich Reflex ist dann, das Problem zu lösen. An dieser Stelle musst du erstmal verstehen, dass SQL alle Informationen vor Ausführung kennen möchte. Genauer bedeutet das, dass ein Paradigma von SQL voraussetzt, die Anzahl, den Typ und den Namen der Spalten der Ergebnistabellen zu kennen. Somit ist es nicht möglich innerhalb von SQL selbst, eine Pivot-Tabelle mit dynamischen Spalten in einer einzigen Abfrage zu erstellen. Lösen wir die Beschränkung auf eine Abfrage können wir folgenden Trick anwenden.

select $$select * 
from crosstab('select name, augenfarbe, anzahl from pivot_example order by 1, 2', -- Datten vernünftig anordnen
			  'select distinct augenfarbe from pivot_example order by 1' -- Zu verwendende Werte. Ohne dieses Argument würde die Tabelle falsch aggregiert werden, da nicht jeder Name mit jeder Augenfarbe auftaucht
) AS ct ("name" varchar,$$
|| string_agg(distinct quote_ident(augenfarbe), ' int, ' order by quote_ident(augenfarbe)) || ' int)' as my_query
from   pivot_example;

Damit erzeugen wir das gewünschte Abfrage, welche wir nur noch ausführen müssen. Es ist zwar keine vollautomatische Lösung, jedoch hilft sie dabei, Flüchtigkeitsfehler zu vermeiden, was der ursprüngliche Aufhänger war. Der Zug ist für mich jedoch abgefahren und ich werde kein Fan von Pivot-Tabellen. Wir sollten uns nämlich eher in eine Richtung bewegen, in der wir für einfache oder ungewisse Fragestellungen nicht mehr pivotieren müssen. Solltest du noch wissen, wie ich die Abfrage automatisch in PL/pgSQL ausführen kann, darfst du mir das gerne in den Kommentaren oder auf irgendwelchen anderen Kanälen verraten!

Benutzt du gerne Pivot-Tabellen? Schreib mir deine Ansicht in die Kommentare!