mysql - 比较数据集并返回最佳匹配

Question

在 mysql 中，我使用“连接表”为项目分配标签。我想看看哪些项目与正在查看的项目具有最相似的标签。

例如，假设感兴趣的项目已被标记为“酷”、“汽车”和“红色”。我想搜索带有这些标签的其他项目。我想查看已标记为“汽车”的项目，但我希望标记为“汽车”和“红色”的项目位于仅标记为“汽车”的项目上方。我希望具有相同标签的项目位于结果的顶部。

是否有某种方法可以使用 IN 将一个数据集(子查询)与另一个数据集(子查询)进行比较？或者，是否有一些技巧可以使用 GROUP BY 和 GROUP_CONCAT() 将它们评估为逗号分隔列表？

Answer 1

如果您向我们展示您的表结构会有所帮助，这样我就可以更具体一些。

我假设你有一个类似这样的结构:

Table item: (id, itemname)
1 item1
2 item2
3 item3
4 item4
5 item5

Table tag: (id, tagname)
1 cool
2 red
3 car

Table itemtag: (id, itemid, tagid)
1 1 2 (=item1, red)
2 2 1 (=item2, cool)
3 2 3 (=item2, car)
4 3 1 (=item3, cool)
5 3 2 (=item3, red)
6 3 3 (=item3, car)
7 4 3 (=item3, car)
8 5 3 (=item3, car)

一般来说，我的方法是从计算每个单独的标签开始。

-- make a list of how often a tag was used:
select tagid, count(*) as `tagscore` from itemtag group by tagid

这会为分配给该项目的每个标签显示一行，并带有一个分数。

在我们的示例中，这将是:

tag  tagscore
1    2         (cool, 2x)
2    2         (red, 2x)
3    4         (car, 4x)


set @ItemOfInterest=2;

select
  itemname,
  sum(tagscore) as `totaltagscore`,
  GROUP_CONCAT(tags) as `tags`
from
  itemtag
join item on itemtag.itemid=item.id

join
  /* join the query from above (scores per tag) */
  (select tagid, count(*) as `tagscore` from itemtag group by tagid ) as `TagScores`
  on `TagScores`.tagid=itemtag.tagid
where
  itemid<>@ItemOfInterest and 
  /* get the taglist of the current item */
  tagid in (select distinct tagid from itemtag where itemid=@ItemOfInterest)
group by
  itemid
order by
  2 desc

解释:该查询有 2 个子查询:一种是从感兴趣的项目中获取列表标签。我们只想与那些人合作。另一个子查询生成每个标签的分数列表。

所以最后，数据库中的每个项目都有一个标签分数列表。这些分数与 sum(tagscore) 相加，该数字用于对结果进行排序(最高分在最上面)。

为了显示可用标签列表，我使用了 GROUP_CONCAT。

查询将产生类似这样的结果(我已经在此处制作了实际数据):

Item   TagsScore   Tags
item3  15          red,cool,car
item4   7          red,car
item5   7          red
item1   5          car
item6   5          car