Erstellen Sie eine Pivot-Tabelle in PostgreSQL

Eine Pivot-Tabelle neigt dazu, einige Werte zu aggregieren, die bereits in einer ursprünglichen Tabelle angegeben sind, die verwendet wird, um große Datenflüsse zusammenzufassen. In PostgreSQL ist es eine Tabelle, die mit benutzerdefinierten N-Wertspalten zurückgegeben wird, was der Datentyp der in unserem Ergebnis zusammengefassten Zeile ist.

Heute lernen wir, wie man eine Pivot-Tabelle in PostgreSQL erstellt, indem man eine vordefinierte Tabelle mit vorhandenen Werten verwendet.

Verwenden Sie crossstab(), um eine Pivot-Tabelle in PostgreSQL zu erstellen

Sie finden crossstab() unter der Überschrift TABLE_FUNC in der PostgreSQL-Dokumentation. Es ist eine Funktion, die eine Tabelle mit mehreren Zeilen zurückgibt.

Syntax:

crosstab ( sql text ) ? setof record

Diese Abfrage erzeugt eine Pivot-Tabelle mit Zeilennamen plus N-Wertspalten, wobei N durch den in der aufrufenden Abfrage angegebenen Zeilentyp bestimmt wird.

crosstabN ( sql text ) , setof table_crosstab_N

Diese Abfrage erzeugt eine Pivot-Tabelle mit Zeilennamen plus Spalten mit N-Werten. Kreuztabelle2, Kreuztabelle3 und Kreuztabelle4 sind vordefiniert.

Nun wollen wir sehen, wie wir es verwenden können. Wir werden eine einfache Tabelle mit dem Namen APARTMENT mit Spalten erstellen; ID, EINHEIT, PREIS, FLÄCHE.

Beispiel:

CREATE TABLE apartment (
   ID int PRIMARY KEY,
   UNIT int,
   PRICE int,
   AREA int
)

Lassen Sie uns nun einige Werte zu unserer Tabelle hinzufügen:

INSERT INTO apartment VALUES (1, 20, 200, 10) , (2, 20, 200, 9), (3, 50, 190, 8);

Also, wenn wir uns jetzt unsere Tabelle ansehen, würde es ungefähr so ​​aussehen.

Ausgang:

id    unit   price  area
1     20     200     10
2     20     200     9
3     50     190     8

Und in unserer Pivot-Tabelle würde diese Tabelle wie folgt dargestellt:

unit    ..180    190        200          210...
20         -      -      19/2 = 9.5       -
50         -      8         -             -

Hier nehmen wir also den Durchschnitt der FLÄCHE für jede EINHEIT zu ihren spezifischen PREISEN. Wir wollen die durchschnittliche FLÄCHE sehen, die wir auf den erwähnten PREIS bekommen.

Wenn Sie also die durchschnittliche FLÄCHE für einen PREIS von 200 für eine EINHEIT von 20 finden, erhalten Sie den Durchschnitt als (10 + 9) / 2 = 8. Auf diese Weise finden Sie die Pivot-Tabelle mit den Aggregaten.

Jetzt haben Sie das Konzept verstanden, also lassen Sie uns fortfahren und es umsetzen. Wir können eine Abfrage wie folgt schreiben:

Select UNIT, PRICE, avg(area)
from apartment
group by UNIT, PRICE

Diese Abfrage implementiert die grundlegende Funktionalität dessen, was wir wollen. Es verwendet die Klausel GROUP BY, um die Spalten in der Tabelle zu übernehmen, gegen die wir aggregieren möchten.

Wir möchten den Durchschnitt der FLÄCHE für eine bestimmte EINHEIT und einen PREIS finden; Daher gruppieren wir mithilfe dieser beiden Spalten. Eine Tabelle wird wie folgt zurückgegeben.

Ausgang:

unit   price        avg
50     190     8.0000000000000000
20     200     9.5000000000000000

Nun, diese Tabelle funktioniert auch einwandfrei, aber lassen Sie uns fortfahren und die Kreuztabelle() verwenden. Sie müssen sich ansehen, wie es funktioniert und welche Einschränkungen es gibt.

Um crossstab() zu verwenden, müssen wir zwei entscheidende Punkte sicherstellen:

1. crossstab() muss für jede Zeile unterschiedliche Werte haben.
2. crossstab() muss für jede Spalte die gleichen Datentypen haben.

Stellen Sie also sicher, dass keine Spalte einen anderen Datentyp hat. Um nun unsere Ergebnisse aus dieser GROUP BY-Abfrage zu drehen, lassen Sie uns fortfahren und etwas wie folgt schreiben:

select *
from crosstab
(
'Select UNIT::float, PRICE::float, avg(AREA)::float
from apartment
group by UNIT, PRICE') as ct(
    UNIT float,
    avge float
);

Hier sind einige kritische Punkte zu beachten. Wir wählen die Spalten aus der Kreuztabelle aus, die als Ergebnistabelle CT zurückgegeben wird, wie von uns mit zwei Spalten definiert: UNIT und avge.

Nach der Gruppierung erhalten wir die Spalten aus der WOHNUNG und finden den Durchschnitt. Diese Abfragetabelle wird dann für unser Endergebnis geschwenkt.

Beachten Sie auch, dass wir sicherstellen, dass jede Spalte in denselben Datentyp umgewandelt wird. Natürlich ist ein Durchschnitt entweder eine doppelte Genauigkeit oder ein Gleitkommawert.

Daher ist es besser, die int-Spalten zu floaten, auch wenn es unnötig ist. Wenn Sie die Abfrage ohne Casting ausführen, wird ein Fehler wie unten zurückgegeben.

Ausgang:

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

Um dies zu vermeiden, stellen wir daher sicher, dass wir in Float umwandeln oder eine neue Tabelle mit darin eingefügten Werten mit dem Typ Float erstellen. Der Unterschied in den Datentypen macht das Tupel inkompatibel.

Ausgang:

unit    avg
50       8
20      9.5

In anderen Fällen muss crossstab() nicht verwendet werden. Sogar die Verwendung von GROUP BY ist effektiv, da es uns eine Tabelle mit eindeutigen Werten zurückgibt, um einen Durchschnitt zu erhalten.

Ohne die Funktion crossstab() hätten wir immer noch folgendes Ergebnis erhalten:

unit   price   avg
50     190      8
20     200     9.5

Sie können in crossstab() feststellen, dass die Verwendung einer Rückgabeliste mit drei Spalten, wie wir es möchten, immer noch nur Werte zurückgibt, die die UNIT und den Durchschnitt der Fläche enthalten. Warum?

Die SQL-Anweisung übernimmt eine Reihe von Kategorien und Werten, und in unserem Fall wird die Kategorie zur EINHEIT, und die Werte sind in der Regel die Durchschnittsspalte. Daher werden in unserer Rückgabetabelle nur zwei Spalten beibehalten.

Wir können es jetzt verwenden, da wir wissen, wie crossstab() für unsere Bedürfnisse funktioniert. Hätten wir UNITS mit unterschiedlichen PRICES und würden dann AVG() nehmen, hätten wir abhängig von unseren Daten eine N-Anzahl Spalten.

Ändern wir die Werte in unserer Tabelle wie folgt:

id    unit   price  area
1      20    200     10
2      20    170     9
3      50    190     8

Wenn Sie nun die Abfrage crossstab() darauf ausführen, wird Folgendes zurückgegeben.

Ausgang:

unit   avge   avge1
50      8    [NULL]
20     10      9

Sie können feststellen, dass eine Änderung des PRICE dazu neigt, die AREA-Werte in verschiedene Durchschnittswerte zu verteilen. Dies geschieht, weil wir unseren Durchschnitt auch nach Preisen gruppieren.

Gleiche PREIS-Werte haben einen Durchschnitt unterschiedlicher Werte, wenn sie für alle vorhanden sind, aber andere PREIS-Werte bestehen nur aus Durchschnitten von GEBIETEN für jeden PREIS. Daher ist die Tabelle auf diese Weise sortiert.