sql查询，以通过标签确定最相似的商品 [英] sql query to determine the most similar goods by tags

问题描述

我正在做一个电子商店，所以我有3张桌子:

i'm making an e-store, so i have 3 tables:

1)goods

id      | title
--------+----------- 
1       | Toy car
2       | Toy pony
3       | Doll

2)tags

id      | title
--------+----------- 
1       | Toy
2       | Boys
3       | Girls

3)links

goods_id| tag_id
--------+----------- 
1       | 1
1       | 2
2       | 1
2       | 2
2       | 3
3       | 3

所以我需要使用这种算法来打印相关商品:使用标签获取与所选商品最相似的商品.最多的标签是相互的-最合适的项目是

so i need to print related goods using such an algorithm: get the goods which are most similar to selected item using tags. the most tags are mutual - the most suitable the item is

，因此goods#1的结果应为:goods#2，goods#3

so the result for the goods#1 should be: goods#2,goods#3

对于goods#2:goods#1，goods#3

对于goods#3:goods#2，goods#1

而且我不知道如何通过一个查询就可以按照共同标签的数量对类似商品进行排序

and i have no idea how can i get the similar goods sorted by count of mutual tags with one query

推荐答案

此查询将返回所有具有最大共同标签数的项目:

This query will return all items that have the maximum number of tags in common:

SET @item = 1;

SELECT
  goods_id
FROM
  links
WHERE
  tag_id IN (SELECT tag_id FROM links WHERE goods_id=@item)
  AND goods_id!=@item
GROUP BY
  goods_id
HAVING
  COUNT(*) = (
    SELECT
      COUNT(*)
    FROM
      links
    WHERE
      tag_id IN (SELECT tag_id FROM links WHERE goods_id=@item)
      AND goods_id!=@item
    GROUP BY
      goods_id
    ORDER BY
      COUNT(*) DESC
    LIMIT 1
  )

请在此处看到小提琴.

或者这将返回所有项目，即使那些没有共同标签的项目也按共同desc的标签数量排序:

Or this one will return all items, even those with no tags in common, ordered by the number of tags in common desc:

SELECT
  goods_id
FROM
  links
WHERE
  goods_id!=@item
GROUP BY
  goods_id
ORDER BY
  COUNT(CASE WHEN tag_id IN (SELECT tag_id FROM links WHERE goods_id=@item) THEN 1 END) DESC;

这篇关于sql查询，以通过标签确定最相似的商品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！