5

Is it possible to apply aggregates (like avg(), stddev()) on all the values in an integer[] field (or other arrays of numbers)?

CREATE TABLE widget
(
  measurement integer[]
);

insert into widget (measurement) values ( '{1, 2, 3}');

select avg(measurement::integer[]) from widget;

ERROR:  function avg(integer[]) does not exist
LINE 4: select avg(measurement::integer[]) from widget;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function avg(integer[]) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 71

I can work around by splitting the array into multiple rows like

select avg(m)::float from (select unnest(measurement) m from widget) q;

but it's less elegant.

Thank you.

1
  • You can define a custom aggregate, that will work with an array. Or create a simple function that will convert an array to a single aggregated value and aggregate on top of this function. Commented Sep 23, 2013 at 15:12

2 Answers 2

8

you can create simple function like this:

create function array_avg(_data anyarray)
returns numeric
as
$$
    select avg(a)
    from unnest(_data) as a
$$ language sql;

and query it like this

select avg(array_avg(measurement))
from widget;

or you can simply do

select avg((select avg(a) from unnest(measurement) as a))
from widget;

sql fiddle demo

0
1

In case anyone wants to know how to do this while keeping other attributes, without grouping, lateral joins provide a simple solution:

select Point, High, Low, Average
    from GridPoint G
    join lateral (
        select Max(V) High, Min(V) Low, Avg(V) Average
            from Unnest(G.Values) V
    ) _ on true

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.