固定长度double precision[]列使用avg、stddev聚合函数报错求解决方案

PostgreSQL原生支持数组的min()和max()元素级聚合，但确实没有内置的avg()或stddev()数组聚合函数，以下是三种可行的解决方式：

1. 直接按数组索引手动计算 #

如果数组长度固定且较短，直接指定每个索引位置分别计算聚合值，再重组为数组：

-- 计算元素级平均值
SELECT 
  ARRAY[
    AVG(array_col[1]),
    AVG(array_col[2]),
    AVG(array_col[3]) -- 按实际数组长度补充更多索引
  ] AS array_avg
FROM your_table;

-- 计算元素级标准差
SELECT 
  ARRAY[
    STDDEV(array_col[1]),
    STDDEV(array_col[2]),
    STDDEV(array_col[3])
  ] AS array_stddev
FROM your_table;

2. 用unnest+索引动态处理 #

如果数组长度较长但固定，无需硬编码索引，通过unnest带序号的方式拆分数组，按位置分组聚合后重组：

-- 元素级平均值
SELECT 
  ARRAY_AGG(avg_val ORDER BY idx) AS array_avg
FROM (
  SELECT 
    idx,
    AVG(val) AS avg_val
  FROM your_table,
       UNNEST(array_col) WITH ORDINALITY AS t(val, idx)
  GROUP BY idx
) sub_query;

-- 元素级标准差
SELECT 
  ARRAY_AGG(stddev_val ORDER BY idx) AS array_stddev
FROM (
  SELECT 
    idx,
    STDDEV(val) AS stddev_val
  FROM your_table,
       UNNEST(array_col) WITH ORDINALITY AS t(val, idx)
  GROUP BY idx
) sub_query;

这个方法会自动适配数组的固定长度，不用修改代码适配不同长度。

3. 自定义数组聚合函数 #

如果需要频繁使用元素级avg/stddev，可以自定义聚合函数，实现和min()/max()一样的直接调用体验：

自定义元素级avg聚合函数 #

-- 先创建存储聚合中间状态的复合类型
CREATE TYPE array_avg_accum AS (
  sums double precision[],
  counts integer[]
);

-- 定义状态转移函数：累加每个位置的数值和计数
CREATE OR REPLACE FUNCTION array_avg_transition(array_avg_accum, double precision[])
RETURNS array_avg_accum AS $$
BEGIN
  IF $1.sums IS NULL THEN
    RETURN (
      $2,
      ARRAY(SELECT 1 FROM generate_subscripts($2, 1))
    )::array_avg_accum;
  END IF;
  -- 校验数组长度一致（符合固定长度列的场景）
  IF array_length($1.sums, 1) != array_length($2, 1) THEN
    RAISE EXCEPTION 'Arrays must have the same fixed length';
  END IF;
  RETURN (
    ARRAY(SELECT $1.sums[i] + $2[i] FROM generate_subscripts($1.sums, 1) AS i),
    ARRAY(SELECT $1.counts[i] + 1 FROM generate_subscripts($1.counts, 1) AS i)
  )::array_avg_accum;
END;
$$ LANGUAGE plpgsql;

-- 定义最终计算函数：用总和除以计数得到平均值
CREATE OR REPLACE FUNCTION array_avg_final(array_avg_accum)
RETURNS double precision[] AS $$
BEGIN
  RETURN ARRAY(
    SELECT $1.sums[i] / $1.counts[i]
    FROM generate_subscripts($1.sums, 1) AS i
  );
END;
$$ LANGUAGE plpgsql;

-- 创建avg聚合函数
CREATE AGGREGATE avg(double precision[]) (
  SFUNC = array_avg_transition,
  STYPE = array_avg_accum,
  FINALFUNC = array_avg_final
);

自定义元素级stddev聚合函数 #

类似地，标准差需要存储平方和、总和、计数三个中间值，代码如下：

CREATE TYPE array_stddev_accum AS (
  sums double precision[],
  sum_squares double precision[],
  counts integer[]
);

CREATE OR REPLACE FUNCTION array_stddev_transition(array_stddev_accum, double precision[])
RETURNS array_stddev_accum AS $$
BEGIN
  IF $1.sums IS NULL THEN
    RETURN (
      $2,
      ARRAY(SELECT val * val FROM UNNEST($2) val),
      ARRAY(SELECT 1 FROM generate_subscripts($2, 1))
    )::array_stddev_accum;
  END IF;
  IF array_length($1.sums, 1) != array_length($2, 1) THEN
    RAISE EXCEPTION 'Arrays must have the same fixed length';
  END IF;
  RETURN (
    ARRAY(SELECT $1.sums[i] + $2[i] FROM generate_subscripts($1.sums, 1) AS i),
    ARRAY(SELECT $1.sum_squares[i] + ($2[i] * $2[i]) FROM generate_subscripts($1.sum_squares, 1) AS i),
    ARRAY(SELECT $1.counts[i] + 1 FROM generate_subscripts($1.counts, 1) AS i)
  )::array_stddev_accum;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION array_stddev_final(array_stddev_accum)
RETURNS double precision[] AS $$
BEGIN
  RETURN ARRAY(
    SELECT SQRT( ($1.sum_squares[i]/$1.counts[i]) - (($1.sums[i]/$1.counts[i]) * ($1.sums[i]/$1.counts[i])) )
    FROM generate_subscripts($1.sums, 1) AS i
  );
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE stddev(double precision[]) (
  SFUNC = array_stddev_transition,
  STYPE = array_stddev_accum,
  FINALFUNC = array_stddev_final
);

创建完成后，就可以像用min()/max()一样直接调用：

SELECT avg(array_col) FROM your_table;
SELECT stddev(array_col) FROM your_table;

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