php - SQL中的匹配兴趣(最近邻)搜索

Question

我正在尝试使用以下模式找到具有相似兴趣集的用户..

USERS - ID name etc

Interests - ID UID PID

其中 ID 是兴趣的唯一 ID，UIS 是用户 ID，PID 是产品 ID。我在 SO 看过其他类似的问题，但没有一个有确切的答案。

示例 - 假设我有兴趣吸引与 John 有相似兴趣的用户，这就是两个表的样子......

ID  Name
11  John
12  Mary
13  Scott
14  Tim

ID UID PID
3  12  123
4  12  231
5  12  612
6  13  123
7  13  612
8  14  931
9  14  214
10 11  123
11 11  231
12 11  781
13 11  612

我想要一个按顺序排列的结果。

我正在考虑做一组我感兴趣的用户与所有其他用户的交集。这听起来不是一个很好的解决方案，因为每次用户添加兴趣或添加另一个用户时都必须执行此操作。这是一个小项目，截至目前，我会将用户限制在 100 个以内。我仍然认为上述方法根本不会有效，因为它需要 100² 时间。

有人可以指导我正确的方向吗？有哪些可能的解决方案，以及在上述给定约束条件下哪一个是最好的。我在看 ANN看看我是否可以使用它。

Answer 1

首先计算每个用户与 John 的共同兴趣的数量。该方法是获取 John 的所有兴趣，重新加入兴趣表并聚合到共同兴趣的计数。这是用于该操作的 SQL:

select i.uid, COUNT(*) as cnt
from (select i.*
      from interests i join
           users u
           on i.uid = i.id
      where u.name = 'John'
     ) ilist join
     interests i
     on ilist.pid = i.pid and
        ilist.uid <> i.uid  -- forget about John
group by i.uid

但是，您实际上需要的是产品列表，而不仅仅是数量。所以，你必须重新加入兴趣表:

select i.*
from (select i.uid, COUNT(*) as cnt
      from (select i.*
            from interests i join
                 users u
                 on i.uid = i.id
            where u.name = 'John'
           ) ilist join
           interests i
           on ilist.pid = i.pid and
              ilist.uid <> i.uid  -- forget about John
      group by i.uid
     ) t join
     interests i
     on t.uid = i.uid
group by t.cnt, i.uid