MYSQL - 在单个表中计算差异

Question

MYSQL - 在单个表中计算差异

4

我希望找到一种方法来计算单个表中整数之间的差异。

我正在计划一个类似下面这样的MYSQL表：

user    question    answer

1       1           3
1       2           3
1       3           2
1       4           5
1       5           1

2       1           2
2       2           3
2       3           1
2       4           5
2       5           3

3       1           3
3       2           3
3       3           4
3       4           5
3       5           3

4       1           5
4       2           3
4       3           2
4       4           5
4       5           1

在这个例子中，每个用户都回答了5个问题，并给出了1到5的评分。我要做的是找出用户2、3和4中哪些人给出的答案与用户1最相似。我的想法是计算每个用户对于每个问题所给出的答案与用户1的答案之间的差异，然后将这些差异相加。在这个加法运算之后，差异最小的用户将是最接近用户1的用户。很抱歉我不太清楚如何高效地构建一个查询来完成这个任务，希望有人能指点我正确的方向。如果有任何更好或更合理的方法来构建相同的结果，我也非常愿意听取建议。

- BarfordSimon

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- vearutop · Answer 1

SELECT SUM(ABS(t2.answer - t1.answer)) AS total_diff, t2.user 
FROM my_table AS t1 
LEFT JOIN my_table AS t2 USING(question)
WHERE t1.user = 1 AND t2.user != t1.user
GROUP BY t2.user
ORDER BY total_diff ASC

结果：

total_diff  user 
2       4
4       2
4       3

- fancyPants · Answer 2

SELECT 
yt1.user,
SUM(CASE WHEN yt1.answer = yt2.answer THEN 1 ELSE 0 END) AS howMuchAnswersInCommon
FROM yourTable yt1
INNER JOIN yourTable yt2 ON yt1.question = yt2.question 
WHERE yt2.user = 1 AND yt1.user != 1
GROUP BY yt1.user
ORDER BY howMuchAnswersInCommon DESC 
;

这将为您提供用户1最常见答案的排名靠前的答案。

测试数据：

/*
create table yourTable (user int, question int, answer int);
insert into yourTable values 
(1,       1,           3),
(1,       2,           3),
(1,       3,           2),
(1,       4,           5),
(1,       5,           1),

(2,       1,           2),
(2,       2,           3),
(2,       3,           1),
(2,       4,           5),
(2,       5,           3),

(3,       1,           3),
(3,       2,           3),
(3,       3,           4),
(3,       4,           5),
(3,       5,           3),

(4,       1,           5),
(4,       2,           3),
(4,       3,           2),
(4,       4,           5),
(4,       5,           1);
*/

输出：

user    howMuchAnswersInCommon
4           4
3           3
2           2