php - 比较标签组以查找与 PHP/MySQL 的相似性/分数

Question

如何将一组标签与数据库中另一篇文章的标签进行比较以获得相关文章？

我想做的是将帖子上的一组标签与另一篇帖子的标签进行比较，但不是单独比较每个标签。假设您想根据帖子中的标签获取真正相关的项目，然后从最相关到​​最不相关的顺序显示它们。无论关系级别如何，每次都必须显示三个相关项目。

Post A has the tags: "architecture", "wood", "modern", "switzerland"
Post B has the tags: "architecture", "wood", "modern"
Post C has the tags: "architecture", "modern", "stone"
Post D has the tags: "architecture", "house", "residence"

Post B is related to post A by 75% (3 related tags)
Post C is related to post A by 50% (2 related tags)
Post D is related to post A by 25% (1 related tag)

我该怎么做？我目前正在使用 3-tables。

posts
> id
> image
> date

post_tags
> post_id
> tag_id

tags
> id
> name

我已经搜索了 Internet 和 Stack Overflow 以了解如何执行此操作。我最近的发现是 How to find "related items" in PHP ，但它实际上并没有为我解决太多问题。

Answer 1

注意:此解决方案仅适用于 MySQL，因为 MySQL 对 GROUP BY 有自己的解释

我还使用了自己的相似度计算。我用相同标签的数量除以帖子 A 和帖子 B 中的平均标签数。因此，如果帖子 A 有 4 个标签，而帖子 B 有 2 个标签，它们都与 A 共享，则相似度为 66% .

(SHARED:2/((A:4 + B:2)/2) 或 (SHARED:2)/(AVG:3)

如果您想要/需要...，更改公式应该很容易...

SELECT
 sourcePost.id,
 targetPost.id,

 /* COUNT NUMBER OF IDENTICAL TAGS */
 /* REF GROUPING OF sourcePost.id and targetPost.id BELOW */
 COUNT(targetPost.id) /
 (
  (
   /* TOTAL TAGS IN SOURCE POST */
   (SELECT COUNT(*) FROM post_tags WHERE post_id = sourcePost.id)

   +

   /* TOTAL TAGS IN TARGET POST */
   (SELECT COUNT(*) FROM post_tags WHERE post_id = targetPost.id)

  ) / 2  /* AVERAGE TAGS IN SOURCE + TARGET */
 ) as similarity
FROM
 posts sourcePost
LEFT JOIN
 post_tags sourcePostTags ON (sourcePost.id = sourcePostTags.post_id)
INNER JOIN
 post_tags targetPostTags ON (sourcePostTags.tag_id = targetPostTags.tag_id
                             AND 
                              sourcePostTags.post_id != targetPostTags.post_id)
LEFT JOIN
 posts targetPost ON (targetPostTags.post_id = targetPost.id)
GROUP BY
 sourcePost.id, targetPost.id