with recursive
cte_vals(id, id_array, value_1, value_2) as (
values
(1, array[1, 2, 3], 1, 8),
(2, array[4, 5, 6], 2, 4),
(3, array[7, 8, 1], 4, 7),
(4, array[7, 8, 6], 9, 1),
(5, array[9, 11, 12], 9, 1),
(6, array[9, 13, 14], 9, 1),
(7, array[15, 17], 10, 11)
),
cte_group(checked, id, group_) as (
select '{}'::integer[], '{}'::integer[], 0
union all
select g.checked || e.id, e.id, g.group_ + 1
from
cte_group g
cross join lateral (
with recursive
cte_accumulation (id, id_array) as (
select *
from (
select array[id], id_array
from cte_vals a
where not id = any(checked)
limit 1
) a
union all
select a.id || b.id, (select array_agg(distinct a order by a) from unnest(a.id_array || b.id_array) a)
from
cte_accumulation a
cross join lateral (
select id, id_array
from cte_vals c
where a.id_array && c.id_array and not c.id = any(a.id)
limit 1
) b
)
select *
from cte_accumulation
order by array_length(id, 1) desc
limit 1
) e
)
select array_agg(a.id_array), sum(value_1), sum(value_2)
from
cte_vals a
inner join cte_group b on a.id = any(b.id)
group by b.group_
Telegram: DB developers channel