尝试从数据库中查询信息以获得简单的排名算法

Question

我正在制作一个连接到SQLite数据库的简单图像浏览器。在浏览器中，类似的图像被分组为事件，每个图像都用几个标记。

为了反映这些信息，表格(一张表格中的所有表格)结构看起来有点像这样：

row_id      tag          image_id          event_id
1           computer      201                 1
2           desk          201                 1
3           chair         201                 1
4           computer      202                 1
5           coffee        202                 1
6           desk          202                 1
7           dog           203                 2
8           phone         203                 2
etc.        etc.         etc.                etc.       // many 1000's

基本上，这个想法是用户可以搜索任何数量的标签(例如桌子、椅子和笔记本电脑)，并收到事件id的排序列表。每个事件应该根据包含所有标签的事件中的图像数量进行排名，然后是所有标签减1，然后是全部标签减2，等等。

目标是提出一个查询，返回如下示例中的信息，然后我可以稍后对其进行排序。(很明显，行的长度会随着搜索标签的数量而变化。)

event_id | event_size | no. imgs with 3 tags |  no. imgs with 2 tags |  no. imgs with 1 tag 
2           74                 6                      24                 55 
5           20                 2                      4                  14
3           36                 4                      11                 22

这可能吗？事件的大小只是它包含的唯一图像ID的数量。剩下的，我在考虑使用。。。

SUM(CASE WHEN tag = 'computer' THEN 1 ELSE 0 END)

能实现吗？我是新手，所以不确定这个问题有多难。

Answer 1

您可以通过以下方式获得每个图像的匹配标签数量：

select event_id, image_id, count(*) as num_matches
from t
where tag in ( . . . )
group by event_id, image_id;

这会过滤掉任何没有匹配标签的图像。因此：

select event_id, image_id,
sum(case when tag in ( . . . ) then 1 else 0 end) as num_matches
from t
group by event_id, image_id;

然后你可以调整这个：

select event_id, count(*) as num_images,
sum(case when num_matches = 3 then 1 else 0 end) as num_3_tags,
sum(case when num_matches = 2 then 1 else 0 end) as num_2_tags,
sum(case when num_matches = 1 then 1 else 0 end) as num_1_tags
from (select event_id, image_id, 
sum(case when tag in ( . . . ) then 1 else 0 end) as num_matches
from t
group by event_id, image_id
) t
group by event_id;