PostgreSQL满足条件时用另一表分组均值替换表中对应字段值

现有两个CTE：

• table_a：按月份、类别维度统计豁免学生数量、排除豁免学生后的考试平均分。
  
• table_b：存储学生个体考试明细信息（仅以1月数据为示例），table_a的统计结果即通过table_b的数据聚合计算得到。
  

豁免学生因特殊情况其考试成绩不纳入统计，现需要为豁免学生分配同类别、同月份下所有参加考试学生的平均成绩，期望效果如下：

当前核心卡点：table_a的时间字段是截断后的按月分组时间，table_b存储的是精确考试时间戳，无法直接关联两表完成成绩替换逻辑。
预置CTE代码如下：

with table_a (exam_month, category, "# exempt students", "mean score (excl. exempt students")
as (values 
('2022-01-01 12:00:00'::timestamp, 'A', 2, 61.8),
('2022-02-01 12:00:00'::timestamp, 'A', 1, 63.1),
('2022-03-01 12:00:00'::timestamp, 'A', 2, 61.1),
('2022-01-01 12:00:00'::timestamp, 'B', 4, 73.8),
('2022-02-01 12:00:00'::timestamp, 'B', 3, 71.3),
('2022-03-01 12:00:00'::timestamp, 'B', 2, 72.5)
),

table_b (student_id, exam_timestamp, category, score, student_status)
as (values
(1, '2022-01-10 09:30:00'::timestamp, 'A', 13.1, 'exempt'),
(2, '2022-01-12 12:00:00'::timestamp, 'A', 63.7, 'examined'),
(3, '2022-01-14 09:00:00'::timestamp, 'A', 59.2, 'examined'),
(4, '2022-01-16 14:30:00'::timestamp, 'A', 62.8, 'examined'),
(5, '2022-01-18 12:30:00'::timestamp, 'A', 41.2, 'exempt'),
(6, '2022-01-20 12:00:00'::timestamp, 'A', 61.5, 'examined')
)

关联核心是把两个表的时间字段统一对齐到月度粒度，忽略日、时、分、秒的差异，再按「月份+类别」双维度关联即可，无额外复杂计算。

实现代码 #

承接上述两个CTE，后续查询逻辑如下：

select
    b.student_id,
    b.exam_timestamp,
    b.category,
    case
        when b.student_status = 'exempt' then a."mean score (excl. exempt students"
        else b.score
    end as final_score,
    b.student_status
from table_b b
left join table_a a
    on b.category = a.category
    -- 两边时间都截断到月度维度，消除时分秒、日期差异
    and date_trunc('month', b.exam_timestamp) = date_trunc('month', a.exam_month)
;

逻辑说明 #

• date_trunc('month', 时间字段)是PostgreSQL原生的时间截断函数，会把任意时间统一归到所在月份的1号0点，自动忽略日、时、分、秒差异，性能远高于字符串拼接年月的写法。
• 对两个表的时间字段同时做截断，避免table_a里存储的月度时间带非0时分秒（比如示例里是每月1号12点）导致关联匹配失败。
• 左连接保证table_b所有学生记录完整保留，不会丢数。
• 用case when判断学生状态：豁免学生直接取同维度预计算的平均成绩，非豁免学生保留原始成绩即可。

结果验证 #

用示例中1月A类数据测试，返回结果完全符合预期：

如果使用其他SQL引擎，替换对应月度截断函数即可：MySQL用DATE_FORMAT(时间字段, '%Y-%m')、Hive/Spark用trunc(时间字段, 'MM')，核心关联逻辑完全一致。

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