PostgreSQL-为条件成立的行选择count（*）

Question

我有下表和一些示例记录：

  id  | attr1_id | attr2_id |      user_id      | rating_id | override_comment
------+----------+----------+-------------------+-----------+------------------
 1    |      188 |      201 | [e-mail] |         3 |
 2    |      193 |      201 | [e-mail] |         2 |
 3    |      193 |      201 | [e-mail] |         1 |
 4    |      194 |      201 | [e-mail] |         1 |
 5    |      194 |      201 | [e-mail] |         1 |
 6    |      192 |      201 | [e-mail] |         1 |

的组合（attr1_id，attr2_id，user_id）的UNIQUE，这意味着每个用户只能创建与特定的一对属性id的一个记录。

我的目标是计算的行数rating_id = 1，但仅计算和的每次组合，attr1_id并且attr2_id仅计算一次，并且仅计算不存在（由其他用户使用）具有rating_id > 1和引用相同attr1_id和的任何其他行的行attr2_id。请注意，组合attr1_id和attr2_id可切换左右，所以给这两个记录：

  id  | attr1_id | attr2_id |      user_id       | rating_id | override_comment
------+----------+----------+--------------------+-----------+------------------
  20  |       5  |       2  | [e-mail]  |         3 |
------+----------+----------+--------------------+-----------+------------------
  21  |       2  |       5  | [e-mail]  |         1 |

不应计算任何行，因为这些行指的是的相同组合，attr_ids其中之一具有rating_id > 1。

但是，如果存在这两行：

  id  | attr1_id | attr2_id |      user_id       | rating_id | override_comment
------+----------+----------+--------------------+-----------+------------------
  20  |       5  |       2  | [e-mail]  |         1 |
------+----------+----------+--------------------+-----------+------------------
  21  |       2  |       5  | [e-mail]  |         1 |
------+----------+----------+--------------------+-----------+------------------
  22  |       2  |       5  | [e-mail]  |         1 |

所有行应该只能算作一个，因为他们都有着相同的组合attr1_id，并attr2_id和所有有rating_id = 1。

到目前为止，我的方法是这样，但是它根本没有选择任何行。

SELECT *
FROM compatibility c
WHERE rating_id > 1
  AND NOT EXISTs
    (SELECT *
     FROM compatibility c2
     WHERE c.rating_id > 1
       AND (
             (c.attr1_id = c2.attr1_id) AND (c.attr2_id = c2.attr2_id)
             OR
             (c.attr1_id = c2.attr2_id) AND (c.attr2_id = c2.attr1_id)
           )
    )

我怎样才能做到这一点？

Answer 1

如果我理解正确，那么您想要的属性对始终为“ 1”。

这应该给您属性：

select least(attr1_id, attr2_id) as a1, greatest(attr1_id, attr2_id) as a2,
       min(rating_id) as minri, max(rating_id) as maxri
from compatibility c
group by least(attr1_id, attr2_id), greatest(attr1_id, attr2_id)
having min(rating_id) = 1 and max(rating_id) = 1;

要获得计数，只需将其用作子查询：

select count(*)
from (select least(attr1_id, attr2_id) as a1, greatest(attr1_id, attr2_id) as a2,
             min(rating_id) as minri, max(rating_id) as maxri
      from compatibility c
      group by least(attr1_id, attr2_id), greatest(attr1_id, attr2_id)
      having min(rating_id) = 1 and max(rating_id) = 1
     ) c