仅从主表中选择符合条件的子表行

Question

在我的图像分类软件中，有表格result和image。一个结果可以包含多个图像。每幅图像都可以被归类为阳性，在列image.preclassification中使用值'P‘或负值 'N’。

因此，更多的图像是正面的是积极的。

我只想选择积极的结果。

在阅读了几个小时的PostgreS文档之后，我找到了这样的解决方案，这让我感到害怕：

WITH tmp AS (
SELECT result.result_id AS res, image.result_id , Count( image.preclassification ) AS ImgAll,  
SUM(
CASE image.preclassification 
  WHEN 'P' THEN 1
  ELSE 0
END
) AS ImgPos

from result, image
WHERE result.result_id = image.result_id
GROUP BY result.result_id, image.result_id
)
SELECT result_id
FROM tmp
WHERE ImgPos > ImgAll/2

我的问题是，对于这类问题(ihmo非常常见)是否有更容易的解决办法？

编辑：解释

首先，我创建了一个包含正图像计数和结果所有图像计数的列的临时表。在接下来的步骤中，我只选择行，正图像的计数比所有图像的一半还要多。我的第一个想法是在第一个WHERE语句中使用WHERE，而不是使用WITH-clause。但是它不像ImgPos那样工作，ImgAll被报告为未知列。

Answer 1

巧妙的查询。但我认为你可以简化它

select r.result_id
from result r join
     image i
     on r.result_id = i.result_id
group by r.result_id
having sum(case when i.preclassification = 'P' then 1 else 0 end) >
       sum(case when i.preclassification = 'N' then 1 else 0 end);

您还可以将其写为：

select r.*
from (select r.result_id,
             sum(case when i.preclassification = 'P' then 1 else 0 end) as NumPos,
             sum(case when i.preclassification = 'N' then 1 else 0 end) as NumNeg
      from result r join
           image i
           on r.result_id = i.result_id
      group by r.result_id
     ) r
where NumPos > NumNeg;

Answer 2

我可能会执行以下两个查询：

采取1：

select *
from result r
join ( select t.result_id
       from result t
       join image  i  on i.result_id = t.result_id
       group by t.result_id
       having sum(case i.preclassification when 'P' then 1 else 0 end ) >
              sum(case i.preclassification when 'N' then 1 else 0 end )
     ) s on s.result_id = r.result_id

采取2：

select r.*, p.frequence as positives , n.frequency as negatives
from result r
join      ( select t.result_id , count(*) as frequency
            from result t
            join image  i on i.result_id = r.result_id
                         and i.preclassification = 'P'
          ) p on r.result_id = p.result_id
left join ( select t.result_id , count(*) as frequency
            from result t
            join image  i on i.result_id = r.result_id
                         and i.preclassification = 'N'
          ) n on n.result_id = r.result_id
where p.frequency > coalesce( n.frequency, 0 )

在正数派生表上的内部连接是因为必须至少有一个正数才能使结果为正；在负数派生表上的外部连接是因为你根本不需要有任何负数。

Answer 3

另一种方法是将“积极”映射为“积极”，“否定”映射为“否定”:)

select r.result_id
from result as r
    inner join image as i on r.result_id = i.result_id
group by r.result_id
having sum(case i.preclassification when 'P' then 1 when 'N' then -1 end) > 0