在postgres中跨多个列聚合函数

I have a postgresql table with multiple fields containing integers (a1,a2,a3 etc).

我有一个postgresql表,其中包含多个包含整数的字段(a1,a2,a3等)。

I want to run aggregate functions(mean, standard deviation etc) across more than one of the columns at once. (Some of them may have a reasonable number of nulls, so I don't want to just generate column averages and then average those).

我想一次在多个列上运行聚合函数(均值,标准差等)。 (其中一些可能有合理数量的空值,所以我不想只生成列平均值然后平均值)。

I can get a set of integers with

我可以得到一组整数

SELECT unnest(array[a1,a2,a3]) as values FROM table

but I then can't get the aggregate functions to take this as input.

但是我无法获得聚合函数来将其作为输入。

Can anyone give me any hints on how I could get this to work?

任何人都可以给我任何关于如何让它工作的提示吗?

2 个解决方案

#1

With a subquery you have all rows at your disposal:

使用子查询,您可以使用所有行:

SELECT sum(val) FROM (
    SELECT unnest(array[a1,a2,a3]) as val FROM table) alias;

You can also group your rows, for example:

您还可以对行进行分组,例如:

SELECT field, sum(val) FROM (
    SELECT field, unnest(array[a1,a2,a3]) as val FROM table) alias
GROUP BY field;

#2

you can define own aggregates for array variable:

您可以为数组变量定义自己的聚合:

CREATE OR REPLACE FUNCTION public.sum_sfunc(double precision, double precision[])
 RETURNS double precision LANGUAGE sql
AS $function$
  SELECT coalesce($1,0) + sum(v) FROM unnest($2) g(v)
$function$

CREATE AGGREGATE sum(BASETYPE=double precision[], 
                      SFUNC=sum_sfunc, STYPE=double precision);

postgres=# select * from fo;
 a  │ b  
────┼────
 10 │ 20
 30 │ 40
(2 rows)

postgres=# select sum(array[a,b]) from fo;
 sum 
─────
 100
(1 row)

Similar steps you can do with other aggregates, but implementation of AVG is little bit harder, and median is next level. But all is possible, see http://www.postgresql.org/docs/9.3/static/xaggr.html

您可以使用其他聚合执行类似的步骤,但AVG的实现稍微困难一些,并且中位数是下一级别。但一切皆有可能,请参阅http://www.postgresql.org/docs/9.3/static/xaggr.html

#1