select Ergbnis in der Reihenfolge der Werte im Array

Fragen und Probleme rund um die Abfragespache SQL.
alexander_ro
Benutzer
Beiträge: 88
Registriert: So 12. Nov 2006, 12:29

select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von alexander_ro » Di 21. Mai 2019, 10:16

Hallo,

ich speichere Wörter in einer Tabelle mit einer WortId zusammen. Im Prinzip das gleiche was Computer mit Zeichensätzen machen also eine Nummer repräsentiert das Zeichen. Bei mir das ganze halt mit Wörtern.

Code: Alles auswählen

create table Woerter
(
  WortId               bigserial primary key,
  Wort                 text unique
);
In einer zweiten Tabelle speichere ich Erklärungen zu den Wörtern. Hier wird der Text in einem Array aus WortIds dargestellt.

Code: Alles auswählen

create table Erklaerung
(
  WortId                bigint references Woerter (WortId) on delete cascade,
  Erklaerung            bigint[]
);
Jetzt habe ich versucht mit einem select die Erkläerung wieder in einen Text aus Wörtern umzuwandeln. Das funktioniert leider nur fast. Die Ausgabe des select wird leider nach der WortId sortiert. Deshalb ist der Text dann schwer zu verstehen.

Code: Alles auswählen

select * from woerter where wortid in (select unnest (erklaerung) as Id from erklaerung where wortid = 50);

Code: Alles auswählen

So sieht das Array aus:
 wortid |            erklaerung             |
--------+-----------------------------------+
     50 | {12,49,5,11,53,41,54}             |

So sieht der Output des selects aus:
 wortid |   wort   
--------+-----------+
      5 | mir       | 
     11 | die       | 
     12 | du        | 
     41 | daten     | 
     49 | sollst    | 
     53 | genannten | 
     54 | suchen    | 

Jetzt fehlt mir irgendwie die Idee wie ich die Sortierung so hin bekomme das die Wörter in der Reihenfolge stehen wie im Array die WortIds waren. Ich könnte natürlich das manuell im Programm machen aber ich fände es hübscher wenn es die Datenbank gleich richtig liefert.

Viele Grüße
Alexander

akretschmer
Erfahrener Benutzer
Beiträge: 7613
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von akretschmer » Di 21. Mai 2019, 11:51

Code: Alles auswählen

test=*# select * from woerter ;
 id |   wort    
----+-----------
  5 | mir
 11 | die
 12 | du
 41 | daten
 49 | sollst
 53 | genannten
 54 | suchen
 50 | test
(8 rows)

test=*# select * from erklaerung ;
 wort_id |      erklaerung       
---------+-----------------------
      50 | {12,49,5,11,53,41,54}
(1 row)

test=*# with id as (select unnest (erklaerung) as Id from erklaerung where wort_id = 50) select * from id left join woerter on id.id=woerter.id;
 id | id |   wort    
----+----+-----------
 12 | 12 | du
 49 | 49 | sollst
  5 |  5 | mir
 11 | 11 | die
 53 | 53 | genannten
 41 | 41 | daten
 54 | 54 | suchen
(7 rows)

test=*#
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

alexander_ro
Benutzer
Beiträge: 88
Registriert: So 12. Nov 2006, 12:29

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von alexander_ro » Di 21. Mai 2019, 12:55

Danke funktioniert.

Liegt das an der Reihenfolge?
Ich hatte ja die Daten aus der Woerter Tabelle gesucht. Bei Deiner Version nimmst Du das Id Array und sortierst die Woerter dazu.

akretschmer
Erfahrener Benutzer
Beiträge: 7613
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von akretschmer » Di 21. Mai 2019, 13:13

Das ganze ist a bissl blöd, ich kann/will auch nicht garantieren, daß das stabil funktioniert. Hintergrund: Datenbanken arbeiten mit Mengen, die per se unsortiert sind, und es ist der DB frei, in Abfragen ohne ORDER BY die Daten in absolut beliebiger Ordnung zu liefern. Es kann sogar passieren, daß 2 nacheinander laufende Abfragen auf nicht geänderte Daten diese in untersch. Reihenfolge bringen, Grund kann u.a. ein Feature namens 'synchronized parallel scans' sein.

Also, um es klar zu sagen: das kann u.U. in späteren Versionen von PG nicht mehr garantiert sein, daß das funktioniert. Vielleicht überlegst Du Dir eine andere Lösung, die nicht auf der Annahme basiert, daß irgend etwas in irgend einer Reihenfolge ist. Die Reihenfolge der Elemente im Array mag fest sein und bleiben, vielleicht eine Funktion, die durch das Array geht und dazu noch die Position im Array mitliefert, als Tabelle. Diese extra Spalte kann dann zum sortieren dienen.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1125
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von castorp » Di 21. Mai 2019, 13:40

Die Position der Array Element kannst Du über "WITH ORDINALITY" beim unnest() bekommen. Das geht aber nur wenn man die Funktion im FROM verwendet:

Code: Alles auswählen

select x.id, w.wort
from erklaerung e
  cross join unnest(e.erklaerung) with ordinality as x(id, nr)
  join woerter w on w.id = x.id
where e.wort_id = 50
order by x.nr;
Online Beispiel: https://rextester.com/FCYBH91833
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

akretschmer
Erfahrener Benutzer
Beiträge: 7613
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von akretschmer » Di 21. Mai 2019, 14:45

ahhh, ja.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

alexander_ro
Benutzer
Beiträge: 88
Registriert: So 12. Nov 2006, 12:29

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von alexander_ro » Di 21. Mai 2019, 15:36

Ja das Problem kenne ich das nichts sortiert ist wenn man es nicht extra sortiert. Das array ist aber ein normaler Datentyp der darf die Reihenfolge nicht einfach ändern. Man muss aber das Array halt in eine Menge umwandeln und dann mit einer anderen Verbinden. Da ist viel Raum für etwas das schief gehen kann. Ist mir schon klar. Ob der unnest die Reihenfolge garantiert oder nicht konnte ich nichts dazu finden.

Das mit dem unnest ... with ordinality habe ich gerade versucht zum laufen zu bekommen. Das ist mir beim Suchen in der PostgreSQL Dokumentation aufgefallen. Castorp war da schneller mit einer Funktionierenden Version. Sieht aber schon recht Aufwendig aus.

Momentan erscheint mir die Lösung das vom Anwendungsprogramm aus zu machen am einfachsten und übersichtlichsten. Wollte ich zwar erst nicht. Das Array ist leicht in einzelne Werte zu zerlegen und dann die Wörter aus der Tabelle nachlesen. Bin mir nicht sicher ...

pogomips
Erfahrener Benutzer
Beiträge: 100
Registriert: Fr 18. Nov 2016, 09:55

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von pogomips » Di 21. Mai 2019, 19:09

In der Anwendung würde ich das nur machen, wenn Du die Daten satzweise (einzeln) abrufst, nicht für Massenverarbeitung.
Und was solls, wenn es kompliziert aussieht? Du musst ja nicht persönlich die Bits hin und her schieben.

alexander_ro
Benutzer
Beiträge: 88
Registriert: So 12. Nov 2006, 12:29

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von alexander_ro » Di 21. Mai 2019, 19:51

Ist schon eher Massenverarbeitung.

Aufwändig schrieb ich wegen Laufzeit. Ich probiere gerade ein bisschen damit herum was von den drei Möglichkeiten wenig Rechenzeit kostet. Das ist aber nicht so einfach weil ja nicht nur die Datenbank daran beteiligt ist. So wie es aussieht ist es gar nicht so Aufwändig wie ich dachte. Optische Täuschung ... ;)

castorp
Erfahrener Benutzer
Beiträge: 1125
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: select Ergbnis in der Reihenfolge der Werte im Array

Beitrag von castorp » Mi 22. Mai 2019, 03:05

Naja, das Problem ist halt, dass Dein Modell nicht normalisiert ist, und Du deswegen jedes mal das Array in eine Liste verwandeln musst (also quasi die Daten "on-the-fly" normalisierst). Würdest Du eine klassische m:n mapping Tabelle verwenden, dann wäre das ein einfacher join.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Antworten