PostgreSQL按分组条件统一调整category字段值的SQL实现

基础示例数据 #

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

业务规则 #

同一学生同时满足以下两个条件时，其所有记录的adjusted_category统一设为A，其余情况保留原category值：

• 记录中同时存在A、B两类category
• 存在exam_id为44、55、88的记录

期望输出 #

注：原示例第二行学生名存在笔误，实际应为John对应C，正确输出如下：

student    adjusted_category
Carl       A        
John       C        

原有代码问题 #

原有SQL逻辑仅针对单条记录做判断，只有同时满足category为A/B且exam_id为44/55/88的单条记录会被赋值为A，无法实现符合条件的学生名下所有记录统一调整的效果，原有代码如下：

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

正确实现代码 #

核心思路是先按学生维度聚合，计算每个学生是否符合调整条件，再将标记关联回明细表做统一赋值：

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)
),
student_qualify as (
    select
        student,
        case
            -- 判断是否同时拥有A、B两个分类
            when count(distinct case when category in ('A','B') then category end) = 2
            -- 判断是否同时存在44、55、88三个考试ID的记录
            and count(distinct case when exam_id in (44,55,88) then exam_id end) = 3
            then true else false
        end as is_adjust_to_A
    from my_table
    group by student
)
select
    t.student,
    case when q.is_adjust_to_A then 'A' else t.category end as adjusted_category
from my_table t
join student_qualify q on t.student = q.student
-- 需要明细行就去掉下面的group by，需要学生维度去重结果就保留
group by t.student, adjusted_category
;

逻辑说明 #

• 用CTEstudent_qualify按学生分组做条件聚合，避免逐行判断的局限性
• 条件计数中用distinct去重，避免重复记录干扰判断结果
• 关联标记后，符合调整条件的学生所有记录统一赋值为A，不符合条件的保留原分类
• 可按需选择输出全量明细行，或是学生维度的去重结果

内容的提问来源于stack exchange，提问作者Joehat