postgresql: самостоятельное соединение с массивом
Мой вопрос о формировании SQL-запроса Postgres для приведенного ниже варианта использования
Подход #1
У меня есть таблица как ниже, где я генерирую один и тот же uuid для разных типов (a, b, c, d), как отображение разных типов.
+----+------+-------------+
| id | type | master_guid |
+----+------+-------------+
| 1 | a | uuid-1 |
| 2 | a | uuid-2 |
| 3 | a | uuid-3 |
| 4 | a | uuid-4 |
| 5 | a | uuid-5 |
| 6 | b | uuid-1 |
| 7 | b | uuid-2 |
| 8 | b | uuid-3 |
| 9 | b | uuid-6 |
| 10 | c | uuid-1 |
| 11 | c | uuid-2 |
| 12 | c | uuid-3 |
| 13 | c | uuid-6 |
| 14 | c | uuid-7 |
| 15 | d | uuid-6 |
| 16 | d | uuid-2 |
+----+------+-------------+
Подход № 2
Я создал две таблицы для идентификатора для ввода, а затем идентификатор для master_guid, как показано ниже
Таблица 1:
+----+------+
| id | type |
+----+------+
| 1 | a |
| 2 | a |
| 3 | a |
| 4 | a |
| 5 | a |
| 6 | b |
| 7 | b |
| 8 | b |
| 9 | b |
| 10 | c |
| 11 | c |
| 12 | c |
| 13 | c |
| 14 | c |
| 15 | d |
| 16 | d |
+----+------+
Таблица 2
+----+-------------+
| id | master_guid |
+----+-------------+
| 1 | uuid-1 |
| 2 | uuid-2 |
| 3 | uuid-3 |
| 4 | uuid-4 |
| 5 | uuid-5 |
| 6 | uuid-1 |
| 7 | uuid-2 |
| 8 | uuid-3 |
| 9 | uuid-6 |
| 10 | uuid-1 |
| 11 | uuid-2 |
| 12 | uuid-3 |
| 13 | uuid-6 |
| 14 | uuid-7 |
| 15 | uuid-6 |
| 16 | uuid-2 |
+----+-------------+
Я хочу получить вывод, как показано ниже, с обоими подходами:
+----+------+--------+------------+
| id | type | uuid | mapped_ids |
+----+------+--------+------------+
| 1 | a | uuid-1 | [6,10] |
| 2 | a | uuid-2 | [7,11] |
| 3 | a | uuid-3 | [8,12] |
| 4 | a | uuid-4 | null |
| 5 | a | uuid-5 | null |
+----+------+--------+------------+
Я пробовал самостоятельное соединение с array_agg по идентификаторам и группировкой на основе uuid, но не смог получить желаемый результат.
Используйте запрос ниже для заполнения данных:
Подход #1
insert into table1 values
(1,'a','uuid-1'),
(2,'a','uuid-2'),
(3,'a','uuid-3'),
(4,'a','uuid-4'),
(5,'a','uuid-5'),
(6,'b','uuid-1'),
(7,'b','uuid-2'),
(8,'b','uuid-3'),
(9,'b','uuid-6'),
(10,'c','uuid-1'),
(11,'c','uuid-2'),
(12,'c','uuid-3'),
(13,'c','uuid-6'),
(14,'c','uuid-7'),
(15,'d','uuid-6'),
(16,'d','uuid-2')
Подход № 2
insert into table1 values
(1,'a'),
(2,'a'),
(3,'a'),
(4,'a'),
(5,'a'),
(6,'b'),
(7,'b'),
(8,'b'),
(9,'b'),
(10,'c'),
(11,'c'),
(12,'c'),
(13,'c'),
(14,'c'),
(15,'d'),
(16,'d')
insert into table2 values
(1,'uuid-1'),
(2,'uuid-2'),
(3,'uuid-3'),
(4,'uuid-4'),
(5,'uuid-5'),
(6,'uuid-1'),
(7,'uuid-2'),
(8,'uuid-3'),
(9,'uuid-6'),
(10,'uuid-1'),
(11,'uuid-2'),
(12,'uuid-3'),
(13,'uuid-6'),
(14,'uuid-7'),
(15,'uuid-6'),
(16,'uuid-2')
2 ответа
Попробуй это:
select
t1.id, t1.type, t1.master_guid, array_agg (distinct t2.id)
from
table1 t1
left join table1 t2 on
t1.master_guid = t2.master_guid and
t1.id != t2.id
group by
t1.id, t1.type, t1.master_guid
Я не прихожу к тем же результатам, которые вы перечислили, но я подумал, что было достаточно близко, что, возможно, с вашей стороны было ошибочное ожидание или только небольшая ошибка... в любом случае, потенциальная отправная точка.
-- РЕДАКТИРОВАТЬ --
Для подхода № 2, я думаю, вам просто нужно добавить внутреннее соединение к Table2, чтобы получить GUID:
select
t1.id, t1.type, t2.master_guid,
array_agg (t2a.id)
from
table1 t1
join table2 t2 on t1.id = t2.id
left join table2 t2a on
t2.master_guid = t2a.master_guid and
t2a.id != t1.id
where
t1.type = 'a'
group by
t1.id, t1.type, t2.master_guid
Использование оконной функцииARRAY_AGG позволяет объединить ваши idс групп (в вашем случае группы ваши uuids)
SELECT
id, type, master_guid as uuid,
array_agg(id) OVER (PARTITION BY master_guid) as mapped_ids
FROM table1
ORDER BY id
Результат:
| id | type | uuid | mapped_ids |
|----|------|--------|------------|
| 1 | a | uuid-1 | 10,6,1 |
| 2 | a | uuid-2 | 16,2,7,11 |
| 3 | a | uuid-3 | 8,3,12 |
| 4 | a | uuid-4 | 4 |
| 5 | a | uuid-5 | 5 |
| 6 | b | uuid-1 | 10,6,1 |
| 7 | b | uuid-2 | 16,2,7,11 |
| 8 | b | uuid-3 | 8,3,12 |
| 9 | b | uuid-6 | 15,13,9 |
| 10 | c | uuid-1 | 10,6,1 |
| 11 | c | uuid-2 | 16,2,7,11 |
| 12 | c | uuid-3 | 8,3,12 |
| 13 | c | uuid-6 | 15,13,9 |
| 14 | c | uuid-7 | 14 |
| 15 | d | uuid-6 | 15,13,9 |
| 16 | d | uuid-2 | 16,2,7,11 |
Эти массивы в настоящее время содержат также идентификатор текущей строки (mapped_ids из id = 1 содержит 1). Это можно исправить, удалив этот элемент с array_remove:
SELECT
id, type, master_guid as uuid,
array_remove(array_agg(id) OVER (PARTITION BY master_guid), id) as mapped_ids
FROM table1
ORDER BY id
Результат:
| id | type | uuid | mapped_ids |
|----|------|--------|------------|
| 1 | a | uuid-1 | 10,6 |
| 2 | a | uuid-2 | 16,7,11 |
| 3 | a | uuid-3 | 8,12 |
| 4 | a | uuid-4 | |
| 5 | a | uuid-5 | |
| 6 | b | uuid-1 | 10,1 |
| 7 | b | uuid-2 | 16,2,11 |
| 8 | b | uuid-3 | 3,12 |
| 9 | b | uuid-6 | 15,13 |
| 10 | c | uuid-1 | 6,1 |
| 11 | c | uuid-2 | 16,2,7 |
| 12 | c | uuid-3 | 8,3 |
| 13 | c | uuid-6 | 15,9 |
| 14 | c | uuid-7 | |
| 15 | d | uuid-6 | 13,9 |
| 16 | d | uuid-2 | 2,7,11 |
Вот например id=4 содержит пустой массив вместо NULL значение. Это может быть достигнуто с помощью NULLIF функция. Это дает NULL если оба параметра равны, иначе он выдает первый параметр.
SELECT
id, type, master_guid as uuid,
NULLIF(
array_remove(array_agg(id) OVER (PARTITION BY master_guid), id),
'{}'::int[]
) as mapped_ids
FROM table1
ORDER BY id
Результат:
| id | type | uuid | mapped_ids |
|----|------|--------|------------|
| 1 | a | uuid-1 | 10,6 |
| 2 | a | uuid-2 | 16,7,11 |
| 3 | a | uuid-3 | 8,12 |
| 4 | a | uuid-4 | (null) |
| 5 | a | uuid-5 | (null) |
| 6 | b | uuid-1 | 10,1 |
| 7 | b | uuid-2 | 16,2,11 |
| 8 | b | uuid-3 | 3,12 |
| 9 | b | uuid-6 | 15,13 |
| 10 | c | uuid-1 | 6,1 |
| 11 | c | uuid-2 | 16,2,7 |
| 12 | c | uuid-3 | 8,3 |
| 13 | c | uuid-6 | 15,9 |
| 14 | c | uuid-7 | (null) |
| 15 | d | uuid-6 | 13,9 |
| 16 | d | uuid-2 | 2,7,11 |