获取同一个表中的公共行

Question

我进行了一些搜索，但没有找到任何与我想要实现的目标类似的东西。

基本上，我试图找到两个用户投票习惯之间的相似之处。

我有一个表存储每个单独的投票，其中存储：

voteID
itemID     (the item the vote is attached to)
userID     (the user who voted)
direction  (whether the user voted the post up, or down)

我的目标是通过找出两件事来计算用户 A 和 B 之间的相似度：

1. 他们共同的投票数。 也就是说，他们对同一个帖子投票的次数（此时方向并不重要）。
2. 他们在共同投票中向同一方向投票的次数。

（然后简单地计算#2占#1的百分比，以获得粗略的相似度评级）。

我的问题是，如何找到两个用户投票集之间的交集？（即如何充分计算第 1 点，而不用以非常低效的方式循环每个投票。）如果它们位于不同的表中，我想 INNER JOIN 就足够了......但这显然不适用于同一张表（或者会吗？）。

任何想法将不胜感激。

Answer 1

像这样的事情：

SELECT COUNT(*)
FROM votes v1
INNER JOIN votes v2 ON (v1.item_id = v2.item_id)
WHERE v1.userID = 'userA'
AND v2.userUD = 'userB'

Answer 2

如果您想对单个用户执行此操作（而不是一开始就知道两个用户）以查找他们最接近的匹配项：

SELECT
     v2.userID,
     COUNT(*) AS matching_items,
     SUM(CASE WHEN v2.direction = v1.direction THEN 1 ELSE 0 END) AS matching_votes
FROM
     Votes v1
INNER JOIN Votes v2 ON
     v2.userID <> v1.userID AND
     v2.itemID = v1.itemID
WHERE
     v1.userID = @userID
GROUP BY
     v2.userID

您可以根据您认为合适的方式进行限制（返回前 10 个、前 20 个、所有等）

我还没有对此进行测试，所以如果它没有按预期运行，请告诉我。

Answer 3

这是一个应该让您更接近的示例：

SELECT COUNT(*)
FROM (
      SELECT u1.userID
      FROM vote u1, vote u2
      WHERE u1.itemID = u2.itemID
      AND u1.userID = user1
      AND u2.userID = user2)

Answer 4

假设将 userID 1 与 userID 2 进行比较

用于查找他们有多少共同投票：

SELECT COUNT(*)
FROM Votes AS v1
INNER JOIN Votes AS v2 ON (v2.userID = 2
                            AND v2.itemID = v1.itemID)
WHERE v1.userID = 1;

用于查找他们何时也投票相同：

SELECT COUNT(*)
FROM Votes AS v1
INNER JOIN Votes AS v2 ON (v2.userID = 2
                            AND v2.itemID = v1.itemID
                            AND v2.direction = v1.direction)
WHERE v1.userID = 1;

Answer 5

自加入是有序的。 这是您所问的所有内容：

SELECT v1.userID user1, v2.userID user2,
  count(*) n_votes_in_common,
  sum(case when v1.direction = v2.direction then 1 else 0 end) n_votes_same_direction,
  (n_votes_same_direction * 100.0 / n_votes_in_common) crude_similarity_percent 
FROM votes v1
INNER JOIN votes v2
ON v1.item_id = v2.item_id

Answer 6

您当然可以将一个表与其自身连接起来。 事实上，这就是你必须要做的。 将表与其自身连接时必须使用别名。 如果您的表没有 PK 或 FK，则必须使用 Union。 Union 将删除重复项，而 Union All 则不会。