在PostgreSQL中跨多列聚合函数

Question

我有一个包含多个整数字段（a1，a2，a3等）的postgresql表。

我想同时在多个列上运行聚合函数（平均值、标准偏差等）。 （其中一些可能有相当数量的空值，因此我不想仅生成列平均值然后平均这些平均值）。

我可以使用以下代码获得整数集：

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

但我无法将此作为输入传递给聚合函数。

有人可以给我一些提示，如何使其正常工作吗？

- sparky

Answer 1

使用子查询，您可以利用所有行：

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

你也可以对行进行分组，例如：

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

- klin

---

非常准确 - 我漏掉了_alias_ - 这就是为什么我一直收到语法错误的原因... - sparky

Answer 2

你可以为数组变量定义自己的聚合函数：

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)

你可以用类似的步骤处理其他聚合函数，但是实现平均数稍微有点难度，而中位数则更高级一些。但是所有这些都是可能的，详见http://www.postgresql.org/docs/9.3/static/xaggr.html

- Pavel Stehule

---

嗯，感谢您的帮助，但我不确定我想要重新实现所有聚合函数！似乎有点奇怪，我不能将来自原始查询的setof传递给现有的函数。 - sparky