Получить общие строки в одной таблице

У меня был небольшой поиск, но я не нашел ничего похожего на то, чего я пытаюсь достичь.

По сути, я пытаюсь найти сходство между привычками голосования двух пользователей.

У меня есть таблица, в которой хранится каждое отдельное голосование, в котором хранятся:

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 было бы достаточно, я думаю... но это, очевидно, не будет работать на одной и той же таблице (или будет?).

Любые идеи очень приветствуются.


mysql sql database self-join relational
person James B    schedule 04.02.2009    source источник

Ответы (6)


arrow_upward
6
arrow_downward

Что-то вроде этого:

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

arrow_upward
3
arrow_downward

Если вы хотите сделать это для одного пользователя (вместо того, чтобы знать обоих пользователей в начале), чтобы найти, кому они ближе всего:

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 лучших, все и т. д.).

Я еще не проверял это, поэтому дайте мне знать, если это не работает так, как ожидалось.

person Tom H    schedule 04.02.2009

arrow_upward
2
arrow_downward

Вот пример, который должен приблизить вас:

SELECT COUNT(*)
FROM (
      SELECT u1.userID
      FROM vote u1, vote u2
      WHERE u1.itemID = u2.itemID
      AND u1.userID = user1
      AND u2.userID = user2)
person Lance Roberts    schedule 04.02.2009
comment
Возможно, вы можете указать мне на какую-нибудь статью о соединениях, в которой объясняется, почему один способ лучше другого. У меня нет опыта работы с SQL. - person Lance Roberts; 04.02.2009
comment
Все дело в удобочитаемости и простоте понимания. Посмотрите этот ответ: bit.ly/ansijoin - person ninesided; 05.02.2009

arrow_upward
2
arrow_downward

Предполагая, что 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;
person Chad Birch    schedule 04.02.2009

arrow_upward
2
arrow_downward

Самосоединение в порядке. Вот он со всем, что вы просили:

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
person Carlos A. Ibarra    schedule 04.02.2009

arrow_upward
1
arrow_downward

Вы наверняка можете соединить таблицу с самой собой. На самом деле, это то, что вам придется сделать. Вы должны использовать псевдонимы при присоединении таблицы к самой себе. Если в вашей таблице нет PK или FK, вместо этого вам придется использовать Union. Union удалит дубликаты, а Union All — нет.

person GregD    schedule 04.02.2009
comment
Но как вы собираетесь присоединиться к нему в одном столбце с разными идентификаторами пользователей? Вы могли бы привести пример - поскольку я явно что-то упускаю из виду, здесь... - person James B; 04.02.2009
comment
Я собирался, но девятисторонний меня слишком сильно опередил. - person GregD; 04.02.2009
comment
мой плохой, ваш пост появился на полпути, пока я печатал свой! - person ninesided; 04.02.2009