Question 247037 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Aggregate functions over array columns

Given a table with a column containing double precision[] with a fixed/same size.
I am able to fetch an element wise aggregation for min and max:

select min(array_col) from table;
select max(array_col) from table;

The result should be again a double precision[] of the same size. If I want to use avg() or stddev() I get an error message like:

function stddev(double precision[]) does not exists

The same happens for avg(). Is there any work around?

Tags:

2

Answers


  1. 0

    Use unnest() to open up the array and work on its elements: demo

    select avg(el) from (select unnest(array_col) el from test) a;
select stddev(el) from (select unnest(array_col) el from test) a;

    This will take an average of all elements of all arrays. To get a separate average for each array, differentiate them by primary key (or add some unique id using row_number()over()) and use a group by:

    select id,
       avg(el),
       stddev(el)
from (select row_number()over() as id, 
             unnest(array_col) el 
      from test) a 
group by id;

    Be aware that min() used the way you did doesn’t pick the least element in each array, or from all arrays combined, or the array with the least sum of values but rather selects the first array according to default array order – in this case it’s empty first, value of subsequent elements ascending, null last. Similar applies to max(). To get the least/greatest element or sum of elements from all arrays, use min()/max() like in the first example, or the second to get one from each array.

    Element wise part

    If you don’t mind addressing by hand:

    select ARRAY[min(array_col[1]), min(array_col[2])] from test;

    But you can also leave listing the subscripts to a generate_series() and array_upper(): demo

    select array_agg(el_min) 
from (select n,min(array_col[ n ]) el_min 
      from test, lateral generate_series(1,array_upper(array_col,1)) as g(n)
      group by n 
      order by n
   ) a;
    Login or Signup to reply.
  2. 0

    You can use unnest to unpack the array, then apply the aggregate function. Assuming all arrays have three elements, that could be

    SELECT ARRAY[
          stddev(u.e) FILTER (WHERE n = 1),
          stddev(u.e) FILTER (WHERE n = 2),
          stddev(u.e) FILTER (WHERE n = 3)
       ]
FROM tab
   CROSS JOIN LATERAL unnest(tab.array_col) WITH ORDINALITY AS u(e,n);

    A better data model would not store this as an array, but as three columns.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search