Вы спрашиваете о том, как я могу рассчитать, какие самые близкие города? Например. Если бы я смотрел на город 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 ) 

За работой

Я предполагаю, что вы введете один из идентификаторов города или имя, чтобы найти их ближайший в моем случае «Париж» имеет идентификатор один

  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 ) 

Он будет получать все города, кроме Парижа, которые имеют те же самые теги, которые

Вот ваша скрипка

В то время как чтение о сходстве / индексе Jaccard нашло кое-что для понимания того, что на самом деле термины позволяют взять этот пример, мы имеем два набора A & B

Множество A = {A, B, C, D, E}

Множество B = {I, H, G, F, E, D}

Формула для вычисления сходства с джаккардом равна JS = (A пересекается B) / (A объединение B)

A пересечение B = {D, E} = 2

Объединение B = {A, B, C, D, E, I, H, G, F} = 9

JS = 2/9 = 0,2222222222222222

Теперь переходите к своему сценарию

В Париже есть tag_ids 1,3, поэтому мы делаем набор этого и называем наш Set P = {Europe, River}

В Лондоне есть tag_ids 1,3, поэтому мы делаем набор этого и называем наш Set L = {Europe, River}

В Нью-Йорке есть tag_ids 2,3, поэтому мы делаем набор этого и называем наш Set NW = {North America, River}

Вычисление JS Paris с London JSPL = P пересекает L / P объединение L, JSPL = 2/2 = 1

Вычисление JS Paris с New York JSPNW = P пересекается с NW / P-объединением NW, JSPNW = 1/3 = 0,33333333333

Вот запрос до сих пор, который вычисляет идеальный индекс 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 

Таким образом, результат показывает, что Париж тесно связан с Лондоном, а затем связан с Нью-Йорком

Скриншот

 select c.name, cnt.val/(select count(*) from cities) as jaccard_index from cities c inner join ( select city_id, count(*) as val from cities_tags where tag_id in (select tag_id from cities_tags where city_id=1) and not city_id in (1) group by city_id ) as cnt on c.id=cnt.city_id order by jaccard_index desc 

Этот запрос статически ссылается на city_id=1 , поэтому вам придется сделать эту переменную как в where tag_id in , так и в предложении not city_id in .

Если я правильно понял индекс Jaccard, он также возвращает это значение, упорядоченное по «наиболее тесно связанному». Результаты в нашем примере выглядят следующим образом:

 |name |jaccard_index | |London |0.6667 | |New York |0.3333 | 

редактировать

С лучшим пониманием того, как реализовать Jaccard Index:

Прочитав немного больше о википедии о Индексе Jaccard, я придумал лучший способ реализовать запрос для нашего примера набора данных. По существу, мы будем сравнивать наш выбранный город с другим городом в списке независимо друг от друга и используя подсчет общих тегов, деленный на количество отдельных тегов, выбранных между двумя городами.

 select c.name, case -- when this city's tags are a subset of the chosen city's tags when not_in.cnt is null then -- then the union count is the chosen city's tag count intersection.cnt/(select count(tag_id) from cities_tags where city_id=1) else -- otherwise the union count is the chosen city's tag count plus everything not in the chosen city's tag list intersection.cnt/(not_in.cnt+(select count(tag_id) from cities_tags where city_id=1)) end as jaccard_index -- Jaccard index is defined as the size of the intersection of a dataset, divided by the size of the union of a dataset from cities c inner join ( -- select the count of tags for each city that match our chosen city select city_id, count(*) as cnt from cities_tags where tag_id in (select tag_id from cities_tags where city_id=1) and city_id!=1 group by city_id ) as intersection on c.id=intersection.city_id left join ( -- select the count of tags for each city that are not in our chosen city's tag list select city_id, count(tag_id) as cnt from cities_tags where city_id!=1 and not tag_id in (select tag_id from cities_tags where city_id=1) group by city_id ) as not_in on c.id=not_in.city_id order by jaccard_index desc 

Запрос немного длинный, и я не знаю, насколько он будет масштабироваться, но он реализует настоящий индекс Jaccard, как того требует запрос. Вот результаты с новым запросом:

 +----------+---------------+ | name | jaccard_index | +----------+---------------+ | London | 1.0000 | | New York | 0.3333 | +----------+---------------+ 

Отредактировано снова, чтобы добавить комментарии к запросу, и принять во внимание, когда теги текущего города являются подмножеством тегов выбранного города

Этот запрос без каких-либо фантастических функций или даже подзапросов. Это быстро. Просто убедитесь, что city.id, cities_tags.id, cities_tags.city_id и cities_tags.tag_id имеют индекс.

Запросы возвращают результат, содержащий: city1 , city2 и количество меток, которые имеют города 1 и 2.

 select c1.name as city1 ,c2.name as city2 ,count(ct2.tag_id) as match_count from cities as c1 inner join cities as c2 on c1.id != c2.id -- change != into > if you dont want duplicates left join cities_tags as ct1 on -- use inner join to filter cities with no match ct1.city_id = c1.id left join cities_tags as ct2 on -- use inner join to filter cities with no match ct2.city_id = c2.id and ct1.tag_id = ct2.tag_id group by c1.id ,c2.id order by c1.id ,match_count desc ,c2.id 

Измените != > чтобы избежать возвращения каждого города дважды. Значение города больше не будет отображаться один раз в первом столбце, а также один раз во втором столбце.

Измените два left join на inner join если вы не хотите видеть комбинации городов, у которых нет совпадений с тегами.

Может ли это быть толчком в правильном направлении?

 SELECT cities.name, ( SELECT cities.id FROM cities JOIN cities_tags ON cities.id=cities_tags.city_id WHERE tags.id IN( SELECT cities_tags.tag_id FROM cites_tags WHERE cities_tags.city_id=cites.id ) GROUP BY cities.id HAVING count(*) > 0 ) as matchCount FROM cities HAVING matchCount >0 

Я пробовал это:

// Найти имена городов:
Получите city.names (SUBQUERY) как matchCount FROM города WHERE matchCount> 0

// подзапрос:
выберите количество тегов, у которых есть (SUBSUBQUERY), также

// подсубъект
выберите идентификатор тегов, имя оригинала

Слишком поздно, но я думаю, что ни один из ответов не является полностью правильным. Я получил лучшую часть каждого и собрал все вместе, чтобы ответить на свой вопрос:

• Индекс Jacark Index из @ m-khalid-junaid очень интересен и правилен, но реализация (q.sets + q.parisset) AS union и (q.sets - q.parisset) AS intersect очень неправильно .
• Версия @ n-lx – это путь, но для этого нужен индекс Jaccard. Это очень важно, если в городе есть 2 тега и сопоставляется два тега другого города с 3 тегами, результат будет таким же, как и в матчах на другом город с теми же двумя тегами. Я думаю, что полные матчи наиболее связаны.

Мой ответ:

таблицу cities .

 | id | Name | | 1 | Paris | | 2 | Florence | | 3 | New York | | 4 | São Paulo | | 5 | London | 

Таблица cities_tag следующим образом.

 | city_id | tag_id | | 1 | 1 | | 1 | 3 | | 2 | 1 | | 2 | 3 | | 3 | 1 | | 3 | 2 | | 4 | 2 | | 5 | 1 | | 5 | 2 | | 5 | 3 | 

С этими образцовыми данными Флоренс имеет полные матчи с Парижем, Нью-Йорк соответствует одному тегу , Сан-Паулу не имеет матчи тегов, а Лондон соответствует двум тегам и имеет еще один. Я думаю, что индекс Jaccard этого образца:

Флоренция: 1.000 (2/2)

Лондон: 0,666 (2/3)

Нью-Йорк: 0,333 (1/3)

Сан-Паулу: 0,000 (0/3)

Мой запрос выглядит так:

 select jaccard.city, jaccard.intersect, jaccard.union, jaccard.intersect/jaccard.union as 'jaccard index' from (select c2.name as city ,count(ct2.tag_id) as 'intersect' ,(select count(distinct ct3.tag_id) from cities_tags ct3 where ct3.city_id in(c1.id, c2.id)) as 'union' from cities as c1 inner join cities as c2 on c1.id != c2.id left join cities_tags as ct1 on ct1.city_id = c1.id left join cities_tags as ct2 on ct2.city_id = c2.id and ct1.tag_id = ct2.tag_id where c1.id = 1 group by c1.id, c2.id) as jaccard order by jaccard.intersect/jaccard.union desc 

SQL Fidde