PostgreSQL如何按组对数组逐元素实现聚合函数（如加权求和）

所用表结构包含grp、n、vals三列，现有样本数据：

• grp=0：n=2，vals={1,2,3,4}
• grp=1：n=5，vals={3,2,1,2}
• grp=1：n=3，vals={0,5,4,3}

需要完成的计算逻辑：按grp字段分组，组内先把每行的vals数组乘以对应行的标量n，再将缩放后的数组逐元素累加，最终每组输出一个结果数组，预期输出为：

• grp=0：{2,4,6,8}
• grp=1：{15,25,17,19}

• 第一种写法将UNNEST(vals)直接嵌套在聚合函数SUM内部，触发aggregate function calls cannot contain set-returning function calls报错：PostgreSQL 的语法规则不允许聚合函数内部直接调用返回多行的集合返回函数，必须把集合展开的动作前置到聚合计算环节之前，不能和聚合函数嵌套使用。
• 第二种写法使用generate_series展开数组元素时，仅对所有元素的值做全局求和，未保留元素的位置标记，最终只能得到组内的标量总值，无法得到逐元素聚合的数组结果。
• 第三种写法逐个硬编码数组下标的求和逻辑，虽然能拿到每个位置的计算值，但输出是分散的标量列，不是标准数组格式；且数组长度增大后需要编写大量重复代码，完全不具备实用性。

核心逻辑：先将所有行的数组展开为带位置标记的明细行，再按分组和元素位置做逐位置聚合求和，最后将同组下按位置排序的求和结果重新组装为数组，全程不需要硬编码数组长度。
实现SQL如下：

SELECT
    grp,
    array_agg(elem_total ORDER BY elem_idx) AS result_vals
FROM (
    SELECT
        grp,
        elem_idx,
        SUM(n * elem_val) AS elem_total
    FROM tbl,
         UNNEST(vals) WITH ORDINALITY AS t(elem_val, elem_idx)
    GROUP BY grp, elem_idx
) per_position_sum
GROUP BY grp;

方案优势：

• 无需硬编码数组长度，无论vals数组是4个元素还是上百个元素，只要同组内数组的元素位置一一对应，即可正常计算
• 借助UNNEST ... WITH ORDINALITY自动记录每个元素在原数组中的序号，组装数组时按序号排序，不会打乱元素的原有顺序
• 完全规避了聚合函数嵌套集合返回函数的语法报错：数组展开动作在子查询中完成，早于聚合计算环节

上述SQL的输出完全匹配预期：

• grp=0 组仅1行数据，缩放后数组为{2,4,6,8}，聚合结果与预期一致
• grp=1 组两行数据缩放后分别为{15,10,5,10}、{0,15,12,9}，逐元素累加后结果为{15,25,17,19}，与预期完全匹配

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