MySQL寻找最相似的用户

Question

假设我有一个如下所示的表格:

Mark - Green
Mark - Blue
Mark - Red
Adam - Yellow
Andrew - Red
Andrew - Green

我的目标是将用户“Mark”与数据库中的所有其他用户进行比较，找出他与哪个其他用户最相似。在这种情况下，他与 Andrew 最相似(2/3 匹配)，与 Adam 最相似(0/3)匹配。在我找出哪个用户与 Mark 最相似之后，我想提取 Andrew 得到但 Mark 没有的全部内容。

这在 MySQL 中可行吗？我感谢所有的帮助，谢谢你们!

编辑:被所有的好 helper 淹没了!十分感谢大家!我一定会查看您的所有贡献!

Answer 1

以下查询尝试列出所有用户以及要Mark 的匹配项数。它基本上将表与 Mark 的条目连接起来，并计算所有用户的公共(public)条目。

SELECT ours.user, theirs.user, count(*) as `score`
FROM tableName as `theirs`, (SELECT *
                              FROM tableName
                              WHERE user = 'Mark') as `ours`
WHERE theirs.user != 'Mark' AND
      theirs.color = ours.color
GROUP BY theirs.user
ORDER BY score DESC

但是，如果存在重复数据(即一个人两次选择相同的颜色)，查询将不起作用。但这应该不是问题，因为您在评论中提到它不会发生。

可以修改查询以显示所有用户的分数:

SELECT ours.user as `myUser`, theirs.user as `theirUser`, count(*) as `score`
FROM tableName as `ours`, tableName as `theirs`
WHERE theirs.user != ours.user AND
      theirs.color = ours.color
GROUP BY ours.user, theirs.user
ORDER BY score DESC

让 Q 成为上面为您提供最相似用户的查询。拥有该用户后，您可以使用它来显示他们之间的不同条目。这是我们正在尝试做的事情:

SELECT * 
FROM tableName as theirs
WHERE user = 'Andrew'
AND NOT EXISTS (SELECT 1
                FROM tableName as ours
                WHERE ours.user = 'Mark'
                      AND ours.color = theirs.color)

替换 Q 中的输入 Andrew 和 Mark:

SELECT similar.myUser, theirs.user, theirs.color  
FROM tableName as theirs JOIN (Q) as similar
ON theirs.user = similar.theirUser
WHERE NOT EXISTS (SELECT 1
                  FROM tableName as ours
                  WHERE ours.user = similar.myUser
                        AND ours.color = theirs.color)

Here's the final query up and running .希望这是有道理的。