PostgreSQL：基于两列条件的情况

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，则结果将为“通过”。如果您想要的是不同的，那么您必须调整Camparison。

上述查询使您每行显示一个状态。如果您想要每行不同的状态，只需与该行的分数进行比较：

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;