PostgreSQL - 按索引對數組求和

我有一個雙精度列數組（雙精度$$ $$) 在 PostgreSQL 中保留一天的半小時值。所以每個數組包含 48 個值。我需要一個有效的查詢，按索引對所有這些數組列求和並生成一個新的 48 數組索引，如下所述

A = double[48] = {3,2,0,3....1}
B = double[48] = {1,0,3,2....5}

RESULT = double[48] = {A[0] + B[0], A[1] + B[1],...,A[47] + B[47]}

謝謝！

我將unnest與 一起使用array_agg，如下所示： SQL Fiddle

PostgreSQL 9.3.1 架構設置：

create table t (
A double precision[5],
B double precision[5]);

insert into t values
('{3,2,0,3,1}', '{1,0,3,2,5}');

查詢 1：

with c as(
select unnest(a) a, unnest(b) b from t)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c

結果：

|         A |         B |         C |
|-----------|-----------|-----------|
| 3,2,0,3,1 | 1,0,3,2,5 | 4,2,3,5,6 |

如下所述，上面的查詢適用於相同大小的數組。否則可能會產生意想不到的結果。

如果您需要支持不同大小的數組，請使用此查詢：

with a as(
 select unnest(a) a from t),
b as(
 select unnest(b) b from t),
ar as(
 select a, row_number() over() r from a),
br as(
 select b, row_number() over() r from b),
c as(
 select ar.a, br.b from ar inner join br on ar.r = br.r)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c;

使用@cha 的模式，這裡是如何在 PostgreSQL 9.4 中使用支持unnest ... with ordinality：

SELECT array_agg(unnest_a.unnest_a + unnest_b.unnest_b ORDER BY unnest_a.ordinality)
FROM t,
    LATERAL unnest(a) WITH ORDINALITY AS unnest_a 
    INNER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b 
    ON (unnest_a.ordinality = unnest_b.ordinality);

或者，使用 a 處理長度不均勻的數組FULL OUTER JOIN：

SELECT array_agg(
        coalesce(unnest_a.unnest_a,0) + 
        coalece(unnest_b.unnest_b,0) 
        ORDER BY unnest_a.ordinality
      )
FROM t,
    LATERAL unnest(a) WITH ORDINALITY AS unnest_a 
    FULL OUTER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b 
    ON (unnest_a.ordinality = unnest_b.ordinality);

對於 PostgreSQL 9.3，我只使用 PL/Python：

create or replace function sumarrays(a float8[], b float8[]) returns float8[] language plpythonu as $$
return [ (ax or 0) + (bx or 0) for (ax, bx) in map(None, a, b) ]
$$;

要在不使用擴展過程語言的情況下支持 9.3，您可以LATERAL unnest ... WITH ORDINALITY使用子查詢替換每個row_number()，例如

   LATERAL unnest(a) WITH ORDINALITY AS unnest_a 

變成：

   (SELECT unnest_a, row_number() OVER () AS ordinality
    FROM unnest(a) AS unnest_a) AS unnest_a

從技術上講，不能保證 PostgreSQL 會unnest按順序處理 ed 行，所以row_number() OVER ()有點冒險，但實際上它適用於所有目前的 PostgreSQL 版本。

不過，最好的解決方案實際上是編寫一個簡單的 C 擴展。

引用自：https://dba.stackexchange.com/questions/77982