PostgreSQL中如何对数组字段执行逐元素Avg()聚合并保留维度

这个需求完全可以实现，核心逻辑是先把数组拆分为元素和对应下标的行数据，按下标分组计算平均值后，再按下标顺序重组为数组，就能保留原数组的维度和顺序。

方法1：直接查询（无需额外定义函数） #

直接写SQL查询即可实现，适合临时查询场景：

SELECT array_agg(elem_avg ORDER BY idx) AS x
FROM (
    SELECT
        idx,
        AVG(elem) AS elem_avg
    FROM
        my_arrays,
        unnest(array_field) WITH ORDINALITY AS arr(elem, idx)
    GROUP BY idx
) t;

逻辑说明：unnest函数加WITH ORDINALITY参数会把数组的每个元素和它的对应下标（从1开始）拆分为独立行，按下标分组求平均值后，再用array_agg按下标顺序把平均值聚合为数组。

方法2：自定义聚合函数（可直接调用） #

如果你需要像使用内置avg函数一样直接调用，可以提前自定义一个数组逐元素求平均的聚合函数：

-- 定义数组逐元素求和的过渡函数
CREATE OR REPLACE FUNCTION array_sum_step(state float[], elem float[])
RETURNS float[] AS $$
SELECT array_agg(coalesce(s, 0) + coalesce(e, 0) ORDER BY idx)
FROM unnest(state) WITH ORDINALITY a(s, idx)
FULL OUTER JOIN unnest(elem) WITH ORDINALITY b(e, idx) USING (idx);
$$ LANGUAGE sql IMMUTABLE;

-- 定义最终计算平均值的收尾函数
CREATE OR REPLACE FUNCTION array_avg_final(state float[], count integer)
RETURNS float[] AS $$
SELECT array_agg(elem / count ORDER BY idx)
FROM unnest(state) WITH ORDINALITY a(elem, idx);
$$ LANGUAGE sql IMMUTABLE;

-- 注册自定义聚合函数
CREATE AGGREGATE avg_arr(float[]) (
    SFUNC = array_sum_step,
    STYPE = float[],
    FINALFUNC = array_avg_final,
    FINALFUNC_EXTRA = true,
    INITCOND = '{}'
);

定义完成后就可以用和你期望非常接近的语法查询：

SELECT avg_arr(array_field) AS x FROM my_arrays;

两种方法基于你提供的测试数据执行后，都会返回你预期的结果：

x
---------
{2, 2, 2}

注意：以上实现默认所有行的数组长度相同，如果存在数组长度不一致的情况，可根据业务需求调整缺省值处理逻辑。

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