Wie übereinstimmende Datensätze zweier User finden?

rhodes

Benutzer
Beiträge
8
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)
entity_screenshot.JPG

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
 
Werbung:
Sowas in der Art ?
(Die With-Clause ist nur dafür da, um mir die Daten zu generieren ;) Hast mir ja keine gegeben :p )

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
 
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?
 
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
 
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.
 
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) &gt; 2
LIMIT 0, 30' at line 11

Was kann denn das sein?
 
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.
 
... und @Distrilec nutzt, soweit mir bekannt, auch kein MySQL.
@akretschmer Richtig, ich hab hier Oracle am rennen :)
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) &gt; 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
 
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.

flag_recommend.JPG

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
 
Werbung:
Zurück
Oben