Генерация массива-агг (…) в postgres

user9175828 спросил: 07 октября 2018 в 12:10 в: sql

Используя Postgres 10, у меня есть таблицы, представляющие "единицы", "группу", к которой принадлежит каждая единица, и "расстояние" между каждой парой единиц.

Теперь я хочу, чтобы группа была таблица групповых расстояний, которая агрегирует все расстояния между единицами для каждой пары групп, то есть совокупный массив расстояний от всех единиц в одной группе до всех единиц в другой группе.

Что У меня есть

  '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
...  | ...          ...         | ...          |  ...

Что я хочу

'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}
...            | ...          | {..., ...

Я не уверен, как вернуть все такие массивы для всех пар групп-групп и создайте таблицу, изображенную выше. В настоящее время я могу получить эти массивы только путем определения сайтов по отдельности (как х и у ниже).

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;

Спасибо


0 ответов