通过相关性排序按多个标签搜索的 SQL 查询

Question

我有一组城市，它们与一组标签具有多对多关系。用户给了我一组标签(可能包含重复项!)，我需要返回一个匹配条目列表，按相关性排序。

数据

下面是一些示例数据来说明问题:

城市:

--------------------
| id |    city     |
--------------------
|  1 |  Atlanta    |
|  2 |  Baltimore  |
|  3 |  Cleveland  |
|  4 |  Denver     |
|  5 |  Eugene     |
--------------------

标签:

------
| id |
------
|  1 |
|  2 |
|  3 |
|  4 |
------

城市标记如下:

Atlanta:   1, 2
Baltimore: 3
Cleveland: 1, 3, 4
Denver:    2, 3
Eugene:    1, 4

...因此 CityTags 表如下所示:

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

例子1

如果用户给我标签 ID:[1, 3, 3, 4]，我想计算每个标签有多少匹配项，并返回按相关性排序的结果，例如:

------------------------
|    city    | matches |
------------------------
|  Cleveland |    4    |
|  Baltimore |    2    |
|  Eugene    |    2    |
|  Atlanta   |    1    |
|  Denver    |    1    |
------------------------

由于 Cleveland 匹配了所有四个标签，所以它排在第一位，然后是 Baltimore 和 Eugene，每个都有两个标签匹配，依此类推。

例子2

还有一个例子可以作为很好的衡量标准。对于搜索 [2, 2, 2, 3, 4]，我们会得到:

------------------------
|    city    | matches |
------------------------
|  Denver    |    4    |
|  Atlanta   |    3    |
|  Cleveland |    2    |
|  Baltimore |    1    |
|  Eugene    |    1    |
------------------------

SQL

如果我忽略重复的标签，那就很简单了:

SELECT name,COUNT(name) AS relevance FROM
  (SELECT name FROM cities,citytags 
    WHERE id=city_id AND tag_id IN (1,3,3,4)) AS matches
  GROUP BY name ORDER BY relevance DESC;

但这不是我需要的。我需要尊重重复项。有人可以建议我如何完成这项工作吗？

Postgresql 中的解决方案

啊哈!我需要一个临时表。 Postgresql 允许我使用它的 WITH 语法来做到这一点。这是解决方案:

WITH search(tag) AS (VALUES (1), (3), (3), (4))
SELECT name, COUNT(name) AS relevance FROM cities
INNER JOIN citytags ON cities.id=citytags.city_id
INNER JOIN search ON citytags.tag_id=search.tag
GROUP BY name ORDER BY relevance DESC;

非常感谢那些回答的人。

Answer 1

如果用户列表以逗号分隔的列表形式出现，您可以尝试将其转换为临时表并加入其中。我不知道 PosteGRE 的相关语法，所以这是 MySql 中的想法:

create temporary table usertags (tag_id int);
insert usertags values (1),(3),(3),(4);

SELECT name, COUNT(name) AS relevance
FROM cities
JOIN citytags on cities.id = citytags.city_id
JOIN usertags on citytags.tag_id = usertags.tag_id
GROUP BY name ORDER BY relevance DESC;

将逗号分隔的列表转换为上面的代码就像将所有 , 替换为 ),( 使用您的服务器端语言一样简单，并且然后将其嵌入到 VALUES 语句中以填充临时表。

演示(MySql):http://www.sqlize.com/1qNThhD9tC