Get shared rows in one table

Question

I had a little search, but did not find anything similar to what I am trying to achieve.

Basically, I am trying to find a similarity between the habits for voting between two users.

I have a table that stores every single voice that stores:

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

I am going to calculate the similarity between, say, users A and B by discovering two things:

• The number of votes that they have in common . That is, the number of times they voted at one post (the direction at this stage does not matter).
• How many times have they voted in the same direction, by common vote .

(Then just calculate # 2 as a percentage of # 1 to achieve a rough similarity score).

My question is: how do I find the intersection between the user votes? (that is, how can I correctly calculate point # 1 without looping around each vote is extremely inefficient.) If they were in different tables, I would suggest INNER JOIN ... but this obviously will not work in the same table (or will ?).

We will be very grateful for any ideas.

Answer 1

Something like that:

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

Here is an example to get you closer:

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 3

, 1 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 4

. , :

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 5

( , ), , :

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 6

, , . , , . . FK, Union . , Union All .