mysql - 多重连接中的评分算法

Question

我有一个存储在出版物表中的出版物列表。每个出版物与类别都有多对多关系，也与关键字有多对多关系。

给定一个出版物，我想根据使用以下算法计算的分值查找相关出版物:

• 与其他出版物共享的每个类别均计为一分
• 每个与其他出版物共享的关键字都算一分
• 得分值是前面步骤计算出的分数的总和

我想通过单个查询检索按此分数排序的相关出版物列表。

现在我有这两个查询来计算类别和关键字的分数

SELECT c.publication_id, (COUNT(c.category_id)) AS cscore
FROM cat_pub c
WHERE c.category_id IN <list of category ids obtained from the current publication>
GROUP BY c.publication_id
ORDER BY cscore DESC

以及关键字得分

SELECT k.publication_id, (COUNT(k.keyword_id)) AS kscore
FROM key_pub k
WHERE k.keyword IN <list of category ids obtained from the current publication>
GROUP BY k.publication_id
ORDER BY kscore DESC

最后，我需要使用 SELECT 查询JOIN 生成的查询，该查询应检索出版物数据(标题、简介等)，并按分数和限制对它们进行排序子句来获取与所选出版物最相关的出版物。

目前我尝试使用这两个查询作为联接中的子表:

SELECT mydata.*, (q1.cscore + q2.kscore) AS score
FROM publications p
INNER JOIN (<cscore query>) q1 ON p.id = q1.publication_id
INNER JOIN (<kscore query>) q2 ON p.id = q2.publication_id
ORDER BY score DESC
LIMIT 5

EXPLAIN 向我展示了将使用几个临时表。会不会是性能问题？有没有更好的方法来实现这个？

更新

回复Johan's comment

你的解决方案是错误的。在子查询中使用 LIMIT 子句可能会导致每个限制值的结果不一致。如果子查询有以下结果(我将显示 11 条记录，但您的查询将仅获取前 10 条记录)，该怎么办？

+-------+--------+ +-------+--------+
| p.id  | cscore | | p.id  | kscore |
+-------+--------+ +-------+--------+
| 27854 | 100    | | 27865 | 100    |
| 27853 | 100    | | 27864 | 100    |
| 27852 | 100    | | 27863 | 100    |
| 27851 | 100    | | 27862 | 100    |
| 27850 | 100    | | 27861 | 100    |
| 27849 | 100    | | 27860 | 100    |
| 27848 | 100    | | 27859 | 100    |
| 27847 | 100    | | 27858 | 100    |
| 27846 | 100    | | 27857 | 100    |
| 27845 | 100    | | 27856 | 100    |
| 27844 | 100    | | 27855 | 100    |
| 1000  | 99     | | 1000  | 99     |
+-------+--------+ +-------+--------+

如果我有 10 个记录，其中 cscore 为 100，并且有 10 个不同记录，kscore 为 100，则连接将产生一个空集。所以我没有得到任何结果，而 id 为 1000 的出版物应该是解决方案，并且它已从结果集中排除。

此外，我可以考虑使用 LEFT JOIN 的解决方案，在这种情况下，只会提取左表中的记录，并且每条记录的总分将为 100(因为空 kscore 给出的 NULL第二个表中的 字段)。同样，结果是错误的，因为最高分记录应该是 p1000，总分为 198 (= 99 + 99)

您的解决方案无法产生可靠的结果。

Answer 1

您只需要子查询中的每个结果 5 个结果。
我认为最好只选择 5 个并在查询中使用它。

将 q1 重写为:

SELECT c.publication_id, COUNT(*) AS cscore
FROM cat_pub c
WHERE c.publication_id = p.id  
AND c.category_id IN <list of category ids obtained from the current publication>
GROUP BY c.publication_id
ORDER BY cscore DESC
LIMIT 10

将 q2 重写为:

SELECT k.publication_id, COUNT(*) AS kscore
FROM key_pub k
WHERE p.id = k.publication_id
  AND k.keyword IN <list of category ids obtained from the current publication>
GROUP BY k.publication_id
ORDER BY kscore DESC
LIMIT 10

保持连接不变:

SELECT p.*, (q1.cscore + q2.kscore) AS score
FROM publications p
INNER JOIN (<cscore query>) q1 ON p.id = q1.publication_id
INNER JOIN (<kscore query>) q2 ON p.id = q2.publication_id
ORDER BY score DESC
LIMIT 5

请注意，count(*) 通常是更快的选择，因为它不会测试 null 如果您可以有 null 值并且不想包含这些值在计数中，然后明确命名计数(字段)。