Как объединить ранее сгруппированные строки одной или несколькими общими строками?
В моей базе данных PostgreSQL у меня есть два отдельных запроса (q1, q2), которые объединяются между несколькими таблицами и назначают одни и те же элементы разным группам (я называю эти подгруппы) на основе разных критериев. Я получаю результат запроса 1 и 2 (qr1, qr2).
Элемент может появиться в одном или обоих, но в результате он уникален. Я хочу назначить новый идентификатор группы на основе обеих подгрупп и назначить один и тот же идентификатор группы, если подгруппы совместно используют один или несколько элементов.
qr1 qr2
+----------+-------------+ +----------+-------------+
| item | subgroup1 | | item | subgroup2 |
+----------+-------------+ +----------+-------------+
| 1 | 1 | | 1 | 5 |
| 2 | 1 | | 5 | 6 |
| 3 | 2 | | 6 | 6 |
| 4 | 3 | | 7 | 7 |
| 5 | 3 | | 8 | 5 |
| 6 | 4 | | 10 | 5 |
+----------+-------------+ +----------+-------------+
combined (interested in item and group):
+---------+------------+------------+-------+
| item | subgroup1 | subgroup2 | group |
+---------+------------+------------+-------+
| 1 | 1 | 5 | 1 |
| 2 | 1 | N | 1 |
| 3 | 2 | N | 2 |
| 4 | 3 | N | 3 |
| 5 | 3 | 6 | 3 |
| 6 | 4 | 6 | 3 |
| 7 | N | 7 | 4 |
| 8 | N | 5 | 1 |
| 10 | N | 5 | 1 |
+---------+------------+------------+-------+
- Подгруппы 1 и 5 будут объединены из-за общего элемента 1. (элементы группы 1, 2, 8, 10)
- Подгруппа 3, 4! и 6 будут объединены из-за общего элемента 5. (сгруппировать элементы 4, 5, 6)
- Подгруппы 2 и 7 составляют свою собственную группу и не делят элемент ни с какой другой группой.
Я пытался использовать оконные функции, считать по дублирующемуся элементу и идти оттуда. Но я застрял.
Решение, скорее всего, будет включать рекурсивное поведение. См.: WITH RECURSIVE ...;
Ответы 1
Как упоминалось в комментариях к запросу, вам нужен рекурсивный запрос. Рекурсивная часть — это то, что я называю cte в своем запросе. Существует массив с именем items, который я использую, чтобы избежать циклов.
Идея заключается в том, что для каждого элемента я назначаю все остальные элементы, которые прямо или косвенно связаны с одной и той же группой. Затем я агрегирую по элементам и беру наименьший связанный элемент и, таким образом, обнаруживаю все элементы, принадлежащие к одной и той же группе. Я использую DENSE_RANK, чтобы получить последовательные номера групп.
with recursive
qr1(item, subgroup) as (values (1,1), (2,1), (3,2), (4,3), (5,3), (6,4)),
qr2(item, subgroup) as (values (1,5), (5,6), (6,6), (7,7), (8,5), (10,5)),
qr(item, subgroup) as (select * from qr1 union all select * from qr2),
cte(item, other, items) as
(
select item, item, array[item]
from qr
union all
select cte.item, g.item, cte.items || g.item
from cte
join qr on qr.item = cte.other
join qr g on g.subgroup = qr.subgroup
where g.item <> all (cte.items)
)
select
item,
min(qr1.subgroup) as sg1,
min(qr2.subgroup) as sg2,
dense_rank() over (order by min(other)) as grp
from cte
left join qr1 using (item)
left join qr2 using (item)
group by item
order by item;
Демо: https://dbfiddle.uk/fG6AnX6l
Подгруппа 4 не связана с элементом 5. Она связана с элементом 6, а поскольку подгруппа 6 связана как с элементами 5, так и с элементами 6, вы хотите включить подгруппу 4 с элементами 3 и 6? Это включено косвенной ассоциацией? Я очень сомневаюсь, что один только SQL может достичь этого, и «простой способ» не описывает усилия. Если у вас есть конкретный код с проблемой, опубликуйте вопрос.