SQL左连接会产生重复结果

Question

我有一个模式，其中包含图像和这些图像的结果。结果存储在不同模式的 N 个表中。我需要编写一个搜索查询，可以返回所有符合某些条件（包括限制和偏移量）的图像及其结果。

一张图片可能有10个结果（2个分类，8个检测）。我希望限制作用于图像而不是结果。因此，我期望得到10行，代表1张图像。

以下是我的查询。问题在于结果行的重复和组合。我期望每个结果都有一行，而不是合并检测和分类。我需要使用 UNION ALL 或其他方法吗？

CREATE TABLE images (
  id         VARCHAR(40)     NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE image_results_classification (
  image_id    VARCHAR(40)          NOT NULL,
  c_confidence  REAL                 NOT NULL,
  FOREIGN KEY (image_id)  REFERENCES images(id)
);

CREATE TABLE image_results_detection (
  image_id    VARCHAR(40)          NOT NULL,
  d_confidence  REAL                 NOT NULL,
  FOREIGN KEY (image_id)  REFERENCES images(id)
);

INSERT INTO images (id) VALUES ('123');
INSERT INTO images (id) VALUES ('456');

INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('123', 0.9);
INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('123', 0.8);
INSERT INTO image_results_classification (image_id, c_confidence) VALUES ('456', 0.7);

INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('123', 0.1);
INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('123', 0.2);
INSERT INTO image_results_detection (image_id, d_confidence) VALUES ('456', 0.3);

这个模式是为了简化问题而创造的：两个结果表上有更多行，并且它们也不同（不仅仅是置信度）。

我想在我的应用层最终得到的类型是：
Map[图像，（List [分类结果]，List [检测结果]）]

也就是说，需要图像和所有结果。 结果集中带有null也可以。 可能像这样：

id   c_confidence d_confidence
123  0.9          NULL
123  0.8          NULL
123  NULL         0.1
123  NULL         0.2
456  0.7          NULL
456  NULL         0.3

这是来自DB Fiddle的查询：

SELECT *
FROM images INNER JOIN
     (SELECT id FROM images LIMIT 10 OFFSET 0
     ) AS i
     ON (images.id = i.id) OUTER LEFT JOIN 
     image_results_classification c
     ON (images.id = c.image_id) OUTER LEFT JOIN 
     image_results_detection d
     ON (images.id = d.image_id);

https://www.db-fiddle.com/f/tuDxwY7kQGfEvZSzaajESG/0

编辑：我有一个附加要求，需要对结果进行过滤，并能够限制和偏移图像。

我想执行以下查询：

给我所有的图片和它们的所有结果，其中c_confidence > 0.5。即，如果一张图片的c_confidence为0.4，则应包括该图片（但不包括它的任何结果）。如果它的c_confidence为0.6，则返回所有结果（包括image_results_detection）。

我已更新我的fiddle以反映这一点：https://www.db-fiddle.com/f/tuDxwY7kQGfEvZSzaajESG/1

在fiddle中，我希望没有结果返回，因为该图像没有具有置信度>0.8的image_results_classification。

- Dominic Bou-Samra

---

感谢您的编辑。我已经有一段时间没有使用过 Stack Overflow 了。 - Dominic Bou-Samra

你能否编辑问题并提供期望的结果？ - Pranay Rana

@PranayRana 是的，已经完成了。 - Dominic Bou-Samra

现在你期望的结果看起来像一个 UNION。如果对于给定的 id，分类和检测都没有行，那么你会期望什么呢？ - dnoeth

@LukStorms的查询可能是在您想要限制结果时最好的选择。 - dnoeth

Answer 1

你可以使用GROUP BY与GROUP_CONCAT一起使用。
第一个group_concat可以在具有LIMIT的子查询中完成。
为避免那两个一对多关系之间的笛卡尔积效应。

例如：

SELECT 
 q.*,  
 group_concat(d.d_confidence) as d_confidence_list
FROM
(
    SELECT i.id, group_concat(c.c_confidence) as c_confidence_list
    FROM images i
    LEFT JOIN image_results_classification c ON (c.image_id = i.id)
    GROUP BY i.id
    LIMIT 10
) q
LEFT JOIN image_results_detection d ON (d.image_id = q.id)
GROUP BY q.id, q.c_confidence_list

你也可以使用按值唯一的DISTINCT，而不需要子查询。

SELECT 
 i.id, 
 group_concat(distinct c.c_confidence) as c_confidence_list,
 group_concat(distinct d.d_confidence) as d_confidence_list
FROM images i
LEFT JOIN image_results_classification c ON (c.image_id = i.id)
LEFT JOIN image_results_detection d ON (d.image_id = i.id)
GROUP BY i.id
LIMIT 10

如果联合表中有很多置信度，第一种方法可能会更快。

附加信息

这里有两个要尝试的查询。

第一个应该可以得到预期的结果。使用CTE，限制只需执行一次。

with TOPIMG as (
  select * from images LIMIT 10
)
select image_id, c_confidence, null as d_confidence
from TOPIMG i
join image_results_classification c on c.image_id = i.id
union all
select image_id, null as c_confidence, d_confidence
from TOPIMG i
join image_results_detection d on d.image_id = i.id
order by image_id;

这个查询使用一个技巧，以绕远路的方式模拟带有PARTITION的ROW_NUMBER函数。（我不喜欢它，它会降低性能）

with TOPIMG as (
  select * from images LIMIT 10
)
select 
image_id, 
max(case when src = 'c' then conf end) as c_conf,
max(case when src = 'd' then conf end) as d_conf
from 
(
  select image_id, 'c' as src, c_confidence as conf,
  (
    select count(*) 
    from image_results_classification c2 
    where c.image_id = c2.image_id and c.c_confidence >= c2.c_confidence
  ) as RN
  from TOPIMG i
  join image_results_classification c on (c.image_id = i.id)

  union all

  select image_id, 'd', d_confidence,
  (
    select count(*) 
    from image_results_detection d2 
    where d.image_id = d2.image_id and d.d_confidence >= d2.d_confidence
  ) as RN
  from TOPIMG i
  join image_results_detection d on (d.image_id = i.id)
) cd
group by image_id, RN
order by image_id, RN;

更新

实现特殊要求c_confidence > 0.5:

（注：此处需要更多上下文信息才能进行更准确的翻译）

with IMG as (
  select i.id as image_id, 
  max(case when c.image_id is not null then 1 else 0 end) as show_all
  from images i
  left join image_results_classification c on (c.image_id = i.id and c.c_confidence > 0.5)
  group by i.id
  order by i.id
  LIMIT 100
)
select c.image_id, 'c' as result_type, c.c_confidence as confidence
from IMG i
join image_results_classification c on c.image_id = i.image_id
where i.show_all = 1

union all

select d.image_id, 'd' as result_type, d.d_confidence as confidence
from IMG i
join image_results_detection d on d.image_id = i.image_id
where i.show_all = 1

union all

select i.image_id, null, null
from IMG i
where i.show_all = 0

order by image_id;

- LukStorms

---

这个可行。我不知道group_concat。我现在会保留这个问题，但这可能可行。我看到的问题是，除了结果表上的置信度之外，实际上还有更多的行。我认为我更喜欢一个扁平的结构。 - Dominic Bou-Samra

@DominicBou-Samra 好的，我添加了一个替代方案，似乎可以得到相同的结果。 - LukStorms

@DominicBou-Samra玩得很开心，尝试了SqlLite的限制。又添加了2个查询。 - LukStorms

顺便说一下，通常使用 LIMIT 时会添加 ORDER BY。对于仅有的样本记录来说这并不重要，但是如果数据更多，通常你会想返回某些东西的 TOP x。如果没有 ORDER BY，则只会得到随机的 TOP x 记录。我觉得我应该提一下这个。 - LukStorms

是的，绝对没问题。在我的实际代码中，我有一个 order by。今天我正在集成这个查询，如果有任何困难，我会告诉你的。谢谢，我非常感激。 - Dominic Bou-Samra

嗯，这个查询对我来说不太适用。我从问题中省略了一些细节。我需要能够对结果执行条件语句（即返回所有c_confidence>0.9的图像），并接收符合该条件的所有图像的所有结果。我已经更新了我的问题和db-fiddle。 - Dominic Bou-Samra

Answer 2

您正在将每个分类与每个检测相结合。但是这两者并不真正相关，因此请不要这样做。一种解决方案是分别选择分类和检测，并将它们进行union all联合。

select *
from
(
  select 'Classification' as what, image_id, c_confidence as value
  from image_results_classification
  union all
  select 'Detection' as what, image_id, d_confidence as value
  from image_results_detection
) results
where image_id in
(
  select id
  from images
  -- order by something to decide which images to pick?
  limit 10
);

输出:

+ ---------------+----------+-------+
| 类别            | 图像ID   | 值    |
+ ---------------+----------+-------+
| 分类            | 123      | 0.8   |
| 分类            | 123      | 0.9   |
| 检测            | 123      | 0.1   |
| 检测            | 123      | 0.2   |
| 分类            | 456      | 0.7   |
| 检测            | 456      | 0.3   |
+ ---------------+----------+-------+

DB-fiddle演示: https://www.db-fiddle.com/f/fZPMNL7NC8GzwkwHc4strG/0

- Thorsten Kettner

---

非常好，谢谢。我明天早上会尝试这个，谢谢。 - Dominic Bou-Samra