Postgres中的向量(数组)加法 [英] Vector (array) addition in Postgres
问题描述
我有一列具有 numeric []
值的列,它们的大小均相同。我想按元素进行平均。我的意思是平均
I have a column with numeric[]
values which all have the same size. I'd like to take their element-wise average. By this I mean that the average of
{1, 2, 3}, {-1, -2, -3}, and {3, 3, 3}
应为 {1、1 ,1}
。同样有趣的是如何对这些元素进行求和,尽管我希望一个解决方案将是另一个解决方案。
should be {1, 1, 1}
. Also of interest is how to sum these element-wise, although I expect that any solution for one will be a solution for the other.
(NB:数组是固定在单个表中的,但是在表之间可能会有所不同。因此,我需要一个没有一定长度的解决方案。)
(NB: The length of the arrays is fixed within a single table, but may vary between tables. So I need a solution which doesn't assume a certain length.)
我最初的猜测是应该以某种方式使用 unnest
,因为 unnest
应用于数字[]
列展平所有数组。因此,我想认为有一种很好的方法将其与某种窗口功能+ group by
一起使用,以挑选出每个数组的各个分量并将其求和。
My initial guess is that I should be using unnest
somehow, since unnest
applied to a numeric[]
column flattens out all the arrays. So I'd like to think that there's a nice way to use this with some sort of windowing function + group by
to pick out the individual components of each array and sum them.
-- EXAMPLE DATA
CREATE TABLE A
(vector numeric[])
;
INSERT INTO A
VALUES
('{1, 2, 3}'::numeric[])
,('{-1, -2, -3}'::numeric[])
,('{3, 3, 3}'::numeric[])
;
推荐答案
我自己发现了一个解决方案,可能是我将使用一个。
I discovered a solution on my own which is probably the one I will use.
首先,我们可以定义一个用于添加两个向量的函数:
First, we can define a function for adding two vectors:
CREATE OR REPLACE FUNCTION vec_add(arr1 numeric[], arr2 numeric[])
RETURNS numeric[] AS
$$
SELECT array_agg(result)
FROM (SELECT tuple.val1 + tuple.val2 AS result
FROM (SELECT UNNEST($1) AS val1
,UNNEST($2) AS val2
,generate_subscripts($1, 1) AS ix) tuple
ORDER BY ix) inn;
$$ LANGUAGE SQL IMMUTABLE STRICT;
和一个乘以常数的函数:
and a function for multiplying by a constant:
CREATE OR REPLACE FUNCTION vec_mult(arr numeric[], mul numeric)
RETURNS numeric[] AS
$$
SELECT array_agg(result)
FROM (SELECT val * $2 AS result
FROM (SELECT UNNEST($1) AS val
,generate_subscripts($1, 1) as ix) t
ORDER BY ix) inn;
$$ LANGUAGE SQL IMMUTABLE STRICT;
然后我们可以使用PostgreSQL语句 CREATE AGGREGATE
直接创建 vec_sum
函数:
Then we can use the PostgreSQL statement CREATE AGGREGATE
to create the vec_sum
function directly:
CREATE AGGREGATE vec_sum(numeric[]) (
SFUNC = vec_add
,STYPE = numeric[]
);
最后,我们可以找到平均值:
And finally, we can find the average as:
SELECT vec_mult(vec_sum(vector), 1 / count(vector)) FROM A;
这篇关于Postgres中的向量(数组)加法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!