PostgreSQL - 对条件成立的行选择count（*） [英] PostgreSQL - select count(*) for rows where a condition holds

问题描述

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

  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 ，意味着每个用户只能创建

我的目标是计算 rating_id = 1的行数，但只计算 attr1_id 和 attr2_id 的每个组合只有一次，而且只有在没有任何其他其他用户拥有 rating_id> 1 并引用相同的 attr1_id 和 attr2_id 。
注意，可以切换 attr1_id 和 attr2_id 的组合，所以给定这两个记录： / p>

  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）
）
）如何实现这个？
 



< >解决方案

如果我理解正确，你想要的属性的评级总是1。

这应该给你的属性：

 选择最少（attr1_id，attr2_id）为a1，最大（attr1_id，attr2_id）为a2，
 min ）作为minri，max（rating_id）作为maxri 
从兼容性c 
组最小（attr1_id，attr2_id），最大（attr1_id，attr2_id）
具有min（rating_id）= 1和max rating_id）= 1; 
  

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

  select（*）
 from（select least（attr1_id，attr2_id）as a1，highest（attr1_id，attr2_id）as a2，
 min （att_id1，attr2_id），最大（attr1_id，attr2_id），具有min（rating_id）= 1的
和来自兼容性c 
的最小值（rating_id） max（rating_id）= 1 
）c 
  

I have the following table and some sample records in it:

  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 |

The combination of (attr1_id, attr2_id, user_id) is UNIQUE, meaning each user can only create one record with a specific pair of attribute ids.

My goal is to count the number of rows where rating_id = 1, but only count each combiniation of attr1_id and attr2_id only once, and only where there doesn't exist any other row (by other users) that have rating_id > 1 and refer to the same attr1_id and attr2_id. Note that the combination of attr1_id and attr2_id can be switched around, so given these two records:

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

no row should be counted, as the rows refer to the same combination of attr_ids and one of them has rating_id > 1.

However, if these two rows exist:

  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 |

all rows should only be counted as one, because they all share the same combination of attr1_id and attr2_id and all have rating_id = 1.

My approach so far is this, but it results in no rows being selected at all.

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)
           )
    )

How can I achieve this?

解决方案

If I understand correctly, you want pairs of attributes whose ratings are always "1".

This should give you the attributes:

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;

To get the count, just use this as a subquery:

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

这篇关于PostgreSQL - 对条件成立的行选择count（*）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！