Anwenden von crosstab Funktion

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.

Vielen Dank für eure Hilfe.

Die übliche Empfehlung ist: mach das nicht in SQL - das Frontend ist der beste Platz um die Darstellung so anzupassen.

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;
1 Like

Eine Variante wäre noch eine Stored Procedure zu schreiben, die basierend auf den Werten in der Datenbank regelmässig eine View erstellt:

create procedure generate_test_report_view()
as
$$
declare
  l_row record;
  l_sql text;
  l_expression text;
begin
  l_sql := 'create view test_overview_per_customer as select customer_id';
  for l_row in select distinct concat_ws('_', test_name, test_version, var_name) test
               from the_table
               order by 1
  loop
    l_expression := format($s$max(raw_value) filter (where concat_ws('_', test_name, test_version, var_name) = %L) as %I$s$, l_row.test, l_row.test);
    l_sql := l_sql || ', ' || l_expression;
  end loop;
  execute 'drop view if exists test_overview_per_customer cascade';
  l_sql := l_sql ||' from the_table group by customer_id';
  execute l_sql;
end;
$$
language plpgsql;

Die Prozedur kann z.B. jede Nacht laufen. Das Frontend selektiert dann einfach alles von der View:

select *
from test_overview_per_customer;

Dies ist eine der ganz, ganz wenigen Beispielen wo ein select * im produktiven Code tatsächlich Sinn macht.

Online Beispiel

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 :wink: Vielen Dank.

Es hat uns sehr geholfen auf deiner Code-Basis weiterzuarbeiten. Vielen lieben Dank nochmal für deine Hilfe. :wink:

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.