1

Topic: To unite lists on the general elements

Greetings,  who directs to the necessary channel)
There is initial data
a, b
a, c
d, e
c, f
For every line it is necessary to return the list consisting of the general elements:

lst res
--- --------
a, b a, b, c, f <- - because ' a ' it is connected with ' c ' (the second line), ' c ' it is connected with ' f ' (the last line)
a, c a, b, c, f <- - it is similar, ' a ' it is connected with ' b ' (the first line), ' c ' it is connected with ' f ' (the last line)
d, e d, e <- - neither ' d ', nor ' e ' to anybody are not connected
c, f a, b, c, f <- - it is similar 1 and 2: ' c ' it is connected ' a ' (the second line), ' a ' it is connected with ' b ' (the first line).

In a reality kol-in lines it is measured one hundred thousand, kol-in elements in line from 1 to 300 (elements in line are unique).
Initial data -

with ds as
(
select ' a, b ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' from dual
)
select * from ds

2

Re: To unite lists on the general elements

Lexi-227;
[spoiler connected components: quick find quick union algorithm]

declare
type char_array is table of varchar2 (1) index by varchar2 (1);
type arr_elems is table of sys.ku $ _ vcnt index by varchar2 (1);
root char_array;
root_elems arr_elems;
n varchar2 (1);
l integer: = dbms_utility.get_time ();
procedure print (v in varchar2) is
begin
dbms_output.put_line (to_char ((dbms_utility.get_time-l)/100, ' 0999.99 ') || ' ' || v);
l: = dbms_utility.get_time ();
end;
function get_root (n varchar2) return varchar2 is
begin
if root.exists (n) then
return root (n);
else
return null;
end if;
end;
procedure update_root (old_root varchar2, new_root varchar2) is
i pls_integer;
begin
if old_root! =new_root then
root_elems (new_root): =root_elems (new_root) multiset union all root_elems (old_root);
for i in 1. root_elems (old_root).count
loop
root (root_elems (old_root) (i)): =new_root;
end loop;
root_elems (old_root).delete;
end if;
end;
procedure add_elem (p_root varchar2, p_elem varchar2) is
begin
if not root_elems.exists (p_root) then
root_elems (p_root): =sys.ku $ _ vcnt (p_elem);
else
root_elems (p_root).extend ();
root_elems (p_root) (root_elems (p_root).count): =p_elem;
end if;
end;
procedure add_link (p varchar2, q varchar2) is
r1 varchar2 (1);
r2 varchar2 (1);
new_root varchar2 (1);
begin
r1: = get_root (p);
r2: = get_root (q);
if r1 is null or r2 is null then
new_root: = coalesce (r1, r2, p);
if r1 is null then add_elem (new_root, p); root (p): =new_root; end if;
if r2 is null then add_elem (new_root, q); root (q): =new_root; end if;
else
new_root: = least (r1, r2);
root (p): =new_root;
root (q): =new_root;
update_root (greatest (r1, r2), new_root);
end if;
end;
begin
print (' start ');
for r in (
==> with ds as
(
==> select ' a, b, x, y, z ' as lst from dual union all
==> select ' a, c ' from dual union all
==> select ' d, e ' from dual union all
==> select ' c, f ' from dual
)
select substr (lst, 1, instr (lst, ', ')-1) p
,regexp_substr (lst, ' ([^,]) ', 1, n+1) q
from ds
,(select level n from dual connect by level <=300) g
where g.n <=regexp_count (lst, ' [^,] ')-1
)
loop
add_link (r.p, r.q);
end loop;
print (' processed ');
print (' groups: ');
n: = root_elems.first ();
while n is not null loop
for i in 1. root_elems (n).count loop
dbms_output.put (', ' || root_elems (n) (i));
end loop;
dbms_output.put_line (' ');
n: = root_elems.next (n);
end loop;
end;

[/spoiler]
In the selected there should be your request

3

Re: To unite lists on the general elements

xtender;
Thanks big!
And on pure SQL, interesting, it probably to solve?

4

Re: To unite lists on the general elements

Lexi-227 wrote:

And on pure SQL, interesting, it probably to solve?

join of groups which have intersected elements (the Friday task?)
Implementation of sets
the Amount  communications in many to many
Nejro of communication
Grouping of type "or" - I can not invent as to implement

5

Re: To unite lists on the general elements

Lexi-227;
Here outlined on knees

with ds as
(
select ' a, b ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' lst from dual
)
select Root, listagg (Result _, ' / ') within group (order by Result _) Re
from (
select distinct connect_by_root lst Root, lst Result_
from (
select regexp_substr (lst, ' [^,] + ', 1, level) Symb
,lag (regexp_substr (lst, ' [^,] + ', 1, level), 1, substr (lst, instr (lst, ', ',-1) +1)) over (partition by lst order by level) Symb2
,lst
from ds
connect by level <= length (regexp_replace (lst, ' [^,] + ')) +1
and prior lst = lst
and prior dbms_random.value IS NOT NULL
) T1
where Symb! = Symb2
connect by NOCYCLE Symb = prior Symb2 and lst! = prior lst
) T2
group by Root

Only laziness  Re for  values
Likely it is not optimal, but quickly it turned out smile))

6

Re: To unite lists on the general elements

MaximaXXL;

Lexi-227 wrote:

In a reality kol-in lines it is measured one hundred thousand, kol-in elements in line from 1 to 300

and on such kol-ve?

7

Re: To unite lists on the general elements

xtender;
To 300 did not finish... 3 and 4 test silt, and here at 1 character - saw loss sad((. Thanks.
Here corrected:

with ds as
(
select ' a, b ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' lst from dual
)
select Root, listagg (Result _, ' / ') within group (order by Result _) Re
from (
select distinct connect_by_root lst Root, lst Result_
from (
select regexp_substr (lst, ' [^,] + ', 1, level) Symb
,lag (regexp_substr (lst, ' [^,] + ', 1, level), 1, substr (lst, instr (lst, ', ',-1) +1)) over (partition by lst order by level) Symb2
,lst
from ds
connect by level <= length (regexp_replace (lst, ' [^,] + ')) +1
and prior lst = lst
and prior dbms_random.value IS NOT NULL
) T1
--where Symb! = Symb2
connect by NOCYCLE Symb = prior Symb2 and lst! = prior lst
) T2
group by Root

8

Re: To unite lists on the general elements

9

Re: To unite lists on the general elements

Lexi-227;
The optimized version:
1) Selected case in new_root - an important piece for acceleration of operation with the big groups.
2) the Request transferred above that with logic not to hinder.
[spoiler]

declare
type char_array is table of varchar2 (1) index by varchar2 (1);
type arr_elems is table of sys.ku $ _ vcnt index by varchar2 (1);
root char_array;
root_elems arr_elems;
n varchar2 (1);
cursor cur is
with ds as
(
select ' a, b, x, y, z ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' from dual
)
select substr (lst, 1, instr (lst, ', ')-1) p
,regexp_substr (lst, ' ([^,]) ', 1, n+1) q
from ds
,(select level n from dual connect by level <=300) g
where g.n <=regexp_count (lst, ' [^,] ')-1;
l integer: = dbms_utility.get_time ();
procedure print (v in varchar2) is
begin
dbms_output.put_line (to_char ((dbms_utility.get_time-l)/100, ' 0999.99 ') || ' ' || v);
l: = dbms_utility.get_time ();
end;
function get_root (n varchar2) return varchar2 is
begin
if root.exists (n) then
return root (n);
else
return null;
end if;
end;
procedure update_root (old_root varchar2, new_root varchar2) is
i pls_integer;
begin
if old_root! =new_root then
root_elems (new_root): =root_elems (new_root) multiset union all root_elems (old_root);
for i in 1. root_elems (old_root).count
loop
root (root_elems (old_root) (i)): =new_root;
end loop;
root_elems (old_root).delete;
end if;
end;
procedure add_elem (p_root varchar2, p_elem varchar2) is
begin
if not root_elems.exists (p_root) then
root_elems (p_root): =sys.ku $ _ vcnt (p_elem);
else
root_elems (p_root).extend ();
root_elems (p_root) (root_elems (p_root).count): =p_elem;
end if;
end;
procedure add_link (p varchar2, q varchar2) is
r1 varchar2 (1);
r2 varchar2 (1);
new_root varchar2 (1);
old_root varchar2 (1);
begin
r1: = get_root (p);
r2: = get_root (q);
if r1 is null or r2 is null then
new_root: = coalesce (r1, r2, p);
if r1 is null then add_elem (new_root, p); root (p): =new_root; end if;
if r2 is null then add_elem (new_root, q); root (q): =new_root; end if;
else
==> case when root_elems (r1).count <root_elems (r1).count
then new_root: = r1;
old_root: = r2;
else new_root: = r2;
old_root: = r1;
end case;
root (p): =new_root;
root (q): =new_root;
update_root (old_root, new_root);
end if;
end;
begin
print (' start ');
for r in cur
loop
add_link (r.p, r.q);
end loop;
print (' processed ');
print (' groups: ');
n: = root_elems.first ();
while n is not null loop
for i in 1. root_elems (n).count loop
dbms_output.put (', ' || root_elems (n) (i));
end loop;
dbms_output.put_line (' ');
n: = root_elems.next (n);
end loop;
end;

[/spoiler]