postgres中跨多列的聚合函数 [英] Aggregate functions across multiple columns in postgres

问题描述

我有一个包含多个包含整数(a1、a2、a3 等)字段的 postgresql 表.

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

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

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).

我可以得到一组整数

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?

推荐答案

通过子查询，您可以处理所有行:

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;

这篇关于postgres中跨多列的聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！