用基本的数学函数求标准差

I am trying to get the standard deviation from a table containing income values, using the basic math functions below in postgresql.

我正在尝试从包含收入值的表中获取标准偏差，使用postgresql中的基本数学函数。

This is what I tried:

这就是我所尝试的:

SELECT sqrt(sum(power(income - (sum(income) / count(income)), 2)) / (count(*) - 1)) FROM income_data

however, I keep getting the following error:

然而，我一直有以下错误:

ERROR: aggregate function calls cannot be nested

Has anyone run into this issue? I feel like the logic for obtaining the standard deviation should work, although haven't had any luck thus far, I appreciate any suggestions on how to resolve.

有人遇到过这个问题吗?我觉得获得标准差的逻辑应该是可行的，虽然到目前为止还没有任何的运气，但是我很欣赏关于如何解决的建议。

1 个解决方案

#1

You should calculate a mean in a separate query, e.g. in a with statement:

你应该在单独的查询中计算平均值，例如在with语句中:

with mean as (
    select sum(income) / count(income) as mean
    from income_data
)
select sqrt(sum(power(income - mean, 2)) / (count(*) - 1)) 
from income_data
cross join mean;

or in a derived table:

或在派生表格中:

select sqrt(sum(power(income - mean, 2)) / (count(*) - 1)) 
from income_data
cross join (
    select sum(income) / count(income) as mean
    from income_data
) s;

#1