php 分类标签推荐,MySQL / PHP：通过标签/分类法查找类似/相关的项目

你问我如何计算哪个是最密切相关的城市？例如.如果我正在看城市1(巴黎),结果应该是：伦敦(2),纽约(3),根据你提供的数据集,只有一件事情是城市之间的常见标签,所以共享共同标签的城市将是下面最接近的城市是查找共享公共标签的城市(除了提供以找到最近的城市)的子查询

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

它会找到所有的paris所有的标签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 )

它将获取除巴黎之外的所有城市,巴黎也有与之相同的标签

在阅读Jaccard相似性/索引时,发现一些东西要了解这些术语的实际情况,我们有两套A&乙

Set A={A, B, C, D, E}

Set B={I, H, G, F, E, D}

Formula to calculate the jaccard similarity is JS=(A intersect B)/(A

union B)

A intersect B = {D,E}= 2

A union B ={A, B, C, D, E,I, H, G, F} =9

JS=2/9 =0.2222222222222222

现在转向你的场景

Paris has the tag_ids 1,3 so we make the set of this and call our Set

P ={Europe,River}

London has the tag_ids 1,3 so we make the set of this and call our

Set L ={Europe,River}

New York has the tag_ids 2,3 so we make the set of this and call our

Set NW ={North America,River}

Calculting the JS Paris with London JSPL = P intersect L / P union L ,

JSPL = 2/2 = 1

Calculting the JS Paris with New York JSPNW = P intersect NW / P

union NW ,JSPNW = 1/3 = 0.3333333333

这是迄今为止查询完美的jaccard索引的查询,您可以看到下面的小提琴示例

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

所以结果显示巴黎与伦敦密切相关,然后与纽约有关