获取(count(column1) + count(column2))的和

Question

我有一个表a:

entity_id    name
------------------
1            Test1
2            Test2
3            Test3
4            Test4
5            Test5
6            Test6

我有一张表B:

entity_id   value1     value2
-----------------------------
1           10          20
1           15          30
2           10          25
1           9           45
3           null        1
2           45          50
3           20          null

我需要编写一个查询从表a中选择entity_id和name，并计算表B中列value1和value2的entity_id的总出现次数，然后计算这些列计数的总数(null不计算)。

那么我的输出表就是:

entity_id    name         value1_count    value2_count     total_count
----------------------------------------------------------------------
1            Test1             3               3              6
2            Test2             1               2              3
3            Test3             1               1              2
4            Test4             0               0              0
5            Test5             0               0              0
6            Test6             0               0              0

我在计算value1的计数和value2的计数并在每个唯一entity_it的total_count中输出该值时遇到了麻烦。

这是我到目前为止的查询:

SELECT DISTINCT a.entity_id, a.name
, count(b.value1) AS value1_count, count(b.value2) AS value2_count, sum(2) AS total_count
FROM a
LEFT JOIN b ON a.entity_id = b.entity_id
GROUP BY a.entity_id, a.name

我知道sum(2) as total_count是不正确的，不能得到我想要的。

Answer 1

SELECT entity_id, a.name
, COALESCE(b.v1_ct, 0) AS value1_count
, COALESCE(b.v2_ct, 0) AS value2_count
, COALESCE(b.v1_ct + b.v2_ct, 0) AS total_count
FROM   a
LEFT   JOIN (
SELECT entity_id, count(value1) AS v1_ct, count(value2) AS v2_ct
FROM   b
GROUP  BY 1
) b USING (entity_id);

db<此处小提琴>

先聚合，后加入。这样更简单，更快捷。看到:

• LEFT JOIN不返回计数为0的行

count()不产生NULL。在此查询中，只有LEFT JOIN可以为计数引入NULL值，因此v1_ct和v2_ct要么是NULL，要么是NOT NULL。因此，COALESCE(v1_ct + v2_ct, 0)是可以的。(否则，一个NULL将使加法中的另一个求和无效。)

Answer 2

try this:

WITH list AS
(
SELECT b.entity_id
, count(*) FILTER (WHERE b.value1 IS NOT NULL) OVER () AS value1_count
, count(*) FILTER (WHERE b.value2 IS NOT NULL) OVER () AS value2_count
FROM Table_B AS b
GROUP BY b.entity_id
)
SELECT a.entity_id, a.name
, COALESCE(l.value1_count, 0)
, COALESCE(l.value2_count,0)
, COALESCE(l.value1_count + l.value2_count, 0) AS total_count
FROM Table_A AS a
LEFT JOIN list AS l
ON a.entity_id = l.entity_id