PostgreSQL如何对两个integer[]类型整数数组按元素求和

PostgreSQL原生未提供数组按对应元素相加的+运算符，因此直接对两个数组执行加法会抛出operator does not exist: integer[] + integer[]错误，可通过以下几种方案实现需求：

方案1：无需自定义函数，用UNNEST实现（推荐，兼容性最好） #

利用UNNEST函数的WITH ORDINALITY属性获取数组元素的位置，关联两个数组同位置的元素相加后重新聚合为数组，适配任意长度的等长数组：

SELECT 
  "ID",
  ARRAY_AGG(h1.val + h2.val ORDER BY ord) AS merged_histogram
FROM (
  SELECT 
    "ID",
    histogram(...) AS h1,
    histogram(...) AS h2
  FROM "your_table"
  GROUP BY "ID"
) t,
UNNEST(t.h1) WITH ORDINALITY AS h1(val, ord),
UNNEST(t.h2) WITH ORDINALITY AS h2(val, ord)
WHERE h1.ord = h2.ord
GROUP BY "ID";

该方案要求两个histogram生成的数组长度完全一致，否则会丢失长度更长的数组中超出部分的元素。

方案2：自定义数组加法运算符（一劳永逸） #

如果经常需要做数组按位相加操作，可以先自定义对应函数，再绑定+运算符，之后就可以直接用数组+数组的写法：

1. 首先创建数组相加函数

CREATE OR REPLACE FUNCTION array_add(a integer[], b integer[])
RETURNS integer[] AS $$
DECLARE
  result integer[];
  len integer;
  i integer;
BEGIN
  -- 取两个数组的最大长度，超出部分用0补全后相加，也可根据需求调整为取最小长度
  len := GREATEST(ARRAY_LENGTH(a, 1), ARRAY_LENGTH(b, 1));
  result := ARRAY_FILL(0, ARRAY[len]);
  FOR i IN 1..len LOOP
    result[i] := COALESCE(a[i], 0) + COALESCE(b[i], 0);
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 绑定对应+运算符

CREATE OPERATOR + (
  LEFTARG = integer[],
  RIGHTARG = integer[],
  PROCEDURE = array_add,
  COMMUTATOR = +
);

配置完成后你最初的SQL就可以直接执行：

SELECT "ID", histogram(...) + histogram(...)
FROM "your_table"
GROUP BY "ID"

该方案需要有数据库的函数、运算符创建权限，运算符重载后仅对当前数据库生效。

方案3：固定长度数组简化写法 #

如果Timescale的histogram传入的bucket参数固定，生成的数组长度已知且较短，也可以直接通过下标遍历相加，比如固定生成3个元素的数组：

SELECT 
  "ID",
  ARRAY[
    (histogram(...))[1] + (histogram(...))[1],
    (histogram(...))[2] + (histogram(...))[2],
    (histogram(...))[3] + (histogram(...))[3]
  ] AS merged_histogram
FROM "your_table"
GROUP BY "ID"

该方案灵活性较低，仅适用于数组长度固定的场景。

内容的提问来源于stack exchange，提问作者Paul Müller