PostgreSQL中多JSON数组的平均值计算方法

我来帮你搞定这个需求！我们需要把每个学生的JSON测试成绩拆分成对应位置的单个成绩，计算同位置的平均值，最后再聚合回JSON数组，同时满足精度要求。

以下是完整的SQL解决方案：

SELECT 
    'All' AS student,
    year,
    json_agg(ROUND(avg_score, 3) ORDER BY idx) AS averages
FROM (
    SELECT 
        m.year,
        ord.idx,
        AVG((ord.result)::numeric) AS avg_score
    FROM marks m
    CROSS JOIN LATERAL json_array_elements_text(m.testresult) WITH ORDINALITY AS ord(result, idx)
    WHERE m.year = 2017
    GROUP BY m.year, ord.idx
) sub
GROUP BY year;

代码分步说明： #

• 拆分JSON数组：通过json_array_elements_text搭配WITH ORDINALITY，把每个学生的testresult数组拆成单个成绩（result）和对应的测试序号（idx），这样就能精准区分是第几次测试的成绩。
• 转换数值并计算平均：将字符串类型的成绩转成numeric类型后，按年份和测试序号分组，计算每组的平均值。
• 聚合回JSON数组：用json_agg把每个位置的平均值按序号顺序重新拼成JSON数组，同时用ROUND(avg_score, 3)保留三位小数，满足精度要求。
• 格式化输出：指定student为All，按年份分组得到最终结果。

执行这段SQL后，就能得到你想要的结果：

student| year | averages
-------+------+-----------
All    | 2017 | [6.567, 7.033, 7.700]

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