PostgreSQL: Adjust columns value based on criteria-CodePudding

Imagine the following data:

student    category exam_id    adjusted_category
Carl       A        44             A
Carl       A        55             A
Carl       A        88             A
Carl       A        1              A
Carl       A        2              A
Carl       A        3              A
Carl       B        1              B
Carl       B        2              B
Carl       B        3              B
John       C        100            C
John       C        200            C
John       C        300            C

If for the same user, both categories A and B are encountered but specific exam_ids are found (44, 55, 88), I'd like to adjust the category to be A. Otherwise, keep the same category.

The output I'm aiming is:

student    adjusted_category
Carl       A        
Carl       C

Code I'm currently attempting to modify:

with my_table (student, category, exam_id)
as (values 
('Carl', 'A', 44),
('Carl', 'A', 55),
('Carl', 'A', 88),
('Carl', 'A', 1),
('Carl', 'A', 2),
('Carl', 'A', 3),
('Carl', 'B', 1),
('Carl', 'B', 2),
('Carl', 'B', 3),
('John', 'C', 100),
('John', 'C', 200),
('John', 'C', 300)
) 


select *,
     case 
         when category in ('A','B') and exam_id in (44, 55, 88) then 'A'
         else category
     end as adjusted_category
from my_table

The reason why the code above is not what I'm after is because I end up getting the adjusted category as A only where the exams id_s are 44, 55, or 88. Id's like all of the entries for Carl to have A as the adjusted category.

How can I achieve the desired output?

CodePudding user response：

I may have misinterpreted your requirement. If so, you can change the bool_or() to bool_and(). The bool_or() expression over a window partitioned by student will return true if the student has any one of (44, 55, 88):

with my_table (student, category, exam_id)
as (values 
('Carl', 'A', 44),
('Carl', 'A', 55),
('Carl', 'A', 88),
('Carl', 'A', 1),
('Carl', 'A', 2),
('Carl', 'A', 3),
('Carl', 'B', 1),
('Carl', 'B', 2),
('Carl', 'B', 3),
('John', 'C', 100),
('John', 'C', 200),
('John', 'C', 300)
) 
select *,
     case 
         when category in ('A','B') 
           and bool_or(exam_id in (44, 55, 88)) over (partition by student) then 'A'
         else category
     end as adjusted_category
from my_table;

db<>fiddle here