Using Postgres 10 I have tables representing 'units', the 'group' to which each unit belongs, and the 'distance' between each pair of units.
I now want a group-to-group distance table that aggregates all the unit-to-unit distances for each pair of groups - i.e. an aggregate array of the distances from all the units in one group to all the units in the other group.
What I have
'units' 'unit_distances'
id | group this_unit_id | that_unit_id | distance
---------- --------------------------------------
1 | 1 1 | 2 | d_12
2 | 1 1 | 3 | d_13
3 | 2 1 | 4 | d_14
4 | 3 2 | 3 | d_23
... | ... ... | ... | ...
What I want
'group_distances'
this_group_id | that_group_id | all_distances
---------------------------------------------------
1 | 2 | {d_13, d_23}
1 | 3 | {d_14, d_24}
2 | 3 | {d_34}
... | ... | {..., ...
I'm not sure how to return all such arrays for all group-group pairings and generate the table depicted above. At the moment I can only get these arrays by defining the sites individually (as x and y below).
with dist as (
select distance
from unit_distances
inner join units
on unit_distances.this_unit_id = units.id
WHERE units.group = x
intersect
select distance
from unit_distances
inner join units
on unit_distances.that_unit_id = units.id
WHERE units.group = y)
select array_agg(distance) from dist;
Thanks
If I understand correctly, this is just two joins to get the groups and an aggregation for the distances:
select u1.group, u2.group, array_agg(ud.distance) as all_distances
from unit_distances ud join
units u1
on ud.this_unit_id = u1.id join
units u2
on ud.that_unit_id = u2.id
group by u1.group, u2.group;