Pivotieren mit bedingter Aggregation

Eine der häufigsten Aufgaben in Vorstellungsgesprächen zur Berichterstellung lautet: Zeilen in Spalten umwandeln. Sie haben eine lange Tabelle wie sales(region, quarter, amount), und der Interviewer möchte einen breiten Bericht mit einer Spalte pro Quartal.

Die portable, SQL-Dialekt-unabhängige Antwort, die Sie nennen sollten, lautet bedingte Aggregation: ein CASE-Ausdruck innerhalb einer Aggregatfunktion wie SUM. Wenn Sie dieses Muster beherrschen, können Sie in jeder Datenbank pivotieren, auch in Datenbanken ohne das Schlüsselwort PIVOT.

Benennen Sie vor dem Pivotieren zunächst die beiden Formen. Das Langformat speichert eine Tatsache pro Zeile: Jedes Regions-/Quartalspaar steht in einer eigenen Zeile. Das Breitformat verteilt eine Kategorie über mehrere Spalten.

• Langformat: leicht einzufügen, schwer nebeneinander zu lesen.
• Breitformat: ideal für einen menschenlesbaren Bericht.

Ein Pivotieren wandelt das Langformat in das Breitformat um. Interviewer schätzen dieses Thema, weil es prüft, ob Sie Aggregation und nicht nur Syntax verstehen.

Der entscheidende Trick: Schreiben Sie für jede Ausgabespalte ein CASE, das den Wert zurückgibt, wenn die Zeile zu dieser Spalte passt, und andernfalls NULL. Schließen Sie den Ausdruck in eine Aggregatfunktion ein, damit die Gruppe auf eine Zeile pro Schlüssel reduziert wird.

Lesen Sie es so: Addieren Sie den Umsatz, aber nur für die Q1-Zeilen. Da SUM NULL ignoriert, tragen nicht passende Zeilen nichts zur Summe bei.

Dieses Muster funktioniert aufgrund einer Tatsache, die Interviewer häufig prüfen: Aggregatfunktionen überspringen NULL-Werte. Ein CASE ohne ELSE gibt NULL zurück, wenn kein Zweig zutrifft. Daher addiert SUM(CASE WHEN ... THEN amount END) nur die ausgewählten Zeilen.

Wenn Sie stattdessen ELSE 0 schreiben, funktioniert dies für SUM ebenfalls (das Addieren von 0 ändert nichts), verfälscht aber AVG, MIN und COUNT.

Hier ist die vollständige Abfrage für die Beispieldaten. Jede Region wird zu einer Zeile, jedes Quartal zu einer Spalte.

GROUP BY region sorgt dafür, dass die vier Eingabezeilen zu zwei Ausgabezeilen zusammengefasst werden. Ohne diese Klausel erhielten Sie eine Zeile pro Eingabezeile, die größtenteils NULL-Werte enthielte.

Das Aggregat, in das Sie CASE einschließen, muss zur Fragestellung passen:

• SUM, wenn jede Zelle Werte summiert.
• MAX oder MIN, wenn jedes Regions-/Quartals-Paar genau einen Wert enthält und Sie diesen lediglich ausgeben möchten.
• COUNT, wenn jede Zelle passende Zeilen zählt.

In Vorstellungsgesprächen wird häufig die COUNT-Variante gefragt: Wie viele Bestellungen gibt es pro Status und Monat?

Wenn jedes Schlüssel-/Kategorie-Paar einen einzelnen Wert enthält (also ein echtes Kreuztabellenformat und keine Summe), verwenden Sie MAX oder MIN. Beide geben den einzigen Wert ungleich NULL zurück und ignorieren die NULL-Werte aus nicht passenden Zweigen.

Das ist die sichere Wahl, wenn Sie Attribute umstrukturieren, statt Geldbeträge zu summieren, beispielsweise beim Umwandeln einer Tabelle mit Schlüssel/Wert-Einstellungen in eine Zeile pro Entität.

Wenn eine Region keine Umsätze in Q2 hatte, enthält ihre Zelle q2 den Wert NULL. In einem Vorstellungsgespräch werden Sie möglicherweise gebeten, stattdessen 0 auszugeben. Schließen Sie das gesamte Aggregat in COALESCE ein.

Setzen Sie COALESCE außerhalb des Aggregats, nicht innerhalb von CASE, damit Sie nur dann einen Ersatzwert einsetzen, wenn für die gesamte Gruppe keine passenden Zeilen vorhanden sind.

Eine häufige Anschlussfrage lautet: Fügen Sie eine Summe über alle pivotierten Spalten hinzu. Sie müssen die Spalten nicht namentlich addieren. Ein einfaches SUM(amount) über dieselbe Gruppe ergibt die Zeilensumme, weil es die Filterung durch CASE vollständig ignoriert.

Damit zeigen Sie der interviewenden Person, dass Sie verstehen, dass jedes Aggregat in SELECT unabhängig über dieselbe Gruppe berechnet wird.

PostgreSQL und der SQL-Standard unterstützen FILTER (WHERE ...), eine übersichtlichere Möglichkeit, bedingte Aggregation zu formulieren. Der Ausdruck ist besser lesbar und vermeidet den CASE-Boilerplate-Code.

Erwähnen Sie diese Möglichkeit im Vorstellungsgespräch, um Ihre Bandbreite zu zeigen. Beachten Sie jedoch, dass MySQL und SQL Server sie nicht unterstützen. Daher bleibt CASE die portable Lösung.

Die bedingte Aggregation hat einen Haken, auf den Interviewerinnen und Interviewer gern eingehen: Sie müssen jede Ausgabespalte von Hand angeben. Wenn Quartale oder Kategorien nicht im Voraus bekannt sind, kann sich diese statische Abfrage nicht anpassen.

Dieses Problem wird als dynamischer Pivot bezeichnet und erfordert generiertes SQL. Für eine feste, bekannte Gruppe von Kategorien ist die bedingte Aggregation jedoch die saubere, portable Lösung.

Testen Sie, wie gut Sie das Muster der bedingten Aggregation beherrschen.

Bedingte Aggregation ist der portable Pivot, den jede interviewende Person akzeptiert:

• Eine CASE-Anweisung pro Ausgabespalte, eingeschlossen in ein Aggregat.
• SUM für Summen, MAX/MIN für Zellen mit einem einzelnen Wert, COUNT für Zählungen.
• Das funktioniert, weil Aggregate den NULL-Wert aus nicht passenden Zweigen ignorieren.
• Verwenden Sie COALESCE, um leere Zellen in 0 umzuwandeln.
• Einschränkung: Die Spalten müssen fest im Code angegeben werden. Als Nächstes geht es daher um dynamische Pivots.