Фильтрация в агрегатах Postrgres

Question

Фильтрация в агрегатах Postrgres

У меня есть стол в 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 '%@moderators-domain.net'

Пример данных:

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

Желаемые результаты:

reviewer_email  | overruled_count | affirmed_count
bob@email.net   |               1 |              1
alice@email.net |               1 |              1

Боб и Алиса сделали по три отзыва. В одном обзоре они согласились, поэтому не было никакой модерации. Они не согласились с двумя другими отзывами и были отменены один раз, а один раз подтвержден модератором.

Я полагаю, что приведенный выше код направил меня на правильный путь, но я определенно заинтересован в других подходах к этому.

8

sql postgresql subquery aggregate-functions

Источник

user94154 07 май '18 в 01:22

3 ответа

Решение

Пример данных

CREATE TABLE tasks
    ("id" int, "entity_name" text, "reviewer_email" text, "result" boolean)
;

INSERT INTO tasks
    ("id", "entity_name", "reviewer_email", "result")
VALUES
    (1, 'apple', 'bob@email.net', 'true'),
    (2, 'apple', 'alice@email.net', 'false'),
    (3, 'apple', 'mod@@moderators-domain.net', 'true'),
    (4, 'pair', 'bob@email.net', 'true'),
    (5, 'pair', 'alice@email.net', 'false'),
    (6, 'pair', 'mod@@moderators-domain.net', 'false'),
    (7, 'kiwi', 'bob@email.net', 'true'),
    (8, 'kiwi', 'alice@email.net', 'true')
;

Запрос 1

WITH
CTE_moderated_tasks
AS
(
  SELECT
    id AS mod_id
    ,entity_name AS mod_entity_name
    ,result AS mod_result
  FROM tasks
  WHERE reviewer_email LIKE '%@moderators-domain.net'
)
SELECT
  tasks.reviewer_email
  ,SUM(CASE WHEN tasks.result <> mod_result THEN 1 ELSE 0 END) AS overruled_count 
  ,SUM(CASE WHEN tasks.result = mod_result THEN 1 ELSE 0 END) AS affirmed_count 
FROM
  CTE_moderated_tasks
  INNER JOIN tasks ON 
    tasks.entity_name = CTE_moderated_tasks.mod_entity_name
    AND tasks.id <> CTE_moderated_tasks.mod_id
GROUP BY
  tasks.reviewer_email

Я разделил запрос на две части.

Сначала хочу найти все задания, в которых участвовал модератор (CTE_moderated_tasks). Предполагается, что модератор не может быть вовлечен более одного раза в одну и ту же задачу.

Этот результат соединен с оригиналом tasks Таким образом, таблица, естественно, отфильтровывает все задачи, в которых не участвовал модератор. Это также дает нам мнение модератора рядом с мнением рецензента. Это предполагает, что есть только два рецензента для одной и той же задачи.

Теперь осталось только сгруппировать рецензентов и посчитать, сколько раз совпадало мнение рецензента и модератора. Я использовал классический SUM(CASE ...) для этого условного агрегата.

Вам не нужно использовать CTE, я использовал его в первую очередь для удобства чтения.

Я также хотел бы подчеркнуть, что этот запрос использует LIKE только во время одного сканирования таблицы. Если есть индекс на entity_name объединение может быть довольно эффективным.

Результат

|  reviewer_email | overruled_count | affirmed_count |
|-----------------|-----------------|----------------|
| alice@email.net |               1 |              1 |
|   bob@email.net |               1 |              1 |

,

Вариант без самостоятельного присоединения

Вот еще один вариант без самостоятельного объединения, который может быть более эффективным. Вы должны проверить свои реальные данные, индексы и оборудование.

Этот запрос использует оконную функцию с разбиением по entity_name выводить результаты модератора для каждой строки без явного самосоединения. Вы можете использовать любую агрегатную функцию здесь (SUM или же MIN или же MAX), потому что будет не более одного ряда от модератора для каждого entity_name,

Тогда простая группировка с условным агрегатом даст нам счет.

Здесь условный агрегат использует тот факт, что NULL по сравнению с любым значением никогда не возвращает истину. mod_result для объектов, которые не имеют модератора, будут иметь значения NULL и оба result <> mod_result а также result = mod_result выдаст false, поэтому такие строки не влияют ни на счет.

окончательный HAVING reviewer_email NOT LIKE '%@moderators-domain.net' удаляет счетчик результатов модератора самостоятельно.

Опять же, вам не нужно использовать CTE здесь, и я использовал его в первую очередь для удобства чтения. Я бы рекомендовал сначала запустить CTE и изучить промежуточные результаты, чтобы понять, как работает запрос.

Запрос 2

WITH
CTE
AS
(
  SELECT
    id
    ,entity_name
    ,reviewer_email
    ,result::int
    ,SUM(result::int)
      FILTER (WHERE reviewer_email LIKE '%@moderators-domain.net') 
      OVER (PARTITION BY entity_name) AS mod_result
  FROM tasks
)
SELECT
  reviewer_email
  ,SUM(CASE WHEN result <> mod_result THEN 1 ELSE 0 END) AS overruled_count 
  ,SUM(CASE WHEN result = mod_result THEN 1 ELSE 0 END) AS affirmed_count 
FROM CTE
GROUP BY reviewer_email
HAVING reviewer_email NOT LIKE '%@moderators-domain.net'

2

Источник

user4116017 10 май '18 в 11:49

Просто добавив некоторые изменения, чтобы сделать запрос немного проще для понимания, на мой взгляд. Я предполагаю, что мы также должны рассмотреть случай, когда у нас есть пользователи, которые никогда не были ни подтверждены, ни отменены (поэтому их количество будет равно 0)

SELECT
    tasks.reviewer_email,
    COUNT(*) FILTER (WHERE tasks.result = modtasks.result) AS affirmed_count,
    COUNT(*) FILTER (WHERE tasks.result <> modtasks.result) AS overruled_count
  FROM tasks
  LEFT JOIN tasks modtasks
      ON modtasks.entity_name = tasks.entity_name
      AND modtasks.reviewer_email LIKE '%@moderators-domain.net'
 WHERE tasks.reviewer_email NOT LIKE '%@moderators-domain.net'
 GROUP BY tasks.reviewer_email

2

Источник

user3620775 10 май '18 в 09:26

Другие вопросы по тегам sql postgresql subquery aggregate-functions

user1144035 07 май '18 в 01:42 2018-05-07 01:42 · Accepted Answer · 2018-05-07 01:42

Я думаю, что это более сложная проблема, чем вы можете себе представить. Следующее добавляет обзор модератора к каждому отзыву, не являющемуся модератором:

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 '%@moderators-domain.net' and
      tm.reviewer_email LIKE '%@moderators-domain.net';

Исходя из этого, мы можем агрегировать результаты, которые вы хотите:

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 '%@moderators-domain.net' and
            tm.reviewer_email LIKE '%@moderators-domain.net'
     ) 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 '%@moderators-domain.net' and
      tm.reviewer_email LIKE '%@moderators-domain.net'
group by t.reviewer_email;