PostgreSQL: Case与条件基于两个列

Question

想象一下虚拟数据

id   name     category    score   
1    Alex     A           11        
2    Alex     D           4      
3    Bill     A           81     
4    Bill     B           34       
5    Bill     D           2       
6    Carl     C           5       
7    Carl     D           10       

我想应用动作:

if score of A, B, or C > score of D
then 'Review'
else 'Pass'

所以输出是:

id   name     category    score   conclusion
1    Alex     A           11       Review
2    Alex     D           4        Review
3    Bill     A           81       Review
4    Bill     B           34       Review
5    Bill     D           2        Review
6    Carl     C           5        Pass
7    Carl     D           10       Pass

我如何在PostgreSQL中获得这个?

Answer 1

您希望使用窗口函数进行条件聚合:

select
id, name, category, score,
case when 
max(score) filter (where category in ('A', 'B', 'C')) over (partition by name) >
min(score) filter (where category = 'D')              over (partition by name)
then 'Review'
else 'Pass'
end as result
from mytable
order by name, id;

如果一个名字没有A, B或C，或者一个名字没有D，结果将是'Pass'。如果你想要不同的，那么你必须调整比较。

上面的查询为您提供了显示在所有行中的每个人的状态。如果您希望每行显示不同的状态，只需比较该行的得分:

select
id, name, category, score,
case 
when category = 'D' then null
when score > min(score) filter (where category = 'D') over (partition by name) then 'Review'
else 'Pass'
end as result
from mytable
order by name, id;