Hallo Zusammen,
wir möchten gerne in Postgresql eine dynamische Pivot Tabelle (crosstab) erstellen und wissen nicht genau wie wir es richtig umsetzen/einsetzen. Könnt ihr uns da weiterhelfen?
Im Anhang findet ihr, was wir machen wollen.
table1 ist die Tabelle, wie sie bisher aussieht.
Um die Darstellung der Tabelle (table1) in der Anwendung (Frontend) nutzbar machen zu können, möchten wir gerne die Tabelle transformieren und als View zur Verfügung stellen. siehe im Anhang table1.1
Der Hintergrund wieso die Tabelle dynamisch sein muss ist, dass wir nicht wissen welche Tests noch kommen und können es nicht vorher fest definieren.
Ist aber nicht besser aus Performancegründen wenn man es in SQL macht, da im Frontend die Tabelle dynamisch gerendert werden muss und dadurch die Ladezeiten länger sind.
Die Tabelle im Frontend muss sowieso dynamisch aufgebaut werden. Aber das SQL ohne Aggregation wird deutlich effizienter sein, so dass die Gesamtlaufzeit vermutlich besser ist, wenn Du das im Frontend machst.
Ich würde auch nicht die Crosstab Funktion verwenden. Eine gefilterte Aggregation ist wesentlich einfacher zu verwenden (und zu erweitern).
select customer_id
max(raw_value) filter (where concat_ws('_', test_name, test_version, var_name) = 'XYP_212_A') as "XYP_212_A",
max(raw_value) filter (where concat_ws('_', test_name, test_version, var_name) = 'XYP_212_B') as "XYP_212_B",
max(raw_value) filter (where concat_ws('_', test_name, test_version, var_name) = 'DBZ_C123_f') as "DBZ_C123_f",
....
from the_table
group by customer_id
order by customer_id;
Und bevor Du fragst: nein es nicht möglich dies mit “dynamischen Spalten” zu machen - weder mit er gefilterten Aggregation, noch mit “crosstab()”. Eine fundamentale Einschränkung von SQL ist, dass die Anzahl, Namen und Datentypen der Spalten einer Abfrage vor dem Start der Abfrage erkenntlich sein müssen. Das geht nur sinnvoll im Frontend
Wenn das Frontend gut mit JSON umgehen kann, dann könntet ihr die Testdaten in ein JSON Array pro Kunde aggregieren:
select customer_id
jsonb_agg(jsonb_build_object(concat_ws('_', test_name, test_version, var_name), raw_value)) as tests
from the_table
group by customer_id
order by customer_id;
Man kann das wohl ‘dynamisch’ in SQL machen, hatte das schon mal gesehen. Dazu ist allerdings sehr viel drumrum an Funktionen etc. nötig, was das ganze IMHO völlig unwartbar macht.
Ich schliesse mich deshalb Castorp an, macht das im Frontend, das geht schneller und ist besser zu händeln. Wenn ich derartigen lösen muß erzeuge ich JSON-Daten und parse die mit Javascript oder Python.
Gruß Uwe
Diesen Ansatz finde ich richtig gut, da wir eine C# Anwendung (keine Webanwendung) programmieren, wo wir das DevExpress Framework für WPF nutzen und dort haben wir feststellen müssen bei großen (Dateneinträgen i.d. Tabelle) Transformationen von Tabellen es länger braucht um die Tabelle zu generieren bzw. zu rendern um die dann Anzeigen zu können. Es geht aber nicht optimal. Wenn uns da Postgresql an gute Lösung anbietet würden wir lieber ins Backend auslagern und nur auf die View zugreifen. Ich denke dieser Ansatz wird uns weiterhelfen. Wir probieren es aus und ich melde mich wie es gelaufen ist Vielen Dank.
Wir haben dafür mal eine Clientkomponente entwickelt. Vorteil ist hier die Flexibilität bei großen Mengen an Kriterien. Die sind dann relativ einfach (technisch und im Resourcenbedarf) zu filtern.
Wenn das Ganze gut codiert ist (also manipulationssicher), sehe ich auch kein Problem, die “echte” Crosstabfunktion per SQL zu nutzen, die dann allerdings adhoc zu einem Statement zusammengebaut werden muss.