sql - 在Postgres聚合中进行过滤

我在Postgres中有一个名为tasks的表。它记录了机械特克式的任务。它包含以下列:

entity_name, text (the thing being reviewed)
reviewer_email, text (the email address of the person doing the reviewing)
result, boolean (the entry provided by the reviewer)

需要审查的每个实体都会导致产生两个任务行，每个任务行都分配给不同的审查者。当两位审稿人不同意时(例如，他们的result值不相等)，应用程序将启动分配给主持人的第三项任务。主持人始终具有相同的电子邮件域。

我试图获取每次审阅者被主持人否决或得到主持人确认的次数。我想我已经很接近了，但最后一点证明是棘手的:

SELECT
  reviewer_email,
  COUNT(*) FILTER(
    WHERE entity_name IN (
      SELECT entity_name
      FROM tasks
      GROUP BY entity_name
      HAVING
        COUNT(*) FILTER (WHERE result IS NOT NULL) = 3 -- find the entities that have exactly three reviews
      AND
        -- this is the tricky part:
        -- need something like:
        -- WHERE current_review.result = moderator_review.result
    )
  ) AS overruled_count
FROM
  tasks
WHERE
  result IS NOT NULL
GROUP BY
  reviewer_email
HAVING
  reviewer_email NOT LIKE '[e-mail]'

样本数据:

id | entity_name | reviewer_email             | result
 1 | apple       | [email protected]              | true
 2 | apple       | [email protected]            | false
 3 | apple       | mod@@moderators-domain.net | true
 4 | pair        | [email protected]              | true
 5 | pair        | [email protected]            | false
 6 | pair        | mod@@moderators-domain.net | false
 7 | kiwi        | [email protected]              | true
 8 | kiwi        | [email protected]            | true

所需结果:

reviewer_email  | overruled_count | affirmed_count
[email protected]   |               1 |              1
[email protected] |               1 |              1

鲍勃和爱丽丝都做过三则评论。在一次审查中，他们表示同意，因此没有节制。他们不同意其他两项评论，被否决一次，由主持人确认一次。

我相信上面的代码可以使我走上正确的道路，但是我当然对其他实现此方法的方法感兴趣。

最佳答案

我认为这是一个比您可能意识到的更棘手的问题。以下内容将主持人审阅附加到每个非主持人审阅中:

select t.*, tm.result as moderator_result
from tasks t join
    tasks tm
    on t.entity_name = tm.entity_name
where t.reviewer_email NOT LIKE '[e-mail]' and
      tm.reviewer_email LIKE '[e-mail]';

由此，我们可以汇总所需的结果:

select reviewer_email,
       sum( (result = moderator_result)::int ) as moderator_agrees,
       sum( (result <> moderator_result)::int ) as moderator_disagrees
from (select t.*, tm.result as moderator_result
      from tasks t join
           tasks tm
           on t.entity_name = tm.entity_name
      where t.reviewer_email NOT LIKE '[e-mail]' and
            tm.reviewer_email LIKE '[e-mail]'
     ) t
group by reviewer_email;

可以使用filter甚至窗口函数来执行此操作。这种方法对我来说似乎是最自然的。

我应该注意，子查询不是必需的:

select t.reviewer_email,
       sum( (t.result = tm.result)::int ) as moderator_agrees,
       sum( (t.result <> tm.result)::int ) as moderator_disagrees
from tasks t join
     tasks tm
     on t.entity_name = tm.entity_name
where t.reviewer_email NOT LIKE '[e-mail]' and
      tm.reviewer_email LIKE '[e-mail]'
group by t.reviewer_email;