避免在PostgreSQL 8.3.4中嵌套聚合函数

我不确定我是否正确理解你,但你可能在寻找这样的东西:

SELECT avg(x - mean/y)
FROM (
  SELECT x, 
         y, 
         avg(x) as mean over(partition by your_grouping_column) 
  FROM your_table
) t

如果您不需要对结果进行分组以获得正确的平均值(x),那么只需使用一个空over省略“partition by”: over()

如果数据集不是太大,可以将它们累积到一个数组中,然后从函数返回斜面:

create type typ as (x numeric, y numeric);

create aggregate array_accum( sfunc = array_append,
                              basetype = anyelement,
                              stype = anyarray,
                              initcond = '{}' );

create or replace function unnest(anyarray) returns setof anyelement 
                           language sql immutable strict as $$
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;$$;

create function get_incline(typ[]) returns numeric 
                language sql immutable strict as $$
  select avg(abs(x-(select avg(x) from unnest($1)))/y) from unnest($1);$$;

select get_incline((select array_accum((x,y)::typ) from subquery));

create view subquery as 
select generate_series(1,5) as x, generate_series(1,6) as y;

我发现的一个选项是使用临时表:

begin; 
  create temporary table sub on commit drop as (...subquery code...);
  select avg(abs(x-mean)/y) as incline from (SELECT x, y, (SELECT avg(x) FROM sub) AS mean FROM sub) as sub2;
commit;

但这是不是太过分了?