1

Following on from Combining array_agg and unnest, given this data:

key |  a | b | c
------------------
 1  |  0 | 1 | {1,2}
 1  |  1 | 2 | {3}
 1  | -1 | 3 | {2}
 1  |  2 | 4 | {}

Executing this query:

SELECT 
  d.key,
  min(d.a) AS a,
  sum(d.b) AS b,
  array_agg(DISTINCT x.c) AS c 
FROM data AS d
  CROSS JOIN LATERAL unnest(d.c) AS x(c)
GROUP BY d.key

Gives the unexpected result:

key |  a | b | c
------------------
 1  | -1 | 7 | {1,2,3}

What's going on here, and how to get the correct sum?


Answer performance

On my actual data (16642 rows, 1942 keys, 6 aggregates), I get these cost estimates for each suggested solution.

  • The cost estimates are not really an indicator on the query performance. The actual runtime (generated using explain (analyze, buffers) is the interesting thing - but this is the price you pay for not normalizing properly – a_horse_with_no_name Jul 5 at 4:59
  • The execution time for all three is around 300ms. – OrangeDog Jul 5 at 13:56
1

The unnesting generates 2 rows for (key = 1, a = 0, b = 1), and the cross join removes the row with the empty array.

So your group by operates on the following set:

key | a  | b | c
----+----+---+--
  1 |  0 | 1 | 1
  1 |  0 | 1 | 2
  1 |  1 | 2 | 3
  1 | -1 | 3 | 2

One solution is to combine two group by queries, each one grouping n a different level:

select *
from (
  select d1."key", min(d1.a), sum(d1.b)
  from data d1
  group by d1."key"
) m
  join (
    select "key", array_agg(DISTINCT x.c) AS c 
    from data d2
      left join lateral unnest(d2.c) as x(c) on true
    where x.c is not null
    group by "key"
  ) a on a."key" = m."key";

Another approach is to only include the "first row" for each "unnested" group in the aggregates:

select d."key", 
       min(d.a) filter (where idx = 1 or idx is null), 
       sum(d.b) filter (where idx = 1 or idx is null),
       array_agg(distinct x.c)
from data AS d
  left join lateral unnest(d.c) with ordinality  AS x(c,idx) on true
group by d."key";

with ordinality returns the position of the unnested element in the original array. For the row with the empty array, that will be null.

  • I don't suppose there's any way to avoid repeating the filter for every other aggregate? – OrangeDog Jul 4 at 16:05
  • @OrangeDog: only if you use the first version – a_horse_with_no_name Jul 4 at 20:13
0

One problem with your query is that the CROSS JOIN eliminates rows where unnest() produces no rows (happens for the empty array {}).

You could fix that with LEFT JOIN .. ON true, but the other problem is that rows are multiplied where unnest() returns multiple rows (happens for {1,2}).

Thats how you get 7 for the sum: 1 + 1 + 2 + 3.

Joining two separate subqueries might be simplest / fastest:

SELECT *
FROM (
   SELECT key, min(a) AS min_a, sum(b) AS sum_b
   FROM   data
   GROUP  BY 1
   ) x
JOIN  (
   SELECT key, array_agg(DISTINCT e) AS dist_c 
   FROM   data, unnest(c) e
   GROUP  BY 1
   ) y USING (key);

The plain join works because both subqueries have grouped by the same column - and as long as key is NOT NULL.

db<>fiddle here

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.