mysql - 如何比较行之间的值并找到响应的平均值？

Question

我有一个 MySQL 表，其中包含用户对是/否投票问题的回答。看起来有点像这样:

| user_id    | poll_id  | response  |
|------------|----------|-----------|
|    111     |    1     |   'yes'   |
|    111     |    2     |   'no'    |
|    111     |    3     |   'no'    |
|    222     |    1     |   'yes'   |
|    222     |    2     |   'yes'   |
|    222     |    3     |   'yes'   |
|    333     |    1     |   'no'    |
|    333     |    2     |   'no'    |
|    333     |    3     |   'no'    |

我想计算每个用户的响应与其他所有用户的响应之间的相似度。因此，用户 111 和用户 222 的相似度为 0.333(因为他们有 3 个相同响应中的 1 个)，用户 111 和用户 333 有 0.666 相似度(因为他们有 3 个相同响应中的 2 个)。

我编写了一个查询，它会为我提供两个指定用户的相同响应的数量:

SELECT  COUNT(*) AS same_count 
FROM    (
            SELECT  response 
            FROM    results 
            WHERE   user_id = 111
        ) AS t1
    ,   (
            SELECT  response 
            FROM    results 
            WHERE   user_id = 222
        ) AS t2 
WHERE   t1.response = t2.response

现在我正在尝试找出一种方法来为所有用户获取该信息，以产生如下结果:

| user_1  |  user_2  |  same_count  |
|---------|----------|--------------|
|  111    |   222    |    0.333     |
|  111    |   333    |    0.666     |
|  222    |   111    |    0.333     |
|  222    |   333    |    0         |
|  333    |   111    |    0.666     |
|  333    |   222    |    0         |

或者，如果可能，没有冗余信息:

| user_1  |  user_2  |  same_count  |
|---------|----------|--------------|
|  111    |   222    |    0.333     |
|  111    |   333    |    0.666     |
|  222    |   333    |    0         |

我的直觉告诉我，有一种方法可以将其作为单个巨大的 MySQL 查询来执行，而不必通过 PHP 中的循环执行一堆查询。谁能指出我正确的方向？

Answer 1

您必须使用列 *poll_id* 和 *user_id* 对同一个表执行完全外部联接。结果将显示两次，为了避免我们需要以这样的方式指定条件，即只有 alias1 表的 user_id 值小于 alias2 表的 user_id 值单独包含在结果集中。

Click here to view the demo in SQL Fiddle.

脚本:

CREATE TABLE poll
(
    user_id     INT         NOT NULL
  , poll_id     INT         NOT NULL
  , response    VARCHAR(10) NOT NULL  
);

INSERT INTO poll (user_id, poll_id, response) VALUES
   (111, 1, 'yes'),
   (111, 2, 'no'),
   (111, 3, 'no'),
   (222, 1, 'yes'),
   (222, 2, 'yes'),
   (222, 3, 'yes'),
   (333, 1, 'no'),
   (333, 2, 'no'),
   (333, 3, 'no');

SELECT      p1.user_id AS user_1
        ,   p2.user_id AS user_2, 
            AVG(CASE 
                    WHEN p1.response = p2.response THEN 1 
                    ELSE 0 
                END) Average_Response
FROM        poll p1
,           poll p2 
WHERE       p1.poll_id = p2.poll_id 
AND         p1.user_id < p2.user_id
GROUP BY    p1.user_id
        ,   p2.user_id;

输出:

USER_1 USER_2 AVERAGE_RESPONSE
------ ------ ----------------
111     222      0.3333
111     333      0.6667
222     333      0