Mysql Übereinstimmung von Datensätzen innerhalb einer Tabelle prüfen

MrVienna · 22.01.2010, 16:02

An die Mysql Spezialisten:

Ich bin mit meiner Ein-Tabellendatenbank (erste Normalform) bis jetzt gut ausgekommen. Die Spaltennamen bezeichnen den Inhalt des Datenfeldes und in das Datenfeld wird jeweils 1 oder 0 eingetragen, was aussagt, ob der Wahrheitsgehalt laut Spaltenname zutrifft oder nicht.

Also ungefähr so:

Code:

ID	Name	    Berufstätig   Studiert   Stinkreich     Bettelarm
1  	Alpha	          1           0            1            0
2       Beta              0           1            0            1

Nun soll ich aber eine Beziehung innerhalb der Tabelle herstellen, einen Inner Join, mit dem ich herausfinden kann, zu wieviel Prozent ein Datensatz mit einem anderen Datensatz übereinstimmt. Das heißt es soll der Reihe nach die Übereinstimmung von jeweils einem Datensatz zu einem gegebenen Datensatz geprüft werden und anschließend die Datensätze nach der prozentmäßigen Übereinstimmung zu dem einen gegebenen Datensatz gereiht werden.

Mir ist klar: Hätte ich eine zweite Tabelle und wären die Feldnamen untereinander gereiht, so könnte ich mit select und Wertevergleich einfach die zurückgegebenen rows erhalten und daraus die Übereinstimmung berechnen.

Leider ist die Datenbank bereits ziemlich befüllt und mit PHP verknüpft, so dass ich einem Umbau der Datenbankstruktur in die zweite oder dritte Normalform eher mit Bedenken entgegensehe.

Ich könnte auch die Datensätze aus der Datenbank holen und mit Php vergleichen und reihen. Aber ich glaube, dass dies bei vielen Datensätzen zu lange dauern wird?

Vielleicht sehe ich auch den Wald vor lauter Bäume nicht mehr und es gibt eine einfache Lösung. Für jeden Hinweis wäre ich sehr dankbar.

ChrisvA · 22.01.2010, 20:45

Also, ich kenne mich mit MySQL zwar nicht so optimal aus, habe aber einen ordentlichen Ansatz:
Du ließt den Datensatz, mit dem du vergleichst in PHP aus.
Den absoluten Unterschied (Anzahl der Unterscheidungen) zwischen den unterschiedlichen Einträgen kannst du jeweils mit dem "Exklusiven Oder" (XOR) Operator (MySQL :: MySQL 5.1 Referenzhandbuch :: 12.1.4 Logische Operatoren) erreichen. Der Unterschied ergibt sich bei der Summer der einzelnen Vergleiche. Mit Vergleich meine ich das Ergebnis aus [Vergleichswert] XOR [Unterschiedswert]
Den Absolutwert kannst du dann durch Teilen durch die Gesamtzahl der Spalten in deine gewünschte Prozentzahl umwandeln.
Ja nachdem wie groß aber die Datenbank ist, solltest du dir im klaren sein, dass die Auslastung im DB-Server ziemlich groß sein.

Ich hoffe du hast meinen Gedankengang verstanden, falls nicht frage einfach noch einmal nach. Mir ist aber leider gerade keine bessere Möglichkeit eingefallen das ganze in Worte zu fassen.

nikosch · 22.01.2010, 20:50

Für zwei Datensätze ist sowas einfach, für eine „gib mir für jeden Datensatz die Übereinstimmungen gegen einen bestimmten DS“ kann man so aber nicht mehr verfahren.

Das Problem ist, dass Du durch Deine 1-Tabellen-Lösung bei jeder DB-Änderung den PHP-Code gleichfalls auf die neuen Felder anpassen mußt. Nicht sehr genial.

MrVienna · 23.01.2010, 19:56

Danke verspätet für eure Antworten. Habe leider erst jetzt wieder Zeit für mein Problem gefunden.

@nikosch
Damit hast du recht:

Dafür was ich vorhabe ist eine 1-Tabelle nicht optimal.

Eine Möglichkeit überlege ich: Ob ich eine zweite Tabelle – nur für diese Statistik - erzeugen soll, in der sich die gesuchten Daten ein zweites Mal befinden. Ich hätte dann allerdings 43 Datensätze - jeweils aus 0 oder 1 bestehend - zwei Mal in der Datenbank.

@ChrisvA
Danke für deine Überlegungen.

Ich muss aber zugeben, dass ich noch nicht ganz durchsehe. Mir ist nicht klar, wie die Abfrage in der Praxis aussehen soll. Wenn ich das richtig gelesen habe, bekomme ich mit xor Null zurück wenn der Wert im Datenfeld Null ist. (nicht 0)

Und über diesen Satz aus mysql.com (mittlerer Satzteil nach dem Beistrich) denke ich immer noch nach:

Bei Nicht-NULL-Operanden wird 1 zurückgegeben, wenn eine ungerade Anzahl von Operanden nicht null ist, andernfalls wird 0 zurückgegeben.

Nun gut, ich bin nach wie vor für jeden Vorschlag dankbar. Und auch für jede Aufklärung, falls ich jetzt etwas nicht richtig verstanden habe.

ChrisvA · 24.01.2010, 11:25

Also ich habe das ganze jetzt mal als MySQL-String formuliert und es scheint als kämen die richtigen Werte dabei raus:

PHP-Code:

  SELECT (
(`v1`XOR 1 ) + 
(`v2`XOR 1 ) +
(`v3`XOR 1 ) +
(`v4`XOR 1 ) +
(`v5`XOR 1 ) +
(`v6`XOR 1 ) +
(`v7`XOR 1 ) +
(`v8`XOR 1 )
)
FROM `test1`

Statt der 1 musst du eben jeweils die Werte von deinem Vergleichsausdruck einsetzen. v* seinen deine Spaltennamen.

MrVienna · 24.01.2010, 17:25

@chrisvA

Danke für deine Antwort.

Nachdem ich in Mysql kein Experte bin, kann ich nicht ausschließen, dass ich bei deiner Anregung etwas übersehe.

Möchte nur kurz beschreiben, wie ich deinen Vorschlag nachvollzogen habe:

Code:

Select Spaltenname xor Vergleichswert…

Soweit, so gut. Die Abfrage hat funktioniert.

Aber bei der Ausgabe der Daten:

PHP-Code:

  <?

if ($anzahl>0)

for ( $x = 0; $x < $anzahl; $x++ )

  {$array  = mysql_fetch_array($res,MYSQL_ASSOC);

  echo "</pre>";

while (list($key,$value) = each($array))

{ echo "$key: $value " . "<br />";}

echo "<br />"; } }

?>

Bekomme ich natürlich wieder nur den tatsächlichen Inhalt des Datensatzes und keinen Hinweis auf einen Unterschied zu dem Vergleichswert, egal wie ich diesen auch verändere.

Oder gibt es einen anderen Weg, den Unterschied zu dem Vergleichswert aufzurufen ?

Harry_X · 24.01.2010, 20:10

ich versteh das Problem nicht so ganz.

Gehe ich hiervon aus:

Code:

ID	Name	    Berufstätig   Studiert   Stinkreich     Bettelarm
1  	Alpha	          1           0            1            0
2       Beta              0           1            0            1

so soll doch eine neue Tabelle/Matrix entstehen, die die Ähnlichkeiten eines Satzes mit einem anderen darstellt? Was dann so gemäß dem Beispiel aussähe:

Code:

         1        2
1    100%      0%      
2       0%   100%

(weil 1 zu 1/2 zu 2 natürlich zu 100% ähnlich ist, die sind natürlich vermutlich uninteressant)

Ja, kommt denn mit dem SELECT XOR dingens genau das dabei heraus?

MrVienna · 25.01.2010, 10:56

@Harry_X

Danke für deine Beteiligung.

Aber was meinst du mit:

Zitat:

so soll doch eine neue Tabelle/Matrix entstehen

Mit welchem Aufruf/Abfrage sollte eine neue Tabelle entstehen ?

draco88 · 25.01.2010, 11:15

Nach ChrisvA's zweiter Erklärung ist mir langsam klar geworden, was er meint.
Mit den ganzen XOR-Bedingen fragt er ab, wieviele Felder übereinstemmen, wenn man dazu dann noch die Anzahl an Feldern kennt kann man auch den Prozentsatz ausrechnen

PHP-Code:

  $sql = "
    SELECT t1.name AS t1_name,
               t1.ID AS t1_id
               t2.name AS t2_name,
               t2.ID AS t2_id,
               (
                   (t1.Berufstätig NOR t2.Berufstätig) + 
                   (t1.Studiert NOR t2.Studiert) + 
                   (t1.Stinkreich NOR t2.Stinkreich) + 
                   (t1.Bettelarm NOR t2.Bettelarm)
               )/4*100 AS percent
    FROM    tabelle t1
    INNER JOIN tabelle t2
";

Mit der Abfrage wird jeder Datensatz mit jedem Verknüpft und die Prozentuale Übereinstimmung mit angegeben.

Edit: Statt dem XOR sollt man besser NOR nehmen. Bei XOR würden 100% bedeuten, dass die beiden Datensätze garnicht übereinstimmen, bei NOR genau das gegenteil.

MrVienna · 25.01.2010, 11:55

@draco88

Danke für die ausführliche Erklärung.

Die leuchtet auch mir, als Msql-Einsteiger ein. Und dabei wollte ich das Ganze schon mit php erledigen, was aber sicher länger in der Ausführung gedauert hätte.

Schade, dass ich jetzt noch Anderes erledigen muss und erst am späten Abend zum ersten Test komme. Ich werde aber dann – sollte alles gut gehen – den Erfolg verkünden.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Bestehende php /Myqsl Tabelle Neben einander aus geben	Totti-Totti	PHP Tipps 2009	3	21.12.2009 11:00
Bildpfad in Tabelle mysql auslesen	DKuhn	Datenbanken	5	07.10.2009 13:46
Bilder in Tabelle einfügen Mysql oder direkt?!	DKuhn	PHP Tipps 2009	3	30.09.2009 10:14
MySql: Große Tabelle	boolean	Datenbanken	4	12.05.2009 07:01
MYSQL Tabelle -> backup und damit andere DB updaten	gamerphil	PHP Tipps 2009	2	21.03.2009 20:09
[Erledigt] MySQL Auswahl der vorhandenen IDs in einer Tabelle & dadurch auswahl ander	Enra	PHP Tipps 2009	11	20.03.2009 21:13
[Erledigt] Mysql Tabelle liest bei Float nix aus??	Toby	Datenbanken	10	19.02.2009 23:08
[Erledigt] Php Mysql ausgabe in Tabelle / Gruppentrennung	xitam	PHP Tipps 2008	3	28.05.2008 22:03
felder auf übereinstimmung prüfen		PHP Tipps 2007	2	19.11.2005 19:32
Suche Tipps für Persormance-Steigerung (Geld für Nützliches)		Beitragsarchiv	18	16.08.2005 10:57
Mysql Fehlermeldung gezielt prüfen	suter	PHP Tipps 2004	1	25.08.2004 23:28
tabelle in Mysql übertragen		Datenbanken	0	05.08.2004 10:23
Maximal Wert in MYSQL Tabelle nach id rausfinden?		PHP Tipps 2004	3	03.07.2004 23:27
[Erledigt] einlesen von .txt file in deine Tabelle unter MySQL		Datenbanken	1	16.06.2004 14:14

22.01.2010, 16:02
MrVienna Erfahrener Benutzer Registriert seit: 29.07.2009 Beiträge: 143 PHP-Kenntnisse: Anfänger	Mysql Übereinstimmung von Datensätzen innerhalb einer Tabelle prüfen An die Mysql Spezialisten: Ich bin mit meiner Ein-Tabellendatenbank (erste Normalform) bis jetzt gut ausgekommen. Die Spaltennamen bezeichnen den Inhalt des Datenfeldes und in das Datenfeld wird jeweils 1 oder 0 eingetragen, was aussagt, ob der Wahrheitsgehalt laut Spaltenname zutrifft oder nicht. Also ungefähr so: Code: ID Name Berufstätig Studiert Stinkreich Bettelarm 1 Alpha 1 0 1 0 2 Beta 0 1 0 1 Nun soll ich aber eine Beziehung innerhalb der Tabelle herstellen, einen Inner Join, mit dem ich herausfinden kann, zu wieviel Prozent ein Datensatz mit einem anderen Datensatz übereinstimmt. Das heißt es soll der Reihe nach die Übereinstimmung von jeweils einem Datensatz zu einem gegebenen Datensatz geprüft werden und anschließend die Datensätze nach der prozentmäßigen Übereinstimmung zu dem einen gegebenen Datensatz gereiht werden. Mir ist klar: Hätte ich eine zweite Tabelle und wären die Feldnamen untereinander gereiht, so könnte ich mit select und Wertevergleich einfach die zurückgegebenen rows erhalten und daraus die Übereinstimmung berechnen. Leider ist die Datenbank bereits ziemlich befüllt und mit PHP verknüpft, so dass ich einem Umbau der Datenbankstruktur in die zweite oder dritte Normalform eher mit Bedenken entgegensehe. Ich könnte auch die Datensätze aus der Datenbank holen und mit Php vergleichen und reihen. Aber ich glaube, dass dies bei vielen Datensätzen zu lange dauern wird? Vielleicht sehe ich auch den Wald vor lauter Bäume nicht mehr und es gibt eine einfache Lösung. Für jeden Hinweis wäre ich sehr dankbar.


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

22.01.2010, 20:45
ChrisvA Erfahrener Benutzer Registriert seit: 10.01.2010 Beiträge: 276 PHP-Kenntnisse: Fortgeschritten	Also, ich kenne mich mit MySQL zwar nicht so optimal aus, habe aber einen ordentlichen Ansatz: Du ließt den Datensatz, mit dem du vergleichst in PHP aus. Den absoluten Unterschied (Anzahl der Unterscheidungen) zwischen den unterschiedlichen Einträgen kannst du jeweils mit dem "Exklusiven Oder" (XOR) Operator (MySQL :: MySQL 5.1 Referenzhandbuch :: 12.1.4 Logische Operatoren) erreichen. Der Unterschied ergibt sich bei der Summer der einzelnen Vergleiche. Mit Vergleich meine ich das Ergebnis aus [Vergleichswert] XOR [Unterschiedswert] Den Absolutwert kannst du dann durch Teilen durch die Gesamtzahl der Spalten in deine gewünschte Prozentzahl umwandeln. Ja nachdem wie groß aber die Datenbank ist, solltest du dir im klaren sein, dass die Auslastung im DB-Server ziemlich groß sein. Ich hoffe du hast meinen Gedankengang verstanden, falls nicht frage einfach noch einmal nach. Mir ist aber leider gerade keine bessere Möglichkeit eingefallen das ganze in Worte zu fassen.

22.01.2010, 20:50
nikosch moderatives Dielektrikum Registriert seit: 21.05.2008 Beiträge: 34.256 PHP-Kenntnisse: Fortgeschritten	Für zwei Datensätze ist sowas einfach, für eine „gib mir für jeden Datensatz die Übereinstimmungen gegen einen bestimmten DS“ kann man so aber nicht mehr verfahren. Das Problem ist, dass Du durch Deine 1-Tabellen-Lösung bei jeder DB-Änderung den PHP-Code gleichfalls auf die neuen Felder anpassen mußt. Nicht sehr genial. __________________ -- One pixel is still too big. Please make it smaller. ASAP. Initiative Mittelstand. Die wichtigste Gestaltungsregel im Screendesign ist Pi mal Daumen des Arbeitgebers. --

23.01.2010, 19:56
MrVienna Erfahrener Benutzer Registriert seit: 29.07.2009 Beiträge: 143 PHP-Kenntnisse: Anfänger	Danke verspätet für eure Antworten. Habe leider erst jetzt wieder Zeit für mein Problem gefunden. @nikosch Damit hast du recht: Dafür was ich vorhabe ist eine 1-Tabelle nicht optimal. Eine Möglichkeit überlege ich: Ob ich eine zweite Tabelle – nur für diese Statistik - erzeugen soll, in der sich die gesuchten Daten ein zweites Mal befinden. Ich hätte dann allerdings 43 Datensätze - jeweils aus 0 oder 1 bestehend - zwei Mal in der Datenbank. @ChrisvA Danke für deine Überlegungen. Ich muss aber zugeben, dass ich noch nicht ganz durchsehe. Mir ist nicht klar, wie die Abfrage in der Praxis aussehen soll. Wenn ich das richtig gelesen habe, bekomme ich mit xor Null zurück wenn der Wert im Datenfeld Null ist. (nicht 0) Und über diesen Satz aus mysql.com (mittlerer Satzteil nach dem Beistrich) denke ich immer noch nach: Bei Nicht-NULL-Operanden wird 1 zurückgegeben, wenn eine ungerade Anzahl von Operanden nicht null ist, andernfalls wird 0 zurückgegeben. Nun gut, ich bin nach wie vor für jeden Vorschlag dankbar. Und auch für jede Aufklärung, falls ich jetzt etwas nicht richtig verstanden habe.

24.01.2010, 11:25
ChrisvA Erfahrener Benutzer Registriert seit: 10.01.2010 Beiträge: 276 PHP-Kenntnisse: Fortgeschritten	Also ich habe das ganze jetzt mal als MySQL-String formuliert und es scheint als kämen die richtigen Werte dabei raus: PHP-Code: SELECT ( (`v1`XOR 1 ) + (`v2`XOR 1 ) + (`v3`XOR 1 ) + (`v4`XOR 1 ) + (`v5`XOR 1 ) + (`v6`XOR 1 ) + (`v7`XOR 1 ) + (`v8`XOR 1 ) ) FROM `test1` Statt der 1 musst du eben jeweils die Werte von deinem Vergleichsausdruck einsetzen. v* seinen deine Spaltennamen.

24.01.2010, 17:25
MrVienna Erfahrener Benutzer Registriert seit: 29.07.2009 Beiträge: 143 PHP-Kenntnisse: Anfänger	@chrisvA Danke für deine Antwort. Nachdem ich in Mysql kein Experte bin, kann ich nicht ausschließen, dass ich bei deiner Anregung etwas übersehe. Möchte nur kurz beschreiben, wie ich deinen Vorschlag nachvollzogen habe: Code: Select Spaltenname xor Vergleichswert… Soweit, so gut. Die Abfrage hat funktioniert. Aber bei der Ausgabe der Daten: PHP-Code: `<? if ($anzahl>0) for ( $x = 0; $x < $anzahl; $x++ ) {$array = mysql_fetch_array($res,MYSQL_ASSOC); echo "</pre>"; while (list($key,$value) = each($array)) { echo "$key: $value " . "<br />";} echo "<br />"; } } ?>` Bekomme ich natürlich wieder nur den tatsächlichen Inhalt des Datensatzes und keinen Hinweis auf einen Unterschied zu dem Vergleichswert, egal wie ich diesen auch verändere. Oder gibt es einen anderen Weg, den Unterschied zu dem Vergleichswert aufzurufen ?

24.01.2010, 20:10
Harry_X Erfahrener Benutzer Registriert seit: 08.10.2009 Beiträge: 585 PHP-Kenntnisse: Anfänger	ich versteh das Problem nicht so ganz. Gehe ich hiervon aus: Code: ID Name Berufstätig Studiert Stinkreich Bettelarm 1 Alpha 1 0 1 0 2 Beta 0 1 0 1 so soll doch eine neue Tabelle/Matrix entstehen, die die Ähnlichkeiten eines Satzes mit einem anderen darstellt? Was dann so gemäß dem Beispiel aussähe: Code: 1 2 1 100% 0% 2 0% 100% (weil 1 zu 1/2 zu 2 natürlich zu 100% ähnlich ist, die sind natürlich vermutlich uninteressant) Ja, kommt denn mit dem SELECT XOR dingens genau das dabei heraus?

25.01.2010, 11:15
draco88 Erfahrener Benutzer Registriert seit: 01.12.2009 Beiträge: 645 PHP-Kenntnisse: Fortgeschritten	Nach ChrisvA's zweiter Erklärung ist mir langsam klar geworden, was er meint. Mit den ganzen XOR-Bedingen fragt er ab, wieviele Felder übereinstemmen, wenn man dazu dann noch die Anzahl an Feldern kennt kann man auch den Prozentsatz ausrechnen PHP-Code: $sql = " SELECT t1.name AS t1_name, t1.ID AS t1_id t2.name AS t2_name, t2.ID AS t2_id, ( (t1.Berufstätig NOR t2.Berufstätig) + (t1.Studiert NOR t2.Studiert) + (t1.Stinkreich NOR t2.Stinkreich) + (t1.Bettelarm NOR t2.Bettelarm) )/4*100 AS percent FROM tabelle t1 INNER JOIN tabelle t2 "; Mit der Abfrage wird jeder Datensatz mit jedem Verknüpft und die Prozentuale Übereinstimmung mit angegeben. Edit: Statt dem XOR sollt man besser NOR nehmen. Bei XOR würden 100% bedeuten, dass die beiden Datensätze garnicht übereinstimmen, bei NOR genau das gegenteil.

25.01.2010, 11:55
MrVienna Erfahrener Benutzer Registriert seit: 29.07.2009 Beiträge: 143 PHP-Kenntnisse: Anfänger	@draco88 Danke für die ausführliche Erklärung. Die leuchtet auch mir, als Msql-Einsteiger ein. Und dabei wollte ich das Ganze schon mit php erledigen, was aber sicher länger in der Ausführung gedauert hätte. Schade, dass ich jetzt noch Anderes erledigen muss und erst am späten Abend zum ersten Test komme. Ich werde aber dann – sollte alles gut gehen – den Erfolg verkünden.