postgresql count distinct by col and sum false and true

Question

如何使用Sqlstatement查询Expect Count的结果

ID      StuId    IsDone    ExpectCount
11111   q-01     false        1         
11111   q-02     false        2      
11111   q-03     false        3        
11111   q-02     true         2   
11111   q-04     false        3   
22222   q-04     false        2
11111   q-01     true         1  
11111   q-01     true         1 

期望如果相同的ID StuId IsDone = true将忽略它。

11111 = 3
22222 = 1

但

11111 = 1
22222 = 1

我查询

SELECT ID, count(*)
FROM
(SELECT DISTINCT StuId, ID, IsDone FROM Student s where IsDone = false) stu
group by ID;

Answer 1

您可以通过更改查询获得预期的输出，如本演示所示。

SELECT ID, COUNT(DISTINCT StuId) - SUM(CASE WHEN IsDone = true THEN 1 ELSE 0 END) as ExpectedCount
FROM Student
GROUP BY ID;

输出:

tbody> <<tr>

id	ExpectedCount
11111	3
22222	1

正确的解决方案是进行2级聚合。首先，我们可以通过ID和StuId进行聚合，以确定哪些StuId只有假的IsDone值。然后，我们单独按ID进行聚合，以获得纯错误计数。

WITH cte AS (
SELECT ID, StuId, COUNT(*) FILTER (WHERE IsDone = true) cnt
FROM Student
GROUP BY ID, StuId
)
SELECT ID, COUNT(*) FILTER (WHERE cnt = 0) AS cnt
FROM cte
GROUP BY ID
ORDER BY ID;