SQLize Online / PHPize Online  /  SQLtest Online

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_