mySQL: Benutzer mit den meisten persönlichen Übereinstimmungen

[jumini]

Hallo zusammen,

nach langem und stillen mitlesen, wage ich mich nun an meinen ersten Beitrag. Meine Problematik erfordert eine überdurchschnittliche Ausführung, ich bedanke mich im Voraus für jede Hilfe:

Ich speichere in der Tabelle 'history' die Chronik eines Surfers (URLs) mit Bewertungen (history_vote).
CREATE TABLE IF NOT EXISTS history (
  history\_user int(11) unsigned NOT NULL,
  history\_link int(11) unsigned NOT NULL,
  history\_vote enum('awsome','nice','ok') COLLATE latin1_german1_ci NOT NULL,
) ENGINE=MyISAM;

Nun möchte ich - ausgehend von meinem Benutzer (nehmen wir an, ich habe die ID 1) - die 2 (oder n) Benutzer herausfinden, welche die meisten Übereinstimmungen mit den von mir als 'awsome' oder 'nice' bewerteten URLs haben.

Das Ergebnis soll eine Liste der User mit ID sowie der Anzahl von Übereinstimmungen im Verhältnis zum eigenen Benutzer enthalten.
Also z.B.
user    matches
2       2
3       1
-> Dabei würden folgende Datensätze bestehen: (ID 1 = eigener User)
INSERT INTO history (history\_user, history\_link, history\_vote) VALUES
(1, 1, 'awsome'),
(1, 2, 'nice'),
(1, 3, 'awsome'),
(2, 1, 'nice'),
(2, 3, 'awsome'),
(2, 2, 'ok'),
(3, 2, 'awsome)

Nach einigem Kopfzerbrechen ist mir folgender Lösungsansatz eingefallen:
1. Ich ermittle eine begrenzte Zahl der Benutzer welche den Link (history_link) meiner eigenen Einträge (history_user = 1) 'awsome' oder 'nice' fanden.
2. Von diesen selektiere ich alle (awsome & nice) Einträge der Tabelle
3. und überprüfe ob mein Benutzer ebenfalls einen passenden Eintrag vorliegen hat - gegebenenfalls erhöhe ich manuell eine Variable für die Benutzer-Übereinstimmungen.

Das scheint mir aber bei großen Datenbeständen sehr umfangreich zu werden, daher strebe ich nach einer alternativen Idee. Kreativität erwünscht ;)

Grüße
jumini

[Tom]

Hello,

wenn ich dich richtig verstehe, möchtest Du also zählen, wie oft einem Benutzer welche Eigenschaft zugewiesen wurde?

Dann kannst Du gruppieren und zählen.

BTW: meintest Du 'awesome' oder 'awful'?

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[EKKi]

Mahlzeit Tom,

wenn ich dich richtig verstehe, möchtest Du also zählen, wie oft einem Benutzer welche Eigenschaft zugewiesen wurde?

Und wenn ich jumini richtig verstanden habe, geht es nicht um die reine Anzahl, sondern darum, bei *welchen* Links *welche* Bewertung gegeben wurde und bei *wievielen* "Bewertungen" diese mit welchen anderen Benutzern übereinstimmt ...

MfG,
EKKi

[Vinzenz Mai]

Hallo,

Ich speichere in der Tabelle 'history' die Chronik eines Surfers (URLs) mit Bewertungen (history_vote).

zum Nachvollziehen ein paar kleine Syntaxkorrekturen (die durch das Reduzieren des Codes wohl verursacht sind)

CREATE TABLE IF NOT EXISTS history (
  history\_user int(11) unsigned NOT NULL,
  history\_link int(11) unsigned NOT NULL,
  history\_vote enum('awsome','nice','ok') COLLATE latin1_german1_ci NOT NULL    -- kein Komma hinter der letzten Spaltendefinition
) ENGINE=MyISAM;

Nun möchte ich - ausgehend von meinem Benutzer (nehmen wir an, ich habe die ID 1) - die 2 (oder n) Benutzer herausfinden, welche die meisten Übereinstimmungen mit den von mir als 'awsome' oder 'nice' bewerteten URLs haben.

Das Ergebnis soll eine Liste der User mit ID sowie der Anzahl von Übereinstimmungen im Verhältnis zum eigenen Benutzer enthalten.

1. Ich ermittle eine begrenzte Zahl der Benutzer welche den Link (history_link) meiner eigenen Einträge (history_user = 1) 'awsome' oder 'nice' fanden.

sprich: 'awsome' und 'nice' sind hier gleichwertig.

Diese Definition hatte ich anfangs übersehen und mich daher über Dein Ergebnis bei den vorliegenden Daten gewundert.

Also z.B.
user    matches
2       2
3       1

-> Dabei würden folgende Datensätze bestehen: (ID 1 = eigener User)
INSERT INTO history (history\_user, history\_link, history\_vote) VALUES
(1, 1, 'awsome'),
(1, 2, 'nice'),
(1, 3, 'awsome'),
(2, 1, 'nice'),
(2, 3, 'awsome'),
(2, 2, 'ok'),
(3, 2, 'awsome')    -- schließendes Anführungszeichen vergessen

sieht doch gar nicht soo kompliziert aus:

a) die anderen Bewertungen:
   es interessieren nur die, die von anderen mit 'awsome' oder 'nice' bewertet sind:

SELECT                  -- gib mir  
    history_user user,       -- alle Spalteninhalte  
    history_link link,       --  
    history_vote vote       -- sagte ich schon mal, dass ich von Tabellenpräfixen  
                        -- in Spaltennamen überhaupt nichts halte? :-)  
FROM                    -- aus  
    history             -- der Tabelle history  
WHERE                   -- wobei nur die interessieren,  
    history_user <> 1   -- die von anderen sind  
AND                     -- und  
    history_vote IN ('awsome', 'nice')   -- in den gewünschten Bewertungskategorien sind

liefert:

user | link | vote
------------------
   2 |    1 | nice
   2 |    3 | awsome
   3 |    2 | awsome

Anmerkung:
Die Beispieldaten sind etwas ungünstig gewählt, denn ein Zählen hier lieferte zufälligerweise bereits das richtige Ergebnis.

Diese Bewertungen müssen nun noch mit den eigenen Bewertungen verglichen werden. Die eigenen erhältst Du über

SELECT  
    history_user user,  
    history_link link,  
    history_vote vote  
FROM  
    history  
WHERE  
    history_user = 1  
AND  
    history_vote IN ('awsome', 'nice')

Du willst die Bewertungen der anderen mit den eigenen Bewertungen verknüpfen, um die Übereinstimmungen herausfinden zu können. Die Verknüpfungsoperation in SQL heißt JOIN. Man kann nicht nur Tabellen miteinander joinen, sondern auch Tabellen mit Subselects. Wir werden das nutzen.

Du benötigst die Anzahl *aller* anderen User, die schon abgestimmt haben. Dabei kann es vorkommen, dass es *keine* Übereinstimmung gibt. In diesem Fall sollte das Ergebnis 0 sein. Ich gehe davon aus, dass Du auch diese Benutzer aufführen willst. Deswegen benötigen wir *alle* Datensätze aus unserer ersten Abfrage, die wir mit dem Ergebnis der zweiten Abfrage joinen und müssen daher einen OUTER JOIN nutzen, genauer einen LEFT JOIN.

Ganz so stimmt das zwar nicht, denn

a) ist die Einschränkung auf die richtige Bewertungskategorie eigentlich
   Teil der Joinbedingung.
b) verschiebe ich die Einschränkung "nicht der betrachtete Benutzer" in die
   WHERE-Klausel der gesamten Abfrage, ohne dass ich dadurch das Ergebnis
   verändere:

Da ich zweimal auf die gleiche Tabelle zugreife, muss ich zwangsläufig auf Aliasnamen für die Tabelle zurückgreifen, um Teil 1 von Teil 2 unterscheiden zu können, siehe Abschnitt Selfjoin im Selfwiki-Artikel:

  
SELECT  
    andere.history_user,  
    andere.history_link,  
    andere.history_vote,  
    meine.user,  
    meine.link,  
    meine.vote  
FROM  
    history andere  
LEFT JOIN (  
    SELECT  
        history_user user,  
        history_link link,  
        history_vote vote  
    FROM  
        history  
    WHERE  
        history_user = 1  
    AND  
        history_vote IN ('awsome', 'nice')  
) meine  
ON  
    andere.history_link = meine.link  
AND  
    andere.history_vote IN ('awsome', 'nice')  
WHERE  
    andere.history_user <> 1

liefert

history_user | history_link | history_vote | user | link | vote
----------------------------------------------------------------
           2 |            1 | nice         |    1 |    1 | awsome
           2 |            3 | awsome       |    1 |    3 | awsome
           2 |            2 | ok           | NULL | NULL | NULL
           3 |            2 | awsome       |    1 |    2 | nice

Beachte, dass auch das ok-Voting angezeigt wird. Das ist auch sinnvoll, denn es könnte ja sein, dass ein Benutzer nur ein ok-Voting vorgenommen hat. Dies ist übrigens Folge davon, dass ich die Bedingung

andere.history_vote IN ('awsome', 'nice')

in die Join-Klausel aufgenommen habe.

Um das gewünschte Ergebnis zu erhalten, müssen wir nur die Einträge in der Spalte: meine.user zählen und nach andere.history_user gruppieren und wie gewünscht zu sortieren. Alle anderen Spalten interessieren gar nicht:

SELECT  
    andere.history_user user,  
    COUNT(meine.user) matches  
FROM  
    history andere  
LEFT JOIN (  
    SELECT                                   -- meine Bewertungen  
        history_user user,  
        history_link link,  
        history_vote vote  
    FROM  
        history  
    WHERE  
        history_user = 1  
    AND  
        history_vote IN ('awsome', 'nice')   -- in der gewünschten Kategorie  
) meine  
ON  
    andere.history_link = meine.link  
AND  
    andere.history_vote IN ('awsome', 'nice')  
WHERE  
    andere.history_user <> 1  
GROUP BY  
    andere.history_user  
ORDER BY  
    COUNT(meine.user) DESC       -- die mit den meisten Übereinstimmungen oben  

liefert das gewünschte (und das nicht nur zufällig):

user | matches
--------------
   2 |     2
   3 |     1

Wie es mit der Performance aussieht, kannst Du mit EXPLAIN prüfen.
Du solltest auf jeden Fall einen Index auf der Spalte history_user und einen auf der Spalte history_link haben.

Füge zum Beispiel folgende Datensätze hinzu

INSERT INTO history VALUES
(4, 1, 'ok'),
(5, 4, 'awsome');

die beide nicht zu einer Übereinstimmung führen:
Erster ist nicht in der geforderten Bewertungskategorie,
zweiter vom ersten Benutzer nicht bewertet.

und führe die Abfrage erneut aus. Du erhältst das Ergebnis, das ich als gewünscht ansehe:

user | anzahl
-------------
   2 |     2
   3 |     1
   4 |     0
   5 |     0

(die letzten beiden Datensätze könnten natürlich auch vertauscht sein).

Abschließende Bemerkung: ganz sicher gibt es auch andere Wege ...

Freundliche Grüße

Vinzenz

[jumini]

Hallo Vinzenz,

Wow.
Mit einer dermaßen ausführenden und lösenden Antwort hätte ich mit dem großen Optimismus nicht gerechnet,
damit hast du mir den Tag gerettet - herzlichen Dank!

Für alle Anderen: natürlich schreibt man 'awsome' mit einem zweiten e.
Ich übe die Awesomeness noch.

Die Ergebnisse sind wie gewünscht und meine Fragen wurden auch alle beantwortet,
ich lese mich nun ausführlicher in die Self-Joins ein :)

Darf ich dich in Zukunft bei ähnlichen Problemen & Fragen auf geschäftlicher Basis kontaktieren
oder fragt man soetwas bei selfHTML nicht?

Der zufriedenste User,
jumini