Aggregate Avg() over array fields in PostgreSQL

Question

Before this question gets flagged and closed, I saw this question already, yet it does not quite answer my problem.

I would like to calculate the element-wise average over the arrays in the field per row, and keep the dimensionsionality

create table if not exists my_arrays (array_field float[]);

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

select avg(array_field) as x from my_arrays;

Which should output:

    x
---------
{2, 2, 2}

Is this possible?

The error is get is that no avg(float[]) exists ...

Clebo Sevic
– Clebo Sevic

2021-11-27 17:44:29 +00:00
Commented Nov 27, 2021 at 17:44 — Clebo Sevic
– Clebo Sevic, Commented Nov 27, 2021 at 17:44

jjanes · Accepted Answer · 2021-11-27 18:08:03Z

1

You can use unnest...with ordinality to break them apart while labeling their positions, then group by position, then reassemble with array_agg.

select array_agg(avg order by ordinality) from (
    select ordinality, avg(unnest) from my_arrays, unnest(array_field) with ordinality group by ordinality
) foo;

You need to make sure it deals with NULLs and missing values in a way you find suitable.

If you will be doing this a lot, it might make sense to define your own aggregate that operates over float[].

answered Nov 27, 2021 at 18:08

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Aggregate Avg() over array fields in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related