(My)SQL: Suche In über mehrere Rows verteilte Daten

(My)SQL: Suche In über mehrere Rows verteilte Daten (26 Beiträge, 522 Mal gelesen)

Du bist nicht angemeldet. [ Login/Registrieren ]

Ich habe eine Tabelle mit im Prinzip nur drei Spalten (sind zwar mehr, aber die sind hier nicht relevant). Die Spalten sein eine Benutzer-ID, eine - nennen wir's mal - Feld-ID und die zugehörigen Daten (eine Zeile ist z. B. Benutzer-ID="32", Feld-ID="2", Daten="23").

Ich möchte nun alle Benutzer-IDs haben, die bestimmte Kriterien erfüllen, z. B. Feld 2 = "23" und Feld 5 = "2010" usw. Nicht jeder Benutzer hat alle Felder gesetzt (z. B. kein Eintrag mit Feld-ID="12").

Mein Ansatz wäre (halb Pseudo-Code):

SELECT user WHERE field = 2 AND data = "23";

found_ids = join(',', result);

SELECT user WHERE field = 5 AND data = "2010" AND user IN (found_ids);

found_ids = join(',', result);

SELECT user WHERE field = ... usw. usw.

Kennt jemand einen besseren Ausdruck, mit dem ich das eventuell in einem einzigen SQL-Statement erledigen kann?
Zu beachten: es gibt ein paar tausend Benutzer und ca. 100 unterschiedliche Felder, die meisten davon gar nicht gesetzt (das ist - neben der leichteren Erweiterbarkeit um neue Felder - der Grund, warum es keine Tabelle mit über 100 Spalten ist). Selbst bei "Extrem"-Anfragen soll MySQL natürlich nicht eingehen, falls das ganze in einem Statement möglich ist.

bin ned sicher ob ich deine beschreibung ganz check....

du willst mehrere benuzerIDs haben zu unterschiedlichen field und data werten!

also alle die zb field = 2 und data = "23" und alle die field = 5 AND data = "2010" haben?

dann könntest doch genauso sowas schreiben:

select * from user where (field = 2 und data = "23") OR (field = 5 AND data = "2010")

falls du das ned meinst, dann weiss i ned was du willst

bla bla bling bling blow blow bleed bleed bleed bitch

Oh, Ich habe beim SQL natürlich das FROM vergessen. Sollte also
SELECT user FROM user_data WHERE field = "Alter" AND data = "23";
usw. heißen.

Die Tabelle könnte so aussehen (aus der Feld-ID habe ich zum besseren Verständnis ein Wort gemacht):

user | field            | data
------+---------------+---------
    1  | Alter           | 23
    1  | Anmeldung | 2010
    1  | Beruf          | Student
    2  | Anmeldung | 2009
    2  | Wohnort     | Wien
    2  | Alter           | 25

Nicht alle Felder sind bei jedem Benutzer gesetzt, Benutzer 1 hat z. B. den Wohnort nicht angegeben, Benutzer 2 den Beruf nicht.

Ich möchte jetzte alle Benutzer, die 23 Jahre alt sind und sich 2010 angemeldet haben.

Ich hätte zuerst alle gesucht, die 23 sind. Dann hätte ich unter denen alle gesucht, die sich 2010 angemeldet haben. Aber jeder dieser Schritte ist eine eigene Datenbankabfrage.

dieses "Datenmodell" ist nicht dein Ernst - oder gibt es echt gute Gründe dafür?!?!?!?

Ok, du bekommst jetzt stellvertretend die Antwort für alle anderen, die auch danach gefragt haben.

Ein Grund: "I sogs glei, i woas ned!". Ich habe das System so in die Hand gedrückt bekommen, und soll jetzt etwas erweitern.

Aber selbst wenn ich das entworfen hätte, würde ich es unter Umständen wieder genauso machen. Wie gesagt, es gibt über 100 unterschiedliche "Daten", nur wenige davon sind verpflichtend bei jedem Benutzer gesetzt. Wäre die Tabelle so aufgebaut, dass jeder Benutzer nur einen Eintrag hat, wären von den über 100 Spalten dieser Zeile 95% leer.

Der Hauptgrund ist aber die Erweiterbarkeit. Die Felder, die es geben soll, sind nicht in Stein gemeißelt. Hin und wieder kommt ein neues dazu oder eins wird entfernt. Das würde bei einer 100+ Spalten-Tabelle heißen, die Tabellen Struktur muss umgebaut werden! Meiner Meinung nach ist ein "Datenmodell", dass sowas erfordert vollkommener Schwachsinn. Und schon gar nicht kann ich so eine Funktion in eine Admin-Oberfläche einbauen, wo ein Hansel dann nach belieben Felder hinzufügt oder löscht, ohne sich bewusst zu sein, was im Hintergrund passiert.

ok - ein Grund existiert damit.
stellt sich natürlich als nächstes die Frage, ob dann wirklich eine SQL-Datenbank das ideale Mittel der Wahl darstellt, oder zB was Richtung NoSQL, Prolog oder eine Engine mit assoziativen Strukturen. Ich verstehe aber gut, daß die Nebenbedingungen (verfügbares KnowHow, benutzte Umgebung, Zeit) derlei halt nur selten zulassen.

Bleibt zu hoffen, daß die Anwendung nicht komplett entartet.

btw - eine "sparse" belegte Tabelle effizient verwalten, das soll so eine moderne Engine ruhig erledigen, dafür ist sie da.

user | field            | data
------+---------------+---------
    1  | Alter           | 23
    1  | Anmeldung | 2010
    1  | Beruf          | Student
    2  | Anmeldung | 2009
    2  | Wohnort     | Wien
    2  | Alter           | 25

Welcher Wahnsinnige hat sich denn diesen Schwachsinn einfallen lassen?

Und ich dachte schon schlimmer als im IT8 kann man 's nimmer machen.

lg
Gott

Wie schon gebracht:

Denkbar ist schon, dass seine Gschicht Sinn macht (auch wenn ich bei weitem nicht überzeugt bin

).

Beispielfall:
Wir stellen uns eine hochgenerische AJAX-Gschicht vor, wo Bearbeiter laufend die Menüstrukturen ändern und so einen "Datensilo" betanken.

Wenn da wirklich
- nur ein "Datensilo/Friedhof" hochgenerisch befüllt werden soll
- danach zB ein ETL-Prozess stattfindet, der das in ein DWH wirft
- und danach Mustererkennung, ... anrennen soll

ist so eine Struktur eine Alternative, die man sich schon mal genau durchdenken kann und die in solchen Fällen sogar Sinn machen kann.

user   | field     | data
------+---------------+---------
    1  | Alter     | 23
    1  | Anmeldung | 2010
    1  | Beruf     | Student
    2  | Anmeldung | 2009
    2  | Wohnort   | Wien
    2  | Alter     | 25

Also...ich bin gerade alles andere als fit, weil in der Nacht einen spaktakulären Abflug mit dem Rad mit der -helmbewehrten- Birne voran auf den Beton einer Straße gemacht habe...

...aber ist das nicht völlig unzureichend normalisierter Daten-Sauhaufen??? Mir scheint, du verdankst dem deine Probleme bei der Abfrage.

Es gibt - im kommerziellen Umfeld - gelegentlich die "variable/variante Tabellenstruktur", die in Ausnahmefällen sogar Sinn macht.

DU hast
- eine Tabelle namens "Tabellen"
- eine Tabelle namens "Attribute"
- eine Tabelle namens "Werte"
- und eine Tabelle für die aufgelöste m:n:o-Beziehung.

Alternativ wandern die Werte in Tabelle "Werte" in die aufgelöste m:n-Beziehung.

Es gibt in sehr speziellen Fällen Argumente für solche Strukturen... Wenn man weiß, was man tut.

Ich glaube nicht, dass diese Struktur in seinem Fall sinnvoll ist, jedenfalls sieht seine Tabelle sehr nach dem Alternativ-Fall oben aus.

Beispiele, wo du ähnliches findest:
Schau Dir in der Oracle die $_-Tabellen an. Das Data Dictionary ist dort oft ähnlich gelöst.

Vielen Dank. Meine DB-Kenntnisse sind ja auch wirklich rudimentär. Aus dieser Warte ist mir die Anordnung sonderbar vorgekommen. Und offenbar macht sie dem User ja auch praktische Probleme. Aber ich werde dem, was du geschreiben hast, nachwassern. Unser Lektor hat ja auch gesagt: Es gibt keine richtige und kein falsche Lösung bei Datenbanken, nur zweckmässige und unpraktische.

Manchmal schon brauchbar, wann es eine Ebene darüber 'flexibel' zusammengefasst wird. So könnte man sehr flexible Datenbanken bauen, wo keine Daten verloren gehen wann in der Zwischenlayer mal was gröberes umgestellt wird.

Save the Earth... it's the only planet with chocolate.
Wir sollten die Fekter aus- oder einweisen...
Amazon Kindle DX: http://www.amazon.com/dp/B0015TG12Q

So etwas hat nur Sinn wann es ein Level drüber ein System gäbe das die Tabellen zu eine 'normale' Tabelle zusammenfügt.

Mit meine beschränkte Programmierkenntnisse würde ich wahrscheinlich etwas wie folgt machen:

Zuerst ein Distinct Query auf den field:

SELECT DISTINCT field as Field FROM tblTable;

Jetzt hast du alle verschiedene Field-Namen. Damit dann eine UNION Query Zusammenbasteln. Also eine Reihe Queries, wo jedesmal ein Feld abgefragt wird. Diese Queries dann mittels Union und First() zu eine Tabelle zusammenfassen.
Danach kannst du diese tabelle einfach abfragen wie immer: "SELECT * FROM UnionTable WHERE Alter = 23;"

Save the Earth... it's the only planet with chocolate.
Wir sollten die Fekter aus- oder einweisen...
Amazon Kindle DX: http://www.amazon.com/dp/B0015TG12Q

Löse es nicht so

.

Du könntest Dich schon spielen... In der PostgreSQL könnte man es wie folgt machen:

(1) Zuerst mal relevantes rauspicken:

SELECT user, field FROM xyz
WHERE (field = 2 AND data="23") OR (field=10 and data="40") or ....

Dadurch hast mal alle relevanten Bedingungen.

(2) Angenommen, derzeit wären in Summe 5 Bedingungen aktuell (also 5 Klammerausdrücke), dann kannst wie folgt filtern:

SELECT user from
(SELECT user from user_data
    where (feld=1 and wert=1)
    or (feld=2 and wert=30))
    or (feld=7 and ....)
    ....
    ....
  as bubu2
  group by user
  having count(*)=5 ;

Es klappt also in einem einzigen SQL-Statement, das noch dazu leicht gebaut ist.
Zusätzlich zu dem join(") or (", @bedingungen) musst nur ein
"having count(*)=" . scalar(@bedingungen)
dranpappen.

Aber ich wiederhole:
DU WILLST DAS NICHT

Zur Performance:
Das performt zwar pfeilschnell - du hast maximal 100'000 Zeilen, real wahrscheinlich unter 1000, in der Subquery, darüber lacht eine Datenbank nicht einmal. 1000 Zeilen werden wahrscheinlich unter 50k RAM brauchen - also auch nichts.

AABER:
- alle Zugriffskonstruktionen werden umständlich.
- Es trägt nicht zur Lesbarkeit der DB bei
- Ideen, auf die man selbst nicht kommt, soll man nicht verwenden

, weil:

Debuggen ist doppelt so schwer wie entwickeln. Wenn Du beim Entwickeln etwas "gerade noch" hinbekommst, hast etwas nicht-debugbares erschaffen

Und _wenn_ du schon absurde Sachen machen willst - warum in der MySQL und nicht PostgreSQL ????

18.09.2010, 08:02 Uhr - Editiert von kombipaket, alte Version: hier

Eine Lösung, die ich inzwischen gefunden (habe wohl schon zu lange nimmer mit Datenbanken gearbeitet, da ich nicht gleich an Sub-Selects gedacht habe) habe und die alles in einem Statement löst wäre folgende:

SELECT user FROM user_data WHERE field = 5 AND data = "2010" AND user IN 
 (SELECT user FROM user_data WHERE field = 2 AND data = "23" AND user IN 
   (SELECT user FROM user_data WHERE field = ... usw. usw)
 );

Im Prinzip macht das genau das, was mein als erster vorgeschlagener Ablauf machen würde, nur dass sich eben die Datenbank um's Zusammenfügen der IDs kümmert. Aber wenn auf 20 Felder gleichzeitig geprüft wird, ist ein Statement mit 20 ineinander geschachtelten Selects sicher nicht das wahre.

Weiteres Lesen hat aber ergeben, dass ich das definitiv nicht machen will. MySQL ist bei Sub-Queries recht ineffizient, wobei das oben genau der Worst-Case (sogar laut MySQL-Doku) ist.

In dem Fall scheint es tatsächlich schneller zu sein, das über die (Pseudo

)-Programmiersprache zu lösen.

user_where = '';
foreach (_feld,_wert) aus (suchfelder,suchwerte) {
   user_ids = query('SELECT user FROM user_data WHERE field = _feld AND data = _wert' + user_where);
   if (count(user_ids) > 0) {
      user_where = ' AND user IN (' + join(',', user_ids) + ')';
   } else {
      // keine Benutzer mehr im Ergebnis -> abbrechen
      last;
   }
}

// user_ids enthält hier die Liste aller den Such-Kriterien
// entsprechenden Benutzer; kann auch leer sein

Mit jedem Durchgang ist die Liste der user_ids eine Untermenge des vorherigen Durchgangs, bis entweder alle Felder abgearbeitet wurden oder gar keine Benutzer mehr übrig bleiben (weil keiner die geforderten Kriterien erfüllt).

Das scheint mir hier die beste Lösung zu sein, da ja auch die Anzahl der zu überprüfenden Felder variabel sein kann.

Hi !

Was hattest Du gegen den Lösungsvorschlag hier ? Der hatte nur 1 Subselect, egal wieviele Conditions.
http://forum.geizhals.at/t719624,6160056.html#6160056

Dein Pseudocode hat IMHO Macken:

Angenommen, du hast die 3 Bedingungen A, B und C

Du führst Die Query mit Bedingung A aus, und erhältst die UserIDs (12, 20, 3)
Danach führst die Bedingung B aus - hier soll kein Hit sein, daher rennt er in das last;
und die Schleife wird verlassen.

Danach hast aber Einträge in user_ids - und gibst fälschlicherweise die Benutzer (12,20,3) aus, obwohl
- B für keinen passte und
- C gar nicht geprüft wurde.

BUG!

Wenn schon, müsstest also direkt vor dem "last;" ein "unset user_ids" machen.

Schönheitsprobleme:
Weiters führts Du bei 200 Attributen dann 200 Queries durch - unperformant, bedeutet 200x parsen.
Weiters verwendest keine bind-Variablen dadurch:
- Gefahr von Injection (dadurch musst extra prüfen - aufwändig und unperformant, Fehlerträchtig)
- keine prepared-Statements möglich
- kein Speichern/Reusen der Planner Stats
- 200x Parsen statt nur 1x Parsen. Parsen ist _wirklich_ teuer und klappt in vielen RDBMS nur auf einer CPU. Keine Ahnung, wie das bei MySQL ist

20.09.2010, 11:05 Uhr - Editiert von kombipaket, alte Version: hier

Ist ja Pseudo-Code, schon klar, dass dich da nicht alle notwendigen Feinheiten angebe, die verwendet werden (und Programmiersprachen/Datenbank abhängig sind).

Den Bug den du siehst, sehe ich übrigens nicht. Zum "last" wird nur gesprungen wenn user_ids leer ist. Das ist es dann auch noch nach der Schleife. user_ids wird von der SQL-Abfrage neu gesetzt (übrigens noch eine Pseudo-Code-Verkürzung; so direkt könnte man die Ergebnis-Liste nicht zuweisen).

Den anderen Vorschlag habe ich mir schon angesehen und er ist auf meiner "Möglich-Liste" durchaus auch drauf. Es werden aber nie alle Attribute gleichzeitig abgefragt, eher im Bereich um 5 herum. Deswegen wären die mehrfachen Abfragen kein großes Problem. Vielleicht implemetiere ich auch beides und vergleiche dann (aber dafür muss mich erst mal jemand bezahlen

Nachdem ich das mit der Datenstruktur verstanden habe, wird es am einfachsten sein, Views auf die Tabelle zu generieren.
Also z.B. View Alter mit select user, data from user_data where field = 'Alter', dann einen View für Anmeldung etc.

Danach kannst nach belieben diese verknpfen.
Alternativ könntest es auch mit Sub-Select machen, nur wenn du die Abfragen öfter brauchst, machen Views mehr Sinn.

emefge
Infosauger
_____________________________________________________________________________

Weil die Lichtgeschwindigkeit höher als die Schallgeschwindigkeit ist, hält man viele Leute für helle Köpfe bis man sie reden hört.

"Bekomme ich bei Ihnen so einen ganz kleinen Lötkolben zum CPU reparieren?"

Wieso Realität? Ich hab doch verdammt noch mal was Besseres zu tun!

Select t1.user as user, t1.data as alter, t2.data as anmeldung
from tab as t1, tab as t2
where t1.user = t2.user
and t1.field = 2 and t1.data = 23
and t2.field = 5 and t2.data = 2010

21.09.2010, 20:09 Uhr - Editiert von TomCat, alte Version: hier

Dieses Forum ist eine frei zugängliche Diskussionsplattform.
Der Betreiber übernimmt keine Verantwortung für den Inhalt der Beiträge und behält sich das Recht vor, Beiträge mit rechtswidrigem oder anstößigem Inhalt zu löschen.
Datenschutzerklärung