vol7ron vol7ron - 2 days ago 7
SQL Question

Create two arrays for two fields, keeping sort order of arrays in sync (without subquery)

There is no rhyme or reason for this question other than I was curious about how one would go about doing this.

Platform: while I was hoping for a SQL-Standard solution, my main concentration is with PostgreSQL 8.4+. (I know 9.0+ has some array sorting functions.)

SELECT id, group, dt
FROM foo
ORDER BY id;



id | group | dt
-------+-------+-----------
1 | foo | 2012-01-01
1 | bar | 2012-01-03
1 | baz | 2012-01-02
2 | foo | 2012-01-01
3 | bar | 2012-01-01
4 | bar | 2012-01-01
4 | baz | 2012-01-01



I know the following query is wrong, but the result is similar to what I'm after; a way to tie the two fields (sorting of
group
should also sort
dt
):

SELECT id, sort_array(array_agg(group)), array_agg(dt)
FROM foo
GROUP BY id;



id | group | dt
-------+----------------+------------------------------------
1 | {bar,baz,foo} | {2012-01-03,2012-01-02,2012-01-01}
2 | {foo} | {2012-01-01}
3 | {bar} | {2012-01-01}
4 | {bar,baz} | {2012-01-01,2012-01-01}



Is there an easy way to tie the fields for sorting, w/o using a subquery? Perhaps build an array of arrays and then unnest?

Answer

I changed your column name group to grp because group is a reserved word in Postgres and every SQL standard and shouldn't be used as identifier.

I understand your question like this:

Get the two arrays sorted in identical sort order so that the same element position corresponds to the same row in both arrays.

Use a subquery or CTE and order the rows before you aggregate.

SELECT id, array_agg(grp) AS grp, array_agg(dt) AS dt
FROM  (
    SELECT *
    FROM   tbl
    ORDER  BY id, grp, dt
    ) x
GROUP  BY id;

That's faster than to use individual ORDER BY clauses in the aggregate function array_agg() like @Mosty demonstrates (and which has been there since PostgreSQL 9.0). Mosty also interprets your question differently and uses the proper tools for his interpretation.

Is ORDER BY in a subquery safe?

The manual:

The aggregate functions array_agg, json_agg, [...] as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

Beware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed.

So yes, it's safe in the example.

Without subquery

If you really need a solution without subquery, you can:

SELECT id
     , array_agg(grp ORDER BY grp)
     , array_agg(dt  ORDER BY grp, dt)
FROM   tbl
GROUP  BY id;

Note the ORDER BY grp, dt. I sort by dt in addition to break ties and make the sort order unambiguous. Not necessary for grp, though.

There is also a completely different way to do this, with window functions:

SELECT DISTINCT ON (id)
       id
     , array_agg(grp) OVER w AS grp
     , array_agg(dt)  OVER w AS dt
FROM   tbl
WINDOW w AS (PARTITION BY id ORDER BY grp, dt
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER  BY id;

Note the DISTINCT ON (id) instead of just DISTINCT which produces the same result but performs faster by an order of magnitude because we do not need an extra sort.

I ran some tests and this is almost as fast as the other two solutions. As expected, the subquery version was still fastest. Test with EXPLAIN ANALYZE to see for yourself.

Comments