CODEWITHSUNDEEP
sqlarrayspostgresql
wishihadabettername
wishihadabettername

Reputation: 14751

Apply aggregate functions on array fields in Postgres?

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.

Upvotes: 5

Views: 7445

Answers (2)

N8allan
N8allan

Reputation: 2268

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

Upvotes: 1

roman
roman

Reputation: 117561

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

Upvotes: 8

Related Questions