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.

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

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

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

С лучшим пониманием того, как реализовать индекс Джаккарда:

Прочитав немного больше в википедии о 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 |
+----------+---------------+

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

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

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

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, если вы не хотите видеть комбинации городов, у которых нет совпадений с тегами.

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

• В Индекс Джаккарда объяснение @m-халида-джунаида очень интересно и правильно, но реализация (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      |

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

Флоренция: 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-Фидд

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

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 (ПОДЗАПРОС) как количество совпадений ИЗ городов, ГДЕ количество совпадений >0

// подзапрос:
выберите количество тегов, которые есть у городов, в которых (ВЛОЖЕННЫЙ запрос) также есть

// вложенный запрос
выберите идентификатор тегов, которые имеет исходное имя