mysql - 如何根据标签获取相似对象

Question

我有如下三个表:

• 电影:id、名称

• 标签:id、名称、值

• tagged: id, movie(FK), tag(FK)

因此，每部电影都有自己的一组标签。我需要的是根据标签集检索相似的电影。我想按匹配标签的数量对 10 部电影进行排序。

如果我创建如下所示的 View ，它会使 MySQL 消失。 'tag' 和 'tagged' 表中都有 30k+ 条记录。

create view relatedtags as 

select
    entityLeft.id as id,
    entityRight.id as rightId,
    count(rightTagged.id) as matches

from
    entity as entityLeft join tagged as leftTagged on leftTagged.entity = entityLeft.id, 
    entity as entityRight join tagged as rightTagged on rightTagged.entity = entityRight.id

where leftTagged.tag = rightTagged.tag
and entityLeft.id != entityRight.id
group by entityLeft.id, entityRight.id

Answer 1

这将返回与给定 <current_movie_id> 共享至少 1 个标签的所有电影的列表按共同标签数量递减排序

SELECT movie.*, count(DISTINCT similar.tag) as shared_tags FROM movie INNER JOIN 
    ( tagged AS this_movie INNER JOIN tagged AS similar USING (tag) )
    ON similar.movie = movie.id
WHERE this_movie.movie=<current_movie_id>
AND   movie.id != this_movie.movie
GROUP BY movie.id
ORDER BY shared_tags DESC

希望能给你一些有用的东西