CODEWITHSUNDEEP
postgresqlaggregate-functions
relet
relet

Reputation: 7009

Avoid to nest aggregation functions in PostgreSQL 8.3.4

Assuming that my subquery yields a number of rows with the columns (x,y), I would like to calculate the value avg(abs(x-mean)/y). where mean effectively is avg(x).

select avg(abs(x-avg(x))/y) as incline from subquery fails because I cannot nest aggregation functions. Nor can I think of a way to calculate the mean in a subquery while keeping the original result set. An avgdev function as it exists in other dialects would not exactly help me, so here I am stuck. Probably just due to lack of sql knowledge - calculating the value from the result set in postprocessing is easy.

Which SQL construct could help me?

Edit: Server version is 8.3.4. No window functions with WITH or OVER available here.

Upvotes: 1

Views: 579

Answers (3)

user533832
user533832

Reputation:

if your data sets are not too large you could accumulate them into an array and then return the incline from a function:

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

sample view for testing:

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

Upvotes: 1

relet
relet

Reputation: 7009

One option I found is to use a temporary table:

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;

But is that overkill?

Upvotes: 0

user330315
user330315

Reputation:

Not sure I understand you correctly, but you might be looking for something like this:

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

If you do not need to group your results to get the correct avg(x) then simply leave out the "partition by" using an empty over: over()

Upvotes: 1

Related Questions