Postgres 13.8：返回全量数据时实现科目状态计算方案问询

需要返回exam_assessment表的全部原始数据行，并根据以下规则为每个学生的对应科目计算整体状态SUBJECT_STATUS：

• 若该学生对应科目下任意章节的chapter_status或marked_status为IN_PROGRESS，则科目状态为IN_PROGRESS；
• 若该学生对应科目下所有章节的marked_status均为Marked as Complete，则科目状态为Marked as Complete；
• 若该学生对应科目下所有章节的marked_status仅包含Marked as Complete和Completed的组合（两种状态都存在或仅有一种但不是全为Marked as Complete），则科目状态为Completed。

方案一：关联子查询实现 #

SELECT 
    ea.*,
    CASE
        -- 匹配规则1：存在任意章节处于IN_PROGRESS状态
        WHEN EXISTS (
            SELECT 1 
            FROM exam_assessment ea_inner
            WHERE ea_inner.student_id = ea.student_id
              AND ea_inner.subject_name = ea.subject_name
              AND (ea_inner.chapter_status = 'IN_PROGRESS' OR ea_inner.marked_status = 'IN_PROGRESS')
        ) THEN 'IN_PROGRESS'
        -- 匹配规则2：所有章节的marked_status均为Marked as Complete
        WHEN (
            SELECT COUNT(*) 
            FROM exam_assessment ea_inner
            WHERE ea_inner.student_id = ea.student_id
              AND ea_inner.subject_name = ea.subject_name
              AND ea_inner.marked_status != 'Marked as Complete'
        ) = 0 THEN 'Marked as Complete'
        -- 匹配规则3：剩余情况为两种状态的组合
        ELSE 'Completed'
    END AS SUBJECT_STATUS
FROM exam_assessment ea;

方案二：CTE预统计优化（性能更优） #

针对数据量较大的场景，先通过CTE预计算每个学生-科目的统计指标，再关联原始表生成结果：

WITH student_subject_stats AS (
    SELECT 
        student_id,
        subject_name,
        -- 判断是否存在IN_PROGRESS的章节
        BOOL_OR(chapter_status = 'IN_PROGRESS' OR marked_status = 'IN_PROGRESS') AS has_in_progress,
        -- 统计非Marked as Complete的章节数量
        COUNT(CASE WHEN marked_status != 'Marked as Complete' THEN 1 END) AS non_marked_complete_count
    FROM exam_assessment
    GROUP BY student_id, subject_name
)
SELECT 
    ea.*,
    CASE
        WHEN sss.has_in_progress THEN 'IN_PROGRESS'
        WHEN sss.non_marked_complete_count = 0 THEN 'Marked as Complete'
        ELSE 'Completed'
    END AS SUBJECT_STATUS
FROM exam_assessment ea
JOIN student_subject_stats sss 
    ON ea.student_id = sss.student_id 
    AND ea.subject_name = sss.subject_name;

预期结果 #

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