我有一个看起来像这样的城市表。
|id| Name |
|1 | Paris |
|2 | London |
|3 | New York|
我有一个看起来像这样的标签表。
|id| tag |
|1 | Europe |
|2 | North America |
|3 | River |
和一个city_tags表:
|id| city_id | tag_id |
|1 | 1 | 1 |
|2 | 1 | 3 |
|3 | 2 | 1 |
|4 | 2 | 3 |
|5 | 3 | 2 |
|6 | 3 | 3 |
如何计算最相关的城市？例如。如果我查看的是城市1(巴黎)，则结果应为:伦敦(2)，纽约(3)
我已经找到了Jaccard index，但是不确定如何最好地实现这一点。
最佳答案
您对的问题提出疑问。如何计算最密切相关的城市？例如。如果我查看的是城市1(巴黎)，则结果应为:伦敦(2)，纽约(3)，根据您提供的数据集，只有一件事相关，那就是城市之间的通用标签，因此共享公共(public)标签的城市将是最接近的城市，下面是子查询，该子查询查找共享公共(public)标签的城市(不提供该城市来查找其最近的城市)
SELECT * FROM `cities` WHERE id IN (
SELECT city_id FROM `cities_tags` WHERE tag_id IN (
SELECT tag_id FROM `cities_tags` WHERE city_id=1) AND city_id !=1 )
在职的
我假设您将输入城市ID或名称之一，以找到与他们最接近的城市ID或名称。
SELECT tag_id FROM `cities_tags` WHERE city_id=1
它将找到巴黎拥有的所有标签id
SELECT city_id FROM `cities_tags` WHERE tag_id IN (
SELECT tag_id FROM `cities_tags` WHERE city_id=1) AND city_id !=1 )
它将获取除巴黎以外所有具有与巴黎相同标签的城市
这是你的Fiddle
在阅读有关的Jaccard相似度/索引时，发现了一些有关术语实际含义的信息，让我们以这个例子为例，我们有两组A和B
现在转向您的方案
到目前为止，这是计算完美的jaccard索引的查询，您可以看到下面的 fiddle 示例
SELECT a.*,
( (CASE WHEN a.`intersect` =0 THEN a.`union` ELSE a.`intersect` END ) /a.`union`) AS jaccard_index
FROM (
SELECT q.* ,(q.sets + q.parisset) AS `union` ,
(q.sets - q.parisset) AS `intersect`
FROM (
SELECT cities.`id`, cities.`name` , GROUP_CONCAT(tag_id SEPARATOR ',') sets ,
(SELECT GROUP_CONCAT(tag_id SEPARATOR ',') FROM `cities_tags` WHERE city_id= 1)AS parisset
FROM `cities_tags`
LEFT JOIN `cities` ON (cities_tags.`city_id` = cities.`id`)
GROUP BY city_id ) q
) a ORDER BY jaccard_index DESC
在上面的查询中，我已将结果集导出为两个子选择，以便获得我自定义的计算别名
您可以在上面的查询中添加过滤条件，以不计算与自身的相似度
SELECT a.*,
( (CASE WHEN a.`intersect` =0 THEN a.`union` ELSE a.`intersect` END ) /a.`union`) AS jaccard_index
FROM (
SELECT q.* ,(q.sets + q.parisset) AS `union` ,
(q.sets - q.parisset) AS `intersect`
FROM (
SELECT cities.`id`, cities.`name` , GROUP_CONCAT(tag_id SEPARATOR ',') sets ,
(SELECT GROUP_CONCAT(tag_id SEPARATOR ',') FROM `cities_tags` WHERE city_id= 1)AS parisset
FROM `cities_tags`
LEFT JOIN `cities` ON (cities_tags.`city_id` = cities.`id`) WHERE cities.`id` !=1
GROUP BY city_id ) q
) a ORDER BY jaccard_index DESC
结果表明，巴黎与伦敦紧密相关，然后与纽约紧密相关
Jaccard Similarity Fiddle