Wie übereinstimmende Datensätze zweier User finden?

rhodes · 19 Januar 2015

Hallo zusammen,

in einem CMS-System können Benutzer Artikel mit einem "Gefällt mir" Button bewerten. Dieser Status wird in einer Mysql-Tabelle "flagging" gespeichert.

Die Tabelle hat folgende Felder (siehe dazu auch Screenshot)

flagging_id: eine fortlaufende Indexnummer
fid: die Flagging ID, wobei "Gefällt mir" fid=1 bedeutet.
entity_type: ist immer node (für dieses Beispiel uninteressant)
entity_id: die eindeutige id des Dokuments, für das ein Benutzer "Gefällt mir" geklickt hat.
uid: die eindeutige Benutzer-ID des Benutzers, der "Gefällt mir" geklickt hat.
sid: session id (für dieses Beispiel uninteressant)

So, und jetzt zur SQL-Frage, an der ich mir seit Tagen die Zähne ausbeisse.

Finde für den User A mit uid=4 einen beliebigen weiteren User B, dem mindestens drei gleiche Dokumente wie A gefallen und zeige anschließend alle weiteren Dokumente von B an, die dieser mit "Gefällt mir" markiert hat.

Vielleicht erleichtert ein Beispiel die Anforderung,

User A hat Dokumente mit folgender entity_id als "Gefällt mir" (fid=1) eingestuft.
25 37 128 732

User B hat Dokumente mit folgender entity_id als "Gefällt mir" (fid=1) eingestuft.
25 37 128 256 834

Das Ergebnis der Abfrage sollten die IDs 256 und 834 sein.

Damit habe ich es versucht, aber das ist leider falsch ;-)

select entity_id from table where entity_id NOT IN (select entity_id from table where uid =4 AND fid = 1) AND fid = 1;

Hat jemand einen Tipp für mich?

Danke & Grüße

Rhodes

Distrilec · 19 Januar 2015

Sowas in der Art ?
(Die With-Clause ist nur dafür da, um mir die Daten zu generieren

Hast mir ja keine gegeben

)

Note* Kann sein das du die Aliase ändern musst, weiß nicht ob MySQL das "as" versteht ^^

Code:

With tab As (Select  3 as flagging_id, 1 as fid, 'node' as entity_type,  83 as entity_id, 1 as "uid", 0 as "sid", systimestamp as "timestamp" From dual Union All
Select 25, 1, 'node',  25, 1, 0, systimestamp From dual Union All
Select 27, 1, 'node', 123, 1, 0, systimestamp From dual Union All
Select 28, 1, 'node', 124, 1, 0, systimestamp From dual Union All
Select 30, 1, 'node', 133, 1, 0, systimestamp From dual Union All
Select 32, 1, 'node',  66, 1, 0, systimestamp From dual Union All
Select 33, 1, 'node', 160, 1, 0, systimestamp From dual Union All
Select 40, 1, 'node', 140, 1, 0, systimestamp From dual Union All
Select 41, 1, 'node', 137, 1, 0, systimestamp From dual Union All
Select 47, 1, 'node', 166, 1, 0, systimestamp From dual Union All
Select 27, 1, 'node', 123, 2, 0, systimestamp From dual Union All
Select 28, 1, 'node', 124, 2, 0, systimestamp From dual Union All
Select 30, 1, 'node', 133, 2, 0, systimestamp From dual Union All
Select 32, 1, 'node',  66, 2, 0, systimestamp From dual Union All
Select 33, 1, 'node', 160, 2, 0, systimestamp From dual Union All
Select 40, 1, 'node', 140, 2, 0, systimestamp From dual Union All
Select 41, 1, 'node', 137, 2, 0, systimestamp From dual Union All
Select 47, 1, 'node', 166, 2, 0, systimestamp From dual Union All
Select 47, 1, 'node', 166, 3, 0, systimestamp From dual Union All
Select 27, 1, 'node', 123, 3, 0, systimestamp From dual Union All
Select 28, 1, 'node', 124, 3, 0, systimestamp From dual Union All
Select 30, 1, 'node', 133, 3, 0, systimestamp From dual Union All
Select 32, 1, 'node',  66, 3, 0, systimestamp From dual Union All
Select 33, 1, 'node', 160, 3, 0, systimestamp From dual Union All
Select 40, 1, 'node', 140, 3, 0, systimestamp From dual Union All
Select 41, 1, 'node', 137, 3, 0, systimestamp From dual)
Select t."uid" As user_1,
  t."u2id" As user_2,
  Count(t.entity_id) As matches
From  (Select t1."uid",
  t2."uid" As "u2id",
  t2.entity_id
  From  tab t1
  Left  Join tab t2
  On  t2."uid" <> t1."uid"
  And  t2.entity_id = t1.entity_id
  Where  t1."uid" = 1) t
Group  By t."uid",
  t."u2id"
Having Count(t.entity_id) > 2

rhodes · 19 Januar 2015

Vielen Dank @Distrilec . Ich probiere es heute abend gleich aus...

rhodes · 20 Januar 2015

Hm, zu früh gefreut. Klappt leider nicht, @Distrilec .

>>From tab t1 Left Join tab t2
Es gibt eigentlich nur eine Tabelle "flagging" und wenn ich die anstelle von tab t1 setze, dann ist "Left Join tab t2" undefiniert. und müsste nicht auch fid = 1 irgendwo berücksichtig sein?

Distrilec · 20 Januar 2015

Du joinst die Tabelle mit sich selbst...

Ehm ja... die fid hab ich irgendwie vergessen ^^ Unter die Zeile

Code:

Where  t1."uid" = 1

muss ein

Code:

And t1."fid" = 1

akretschmer · 20 Januar 2015

rhodes schrieb:
Hm, zu früh gefreut. Klappt leider nicht, @Distrilec .

Just for fun eine Lösung, die PostgreSQL funktioniert. Falls mal jemand solche ein Problem UND PostgreSQL hat ;-)

Die Tabelle:

Code:

test=*# select * from rhodes;
 id | entity_id | uid
----+-----------+-----
  1 |  1 |  1
  2 |  1 |  2
  3 |  1 |  3
  4 |  1 |  4
  5 |  2 |  1
  6 |  2 |  4
  7 |  3 |  1
  8 |  3 |  4
  9 |  5 |  1
 10 |  6 |  1
 11 |  6 |  2
 12 |  7 |  1
 13 |  7 |  2
(13 rows)

So, nun mal alle gegen den uid = 4 prüfen und schauen, was der so an entity_id hat und die Anzahl der Übereinstimmungen:

Code:

test=*# select *, (select count (*) from (select unnest(entity) except select unnest(uid_4))x) from (select uid, array_agg(entity_id) as entity, (select array_agg(entity_id) from rhodes where uid = 4) as uid_4 from rhodes where uid != 4 group by uid) foo;
 uid |  entity  |  uid_4  | count
-----+---------------+---------+-------
  1 | {1,2,3,5,6,7} | {1,2,3} |  3
  3 | {1}  | {1,2,3} |  0
  2 | {1,6,7}  | {1,2,3} |  2
(3 rows)

In der uid_4 - Spalte siehst noch mal, was der hat, in der entity-Spalte die Auflistung der jeweiligen uid, in count die Anzahl der Übereinstimmungen.

rhodes · 23 Januar 2015

Danke @akretschmer für die Postgres Lösung. Leider muss ich aber eine Lösung für SQL finden.

@Distrilec - das sieht wirklich vielversprechend aus, wirft aber immer Fehler. D.h. ich habe ja nur eine einzige Tabelle flagging. Wenn ich jetzt in deiner Abfrage t, t1 und t2 durch flagging ersetze, also so

Select t.uid As user_1,
t.u2id As user_2,
Count(t.entity_id) As matches
From (Select t1.uid,
t2.uid As u2id,
t2.entity_id
From flagging t1
Left Join flagging t2
On t2.uid <> t1.uid
And t2.entity_id = t1.entity_id
Where t1.uid = 1) flagging t
Group By t1.uid,
t2.u2id
Having Count(t.entity_id) > 2

dann bekomme ich einen SQL-Fehler

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't
Group By t1.uid,
t2.u2id
Having Count(t.entity_id) > 2
LIMIT 0, 30' at line 11

Was kann denn das sein?

akretschmer · 23 Januar 2015

rhodes schrieb:
Leider muss ich aber eine Lösung für SQL finden.

MySQL hat allerdings mit dem SQL-Standard nicht allzu viel zu tun ... und @Distrilec nutzt, soweit mir bekannt, auch kein MySQL.

rhodes · 23 Januar 2015

akretschmer schrieb:
MySQL hat allerdings mit dem SQL-Standard nicht allzu viel zu tun ... und @Distrilec nutzt, soweit mir bekannt, auch kein MySQL.

Das ist ein klein wenig bedauerlich. Da ich aber mit MySQL arbeiten muss hilft mir am Ende des Tages auch nur eine MySQL-Lösung, auch wenn ich die anderen Vorschläge sehr zu schätzen weiss.

ukulele · 26 Januar 2015

Unabhängig davon ob MySQL oder jedes andere SQL hat dein Code aber tatsächlich Syntaxfehler. Du hast zwei Tabellen-Aliase für dein Subselect und in GROUP BY die Aliase aus dem Subselect und nicht t verwendet. Die sind SQL dort aber nicht bekannt.

Code:

SELECT    t.uid AS user_1,
        t.u2id AS user_2,
        count(t.entity_id) AS matches
FROM    (

SELECT    t1.uid,
        t2.uid AS u2id,
        t2.entity_id
FROM    flagging t1
LEFT JOIN flagging t2
ON        t2.uid <> t1.uid
AND        t2.entity_id = t1.entity_id
WHERE    t1.uid = 1

        ) t
GROUP BY t.uid,t.u2id
HAVING count(t.entity_id) > 2

Das ganze hättest du vieleicht mit etwas Formartierung besser erkennt.

Distrilec · 26 Januar 2015

akretschmer schrieb:
... und @Distrilec nutzt, soweit mir bekannt, auch kein MySQL.

@akretschmer Richtig, ich hab hier Oracle am rennen

rhodes schrieb:
Danke @akretschmer für die Postgres Lösung. Leider muss ich aber eine Lösung für SQL finden.

@DistrilecYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't
Group By t1.uid,
t2.u2id
Having Count(t.entity_id) > 2
LIMIT 0, 30' at line 11

Was kann denn das sein?

@rhodes Sieht so aus als hätte dein MySQL probleme mit der Having-Clause ? Oder dem Limit dahinter ?...
EDIT:

Oder es liegt einfach an der Unfähigkeit meinerseits... Falsche Aliase benutzt... Das sollte funktionieren

Code:

Select t.uid As user_1,
       t.u2id As user_2,
       Count(t.entity_id) As matches
From   (Select t1.uid,
               t2.uid As u2id,
               t2.entity_id
        From   flagging t1
        Left   Join flagging t2
        On     t2.uid <> t1.uid
        And    t2.entity_id = t1.entity_id
        Where  t1.uid = 1) flagging t
Group  By t.uid,
          t.u2id
Having Count(t.entity_id) > 2

ukulele · 26 Januar 2015

Distrilec schrieb:
Where t1.uid = 1) flagging t

Auch hier dopppelter Tabellen Alias

Distrilec · 26 Januar 2015

@ukulele JA... Wie mir gerade aufgefallen ist hat @rhodes wohl an meinem Select rumgewurschtelt und die Fehler selbst eingebaut...
Und ich dachte schon es war meine Schuld...

rhodes · 28 Januar 2015

Ja @Distrilec , ich habe selber herumprobiert. War wohl keine so gute Idee. Danke Euch beiden , @ukulele Jetzt läuft die SQl Abfrage sauber durch. Allerdings zeigt sie noch nicht ganz das Gewünschte an.

z.B. in Zeile 2. Mir ist nicht ganz klar was in der Spalte matches angezeigt wird. User_2 mit der ID 5 hat insgesamt 27 Dokumente mit einem Flag (gefällt mir) versehen (das habe ich nachgezählt), kann also eigentlich nicht 56 gleiche Flags wie user 1 haben. Hier wird also irgendetwas anderes aufsummiert.

Das ist aber halb so wild, denn angezeigt werden soll eigentlich ohnehin etwas anderes (siehe oben im Beitrag)

Vielleicht erleichtert ein Beispiel die Anforderung:

Finde für den User 1 einen beliebigen weiteren User n, dem mindestens drei gleiche Dokumente wie user 1 gefallen und zeige anschließend alle weiteren Dokumente von user n an, die dieser ebenfalls mit "Gefällt mir" markiert hat.

User 1 hat Dokumente mit folgender entity_id als "Gefällt mir" (fid=1) eingestuft.
25 37 128 732

User n hat Dokumente mit folgender entity_id als "Gefällt mir" (fid=1) eingestuft.
25 37 128 256 834

Das Ergebnis der Abfrage sollten die IDs 256 und 834 sein. Diese beiden Dokumente könnte man user 1 jetzt anzeigen, denn da user 1 und user n 3 Übereinstimmungen haben (nämlich die IDs 25,37 und 128) ist die Wahrscheinlichkeit hoch, dass dem user 1 auch die Dokumente 256 und 834 gefallen.

Sorry für meine komplizierten Fragen
Danke & Grüße, rhodes

akretschmer · 28 Januar 2015

rhodes schrieb:
Jetzt läuft die SQl Abfrage sauber durch. Allerdings zeigt sie noch nicht ganz das Gewünschte an.

Na Hauptsache es funzt, gell?

SCNR

Wie übereinstimmende Datensätze zweier User finden?

rhodes

Benutzer

Distrilec

Datenbank-Guru

rhodes

Benutzer

rhodes

Benutzer

Distrilec

Datenbank-Guru

akretschmer

Datenbank-Guru

rhodes

Benutzer

akretschmer

Datenbank-Guru

rhodes

Benutzer

ukulele

Datenbank-Guru

Distrilec

Datenbank-Guru

ukulele

Datenbank-Guru

Distrilec

Datenbank-Guru

rhodes

Benutzer

akretschmer

Datenbank-Guru