Pivot-Tabellen in PostgreSQL unter Verwendung der Crosstab-Funktion

Vor einigen Jahren, als PostgreSQL Version 8.3 veröffentlicht wurde, wurde eine neue Erweiterung namens tablefunc eingeführt. Diese Erweiterung bietet eine ganze Reihe interessanter Funktionen. Eine davon ist die Funktion crosstab, die für die Erstellung von Pivot-Tabellen verwendet wird. Diese Funktion werden wir in diesem Artikel behandeln.

Die Funktionsweise dieser Funktion lässt sich am einfachsten anhand eines Beispiels mit einer Pivot-Tabelle erklären. Zunächst erläutern wir unsere Ausgangssituation aus praktischer Sicht, dann definieren wir die gewünschte Pivot-Tabelle.

Unsere Ausgangssituation: Rohdaten

Stellen Sie sich beim Lesen dieses Artikels vor, Sie wären Lehrer an einer Grundschule. Wir gehen davon aus, dass Sie jedes Fach (Sprache, Musik usw.) unterrichten. Die Schule stellt Ihnen ein System zur Verfügung, mit dem Sie alle Beurteilungs- oder Testergebnisse erfassen können. Die folgende SQL-Anweisung würde die Bewertungsergebnisse anzeigen, die Sie zuvor in das System geladen haben:

SELECT *
FROM evaluations

Unser Ziel: eine Pivot-Tabelle

Mit der folgenden Tabelle könnten Sie die Fortschritte Ihrer Schüler leicht verfolgen. In der Informatik nennen wir diese Art von Raster eine Pivot-Tabelle. Wenn Sie analysieren, wie die Pivot-Tabelle aufgebaut ist, werden Sie feststellen, dass wir Werte aus Rohdaten als Spaltenüberschriften oder Feldnamen verwenden (in diesem Fall Geografie, Geschichte, Mathematik usw.)

Aktivieren der Kreuztabellenfunktion

Wie bereits erwähnt, ist die Kreuztabellenfunktion Teil einer PostgreSQL-Erweiterung namens tablefunc. Um die Kreuztabellenfunktion aufzurufen, müssen Sie zunächst die tablefunc-Erweiterung aktivieren, indem Sie den folgenden SQL-Befehl ausführen:

CREATE extension tablefunc;

So funktioniert die Kreuztabellenfunktion

Die Kreuztabellenfunktion erhält einen SQL SELECT-Befehl als Parameter, der den folgenden Einschränkungen entsprechen muss:

• Der SELECT muss 3 Spalten zurückgeben.
• Die erste Spalte im SELECT ist der Bezeichner jeder Zeile in der Pivot-Tabelle oder dem Endergebnis. In unserem Beispiel ist dies der Name des Schülers. Beachten Sie, dass die Namen der Schüler (John Smith und Peter Gabriel) in der ersten Spalte erscheinen.
• Die zweite Spalte in SELECT steht für die Kategorien in der Pivot-Tabelle. In unserem Beispiel sind diese Kategorien die Schulfächer. Es ist wichtig zu beachten, dass die Werte dieser Spalte sich in viele Spalten der Pivot-Tabelle ausbreiten werden. Wenn die zweite Spalte fünf verschiedene Werte liefert (Geografie, Geschichte usw.), hat die Pivot-Tabelle fünf Spalten.
• Die dritte Spalte im SELECT stellt den Wert dar, der jeder Zelle der Pivot-Tabelle zugewiesen werden soll. Dies sind die Auswertungsergebnisse in unserem Beispiel.

Wenn wir uns unsere Pivot-Tabelle als zweidimensionales Array vorstellen, dann ist die erste SELECT-Spalte die erste Array-Dimension, die zweite SELECT-Spalte die zweite Dimension und die dritte Spalte das Array-Element value.like grid [first_column_value, second_column_value] = third_column_value.

In unserem Beispiel wird der SELECT-Parameter sein:

SELECT student, subject, evaluation_result FROM evaluations ORDER BY 1,2

Die Kreuztabellenfunktion wird in der FROM-Klausel der SELECT-Anweisung aufgerufen. Wir müssen die Namen der Spalten und Datentypen definieren, die in das Endergebnis eingehen sollen. Für unsere Zwecke wird das Endergebnis wie folgt definiert:

AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC)

Wenn wir alle diese Teile zusammenfügen, lautet unsere endgültige Abfrage:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Und das Ergebnis sehen wir hier:

Ein Rohdatensatz, viele Pivot-Tabellen

Aus einem einzigen Datensatz können wir viele verschiedene Pivot-Tabellen erstellen. Fahren wir mit dem Lehrer-Klassen-Beispiel fort und schauen wir uns einige unserer Optionen an.

Beispiel 1: Monatliche Bewertungsdurchschnitte

Als Lehrer benötigen wir vielleicht auch einen Bericht über die Bewertungsergebnisse eines Schülers für das vergangene Jahr. Nehmen wir zum Beispiel an, wir möchten die durchschnittlichen Bewertungen für John Smith von März bis Juli erhalten. In einem Raster wie dem folgenden würde die Tabelle wie folgt aussehen:

Die SQL für diese Pivot-Tabelle lautet:

SELECT * 
FROM crosstab( 'select extract(month from period)::text, subject.name,
		     trunc(avg(evaluation_result),2) 
     from evaluation, subject  
     where evaluation.subject_id = subject.subject_id and student_id = 1 
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Bitte vergleichen Sie diese Ausgabe mit der vorherigen Tabelle:

Beispiel 2: Suche nach unvollständigen Schülerdatensätzen

Wir könnten diesen Abschnitt auch "Eine Einschränkung von Kreuztabellen und wie man sie behebt" nennen. Bevor wir uns damit befassen, lassen Sie uns die Situation schildern:

Nehmen wir an, Sie wollen herausfinden, ob einige Schüler in bestimmten Fächern keine Bewertung erhalten haben. Vielleicht probieren Sie unsere vorherige Abfrage aus und fügen eine WHERE-Klausel für Juli hinzu. Der Code würde wie folgt aussehen:

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations where extract (month from evaluation_day) = 7 order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Die folgende Pivot-Tabelle ist das Ergebnis dieser Abfrage. Wir können schnell sehen, dass wir keine Noten für Sprache, Mathematik und Musik für Peter haben.

Wenn wir jedoch eine reguläre Abfrage versuchen, um Peters Noten im Juli zu erhalten ...

SELECT * from evaluations 
where extract ( month from evaluation_day)=7 and student like 'Gabriel, Peter'

... erhalten wir andere Ergebnisse. Hier haben wir Noten für Geografie und Sprache:

Natürlich ist die zweite Abfrage die richtige, da sie Rohdaten anzeigt. Das Problem liegt in der Erstellung der Pivot-Tabelle - bei einigen Kategorien fehlen Informationen. Um dies zu beheben, können wir die Kreuztabellenfunktion mit einem zweiten Parameter verwenden, der die vollständige Liste der Kategorien darstellt. Wenn Werte fehlen, wird die Pivot-Tabelle trotzdem korrekt erstellt. (Ich habe die Abfrage des zweiten Parameters rot hervorgehoben.)

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations 
                where extract (month from evaluation_day) = 7 order by 1,2',
                'select name from subject order by 1') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Jetzt haben wir eine korrekte Pivot-Tabelle mit leeren Werten an den richtigen Stellen.

Übung

Pivot-Tabellen geben uns eine andere Möglichkeit, unsere Daten zu sehen. Außerdem können wir mit Hilfe der Kreuztabellenfunktion verschiedene Pivot-Tabellen auf der Grundlage derselben Rohdaten erstellen. Versuchen Sie, eine Pivot-Tabelle zu erstellen, die die Höchsttemperatur für jede Stadt und jeden Monat auf der Grundlage der Rohdaten in der folgenden Tabelle anzeigt.

CREATE TABLE weather (city text, when timestamp, temperature float);

Die Pivot-Tabelle sollte eine Zeile für jede Stadt und eine Spalte für jeden Monat enthalten. Wenn Sie möchten, können Sie sich weitere Pivot-Tabellen ausdenken, die mit denselben Daten erstellt werden können.