Postgresql - Map array aggregates into a single array in a particular order

Question

I have a PostgreSQL table containing a column of 1 dimensional array data. I wish to perform an aggregate query on this column, obtaining min/max/mean for each element of the array as well as the group count, returning the result as a 1 dimensional array. The array lengths in the table may vary, but I can be certain that in any grouping I perform, all arrays will be of the same length.

In a simple form, say my arrays are of length 2 and have readings for x and y, I want to return the result as {Min(x), Max(x), Mean(x), Min(y), Max(y), Mean(y), Count()}

I am able to get a result in the form {Min(x), Min(y), Max(x), Max(y), Mean(x), Mean(y) Count()} but I can't get from there to my desired result.

Here's an example showing where I am so far (this time with arrays of length 3, but without the mean aggregation as there isnt one for arrays built in to pgSql): (SQLFiddle here)

CREATE TABLE my_test(some_key numeric, event_data bigint[]);

INSERT INTO my_test(some_key, event_data) VALUES
(1, {11,12,13}),
(1, {5,6,7}),
(1, {-11,-12,-13});

SELECT MIN(event_data) || MAX(event_data) || COUNT(event_data)  FROM my_test GROUP BY some_key;

The above gives me

{11,12,13,-11,-12,-13,3}

However, I don't know how to transform a result like the above into what I want, which is:

{11,-11,12,-12,13,-13,3}

What function should I use to transform the above?

Note that the aggregation functions above don't exactly match with those I am using to get min, max - I'm using the aggs_for_vecs extension to give me min, max and mean.

Gordon Linoff · Accepted Answer · 2020-02-14 12:06:48Z

1

I would recommend using array operations and aggregation:

select x.some_key,
       array_agg(u.val order by x.n, u.nn)
from (select t.some_key, ed.n, min(val) as minval, max(val) as maxval
      from my_test t cross join lateral
           unnest(t.event_data) with ordinality as ed(val, n)
      group by t.some_key, ed.n
     ) x cross join lateral
     unnest(array[x.minval, x.maxval]) with ordinality u(val, nn)
group by x.some_key;

Personally, I would prefer an array with three elements and the min/max as a record:

select x.some_key, array_agg((x.minval, x.maxval) order by x.n)
from (select t.some_key, ed.n, min(val) as minval, max(val) as maxval
      from my_test t cross join lateral
           unnest(t.event_data) with ordinality as ed(val, n)
      group by t.some_key, ed.n
     ) x 
group by x.some_key;

Here is a db<>fiddle.

answered Feb 14, 2020 at 12:06

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Ian Ferguson Over a year ago

Thanks, there were a few new terms for me like the lateral join, so it took me a while to understand how the query works. Nice that they save me from to install a db extension to perform the aggregation on the arrays. The alternative formatting of the result is a valid suggestion, but the query result must match an agreed format in a rest api. If I may ask for one more refinement to your example, how should I append the row count once at the end of the result array? (in order to get something like {avg(x), min(x), max(x), avg(y), min(y), max(y), count(*)}

Gordon Linoff Over a year ago

@IanFerguson . . . If this works, you might consider accepting the answer. As for the row count, I would suggest that you ask a new question; this one is complicated enough.

Ian Ferguson Over a year ago

Including the count in the result array was part of my original question, so it hasn't quite solved the problem for me. I really appreciate your answer though, and I would upvote it, but as a new user SO doesn't allow me to.

Collectives™ on Stack Overflow

Postgresql - Map array aggregates into a single array in a particular order

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related