3 Replies Latest reply: Apr 5, 2012 8:13 AM by Mark Kelly-Oracle

# Dynamic K mean clustring

Hello Every one.
Currently i m working in k mean clustering in oracle 10g, i create the static clustered, follow the following procedure.

1) Test cases values are dumpted as table : n0
2) Next n1.sql selects unique random values, generates average and dumps as table : n1
3) Then n2.sql Takes each groups and regroups based on average as table : n2
4) the above steps is repeated (n[3-9].sql) till the values remian in the same group (tables careated as n3, n4, n5, n6, n7, n8,n9).

For example we have 24 value in a table
``````C1

25
28
32
37
38
20
34
39
42
49
30
55
45
47
52
20
18
46
21
40
39
51
37
45
``````
Then i create cluster in the flowing manner
``````create n1.sql
-------------

drop table n1;
create table n1 as
select * from
(with a1 as
(select c1 from n0 order by c1
),
r1 as
(select c1 from (select c1 from a1 order by dbms_random.value) where rownum=1
),
g1 as
(select r1.c1 rn, a1.c1, 'Grp1' grp1, 1 step1
from a1, r1 where a1.c1 between r1.c1-5 and r1.c1+5
),
r2 as
(select c1 from (select c1 from
(select c1 from a1
where c1 not in (select c1 from g1))
order by dbms_random.value) where rownum=1
),
g2 as
(select r2.c1 rn, a11.c1, 'Grp2' grp1, 1 step1
from (select c1 from a1
where c1 not in (select c1 from g1)) a11, r2
where a11.c1 between r2.c1-5 and r2.c1+5
),
r3 as
(select c1 from (select c1 from
(select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2)
) order by dbms_random.value) where rownum=1
),
g3 as
(select r3.c1 rn, a11.c1, 'Grp3' grp1, 1 step1
from (select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2)
) a11, r3
where a11.c1 between r3.c1-5 and r3.c1+5
),
r4 as
(select c1 from (select c1 from
(select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2
union all
select c1 from g3)
) order by dbms_random.value) where rownum=1
),
g4 as
(select r4.c1 rn, a11.c1, 'Grp4' grp1, 1 step1
from (select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2
union all
select c1 from g3)
) a11, r4
where a11.c1 between r4.c1-5 and r4.c1+5
),
r5 as
(select c1 from (select c1 from
(select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2
union all
select c1 from g3
union all
select c1 from g4)
) order by dbms_random.value) where rownum=1
),
g5 as
(select r5.c1 rn, a11.c1, 'Grp5' grp1, 1 step1
from (select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2
union all
select c1 from g3
union all
select c1 from g4)
) a11, r5
where a11.c1 between r5.c1-5 and r5.c1+5
),
r6 as
(select c1 from (select c1 from
(select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2
union all
select c1 from g3
union all
select c1 from g4
union all
select c1 from g5)
) order by dbms_random.value) where rownum=1
),
g6 as
(select r6.c1 rn, a11.c1, 'Grp6' grp1, 1 step1
from (select c1 from a1
where c1 not in
(select c1 from g1
union all
select c1 from g2
union all
select c1 from g3
union all
select c1 from g4
union all
select c1 from g5)
) a11, r6
where a11.c1 between r6.c1-5 and r6.c1+5
),
bal as
(select 0 rn, c1, 'Rest' grp1, 1 step1
from a1
where (0,c1) not in
(select 0, c1 from g1
union all
select 0, c1 from g2
union all
select 0, c1 from g3
union all
select 0, c1 from g4
union all
select 0, c1 from g5
union all
select 0, c1 from g6)
)
select a.*, round(avg(c1) over (partition by rn),3) average1 from g1 a
union all
select a.*, round(avg(c1) over (partition by rn),3) average1 from g2 a
union all
select a.*, round(avg(c1) over (partition by rn),3) average1 from g3 a
union all
select a.*, round(avg(c1) over (partition by rn),3) average1 from g4 a
union all
select a.*, round(avg(c1) over (partition by rn),3) average1 from g5 a
union all
select a.*, round(avg(c1) over (partition by rn),3) average1 from g6 a
union all
select a.*, round(avg(c1) over (partition by rn),3) average1 from bal a
order by 3,2
);
=============
Create n2.sql
-------------
drop table n2;
create table n2 as
select * from
(with a1 as
(select * from n1
),
g1 as
(select distinct average1 avrg from a1 where grp1='Grp1' and step1=1
),
g12 as
(select c1, 'Grp1' grp2, 2 step2
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average1 avrg from a1 where grp1='Grp2' and step1=1
),
g22 as
(select c1, 'Grp2' grp2, 2 step2
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average1 avrg from a1 where grp1='Grp3' and step1=1
),
g32 as
(select c1, 'Grp3' grp2, 2 step2
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average1 avrg from a1 where grp1='Grp4' and step1=1
),
g42 as
(select c1, 'Grp4' grp2, 2 step2
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average1 avrg from a1 where grp1='Grp5' and step1=1
),
g52 as
(select c1, 'Grp5' grp2, 2 step2
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average1 avrg from a1 where grp1='Grp6' and step1=1
),
g62 as
(select c1, 'Grp6' grp2, 2 step2
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp2),3) average2 from g12 a
union
select a.*, round(avg(c1) over (order by grp2),3) average2 from g22 a
union
select a.*, round(avg(c1) over (order by grp2),3) average2 from g32 a
union
select a.*, round(avg(c1) over (order by grp2),3) average2 from g42 a
union
select a.*, round(avg(c1) over (order by grp2),3) average2 from g52 a
union
select a.*, round(avg(c1) over (order by grp2),3) average2 from g62 a
order by 2, 1
);
=============
Create n3.sql
-------------
drop table n3;
create table n3 as
select * from
(with a1 as
(select * from n2
),
g1 as
(select distinct average2 avrg from a1 where grp2='Grp1' and step2=2
),
g13 as
(select c1, 'Grp1' grp3, 3 step3
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average2 avrg from a1 where grp2='Grp2' and step2=2
),
g23 as
(select c1, 'Grp2' grp3, 3 step3
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average2 avrg from a1 where grp2='Grp3' and step2=2
),
g33 as
(select c1, 'Grp3' grp3, 3 step3
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average2 avrg from a1 where grp2='Grp4' and step2=2
),
g43 as
(select c1, 'Grp4' grp3, 3 step3
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average2 avrg from a1 where grp2='Grp5' and step2=2
),
g53 as
(select c1, 'Grp5' grp3, 3 step3
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average2 avrg from a1 where grp2='Grp6' and step2=2
),
g63 as
(select c1, 'Grp6' grp3, 3 step3
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp3),3) average3 from g13 a
union
select a.*, round(avg(c1) over (order by grp3),3) average3 from g23 a
union
select a.*, round(avg(c1) over (order by grp3),3) average3 from g33 a
union
select a.*, round(avg(c1) over (order by grp3),3) average3 from g43 a
union
select a.*, round(avg(c1) over (order by grp3),3) average3 from g53 a
union
select a.*, round(avg(c1) over (order by grp3),3) average3 from g63 a
order by 2,1
);
==============
Create n4.sql
-------------
drop table n4;
create table n4 as
select * from
(with a1 as
(select * from n3
),
g1 as
(select distinct average3 avrg from a1 where grp3='Grp1' and step3=3
),
g14 as
(select c1, 'Grp1' grp4, 4 step4
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average3 avrg from a1 where grp3='Grp2' and step3=3
),
g24 as
(select c1, 'Grp2' grp4, 4 step4
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average3 avrg from a1 where grp3='Grp3' and step3=3
),
g34 as
(select c1, 'Grp3' grp4, 4 step4
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average3 avrg from a1 where grp3='Grp4' and step3=3
),
g44 as
(select c1, 'Grp4' grp4, 4 step4
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average3 avrg from a1 where grp3='Grp5' and step3=3
),
g54 as
(select c1, 'Grp5' grp4, 4 step4
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average3 avrg from a1 where grp3='Grp6' and step3=3
),
g64 as
(select c1, 'Grp6' grp4, 4 step4
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp4),3) average4 from g14 a
union
select a.*, round(avg(c1) over (order by grp4),3) average4 from g24 a
union
select a.*, round(avg(c1) over (order by grp4),3) average4 from g34 a
union
select a.*, round(avg(c1) over (order by grp4),3) average4 from g44 a
union
select a.*, round(avg(c1) over (order by grp4),3) average4 from g54 a
union
select a.*, round(avg(c1) over (order by grp4),3) average4 from g64 a
order by 2,1
);
==============
Create n5.sql
-------------
drop table n5;
create table n5 as
select * from
(with a1 as
(select * from n4
),
g1 as
(select distinct average4 avrg from a1 where grp4='Grp1' and step4=4
),
g15 as
(select c1, 'Grp1' grp5, 5 step5
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average4 avrg from a1 where grp4='Grp2' and step4=4
),
g25 as
(select c1, 'Grp2' grp5, 5 step5
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average4 avrg from a1 where grp4='Grp3' and step4=4
),
g35 as
(select c1, 'Grp3' grp5, 5 step5
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average4 avrg from a1 where grp4='Grp4' and step4=4
),
g45 as
(select c1, 'Grp4' grp5, 5 step5
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average4 avrg from a1 where grp4='Grp5' and step4=4
),
g55 as
(select c1, 'Grp5' grp5, 5 step5
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average4 avrg from a1 where grp4='Grp6' and step4=4
),
g65 as
(select c1, 'Grp6' grp5, 5 step5
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp5),3) average5 from g15 a
union
select a.*, round(avg(c1) over (order by grp5),3) average5 from g25 a
union
select a.*, round(avg(c1) over (order by grp5),3) average5 from g35 a
union
select a.*, round(avg(c1) over (order by grp5),3) average5 from g45 a
union
select a.*, round(avg(c1) over (order by grp5),3) average5 from g55 a
union
select a.*, round(avg(c1) over (order by grp5),3) average5 from g65 a
order by 2,1
);
===============
Create n6.sql
-------------
drop table n6;
create table n6 as
select * from
(with a1 as
(select * from n5
),
g1 as
(select distinct average5 avrg from a1 where grp5='Grp1' and step5=5
),
g16 as
(select c1, 'Grp1' grp6, 6 step6
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average5 avrg from a1 where grp5='Grp2' and step5=5
),
g26 as
(select c1, 'Grp2' grp6, 6 step6
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average5 avrg from a1 where grp5='Grp3' and step5=5
),
g36 as
(select c1, 'Grp3' grp6, 6 step6
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average5 avrg from a1 where grp5='Grp4' and step5=5
),
g46 as
(select c1, 'Grp4' grp6, 6 step6
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average5 avrg from a1 where grp5='Grp5' and step5=5
),
g56 as
(select c1, 'Grp5' grp6, 6 step6
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average5 avrg from a1 where grp5='Grp6' and step5=5
),
g66 as
(select c1, 'Grp6' grp6, 6 step6
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp6),3) average6 from g16 a
union
select a.*, round(avg(c1) over (order by grp6),3) average6 from g26 a
union
select a.*, round(avg(c1) over (order by grp6),3) average6 from g36 a
union
select a.*, round(avg(c1) over (order by grp6),3) average6 from g46 a
union
select a.*, round(avg(c1) over (order by grp6),3) average6 from g56 a
union
select a.*, round(avg(c1) over (order by grp6),3) average6 from g66 a
order by 2,1
);
=============
Create n7.sql
-------------
drop table n7;
create table n7 as
select * from
(with a1 as
(select * from n6
),
g1 as
(select distinct average6 avrg from a1 where grp6='Grp1' and step6=6
),
g17 as
(select c1, 'Grp1' grp7, 7 step7
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average6 avrg from a1 where grp6='Grp2' and step6=6
),
g27 as
(select c1, 'Grp2' grp7, 7 step7
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average6 avrg from a1 where grp6='Grp3' and step6=6
),
g37 as
(select c1, 'Grp3' grp7, 7 step7
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average6 avrg from a1 where grp6='Grp4' and step6=6
),
g47 as
(select c1, 'Grp4' grp7, 7 step7
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average6 avrg from a1 where grp6='Grp5' and step6=6
),
g57 as
(select c1, 'Grp5' grp7, 7 step7
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average6 avrg from a1 where grp6='Grp6' and step6=6
),
g67 as
(select c1, 'Grp6' grp7, 7 step7
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp7),3) average7 from g17 a
union
select a.*, round(avg(c1) over (order by grp7),3) average7 from g27 a
union
select a.*, round(avg(c1) over (order by grp7),3) average7 from g37 a
union
select a.*, round(avg(c1) over (order by grp7),3) average7 from g47 a
union
select a.*, round(avg(c1) over (order by grp7),3) average7 from g57 a
union
select a.*, round(avg(c1) over (order by grp7),3) average7 from g67 a
order by 2,1
);
==============
Create n8.sql
--------------
drop table n8;
create table n8 as
select * from
(with a1 as
(select * from n7
),
g1 as
(select distinct average7 avrg from a1 where grp7='Grp1' and step7=7
),
g18 as
(select c1, 'Grp1' grp8, 8 step8
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average7 avrg from a1 where grp7='Grp2' and step7=7
),
g28 as
(select c1, 'Grp2' grp8, 8 step8
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average7 avrg from a1 where grp7='Grp3' and step7=7
),
g38 as
(select c1, 'Grp3' grp8, 8 step8
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average7 avrg from a1 where grp7='Grp4' and step7=7
),
g48 as
(select c1, 'Grp4' grp8, 8 step8
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average7 avrg from a1 where grp7='Grp5' and step7=7
),
g58 as
(select c1, 'Grp5' grp8, 8 step8
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average7 avrg from a1 where grp7='Grp6' and step7=7
),
g68 as
(select c1, 'Grp6' grp8, 8 step8
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp8),3) average8 from g18 a
union
select a.*, round(avg(c1) over (order by grp8),3) average8 from g28 a
union
select a.*, round(avg(c1) over (order by grp8),3) average8 from g38 a
union
select a.*, round(avg(c1) over (order by grp8),3) average8 from g48 a
union
select a.*, round(avg(c1) over (order by grp8),3) average8 from g58 a
union
select a.*, round(avg(c1) over (order by grp8),3) average8 from g68 a
order by 2, 1
);
==============
Create n9.sql
--------------
drop table n9;
create table n9 as
select * from
(with a1 as
(select * from n8
),
g1 as
(select distinct average8 avrg from a1 where grp8='Grp1' and step8=8
),
g19 as
(select c1, 'Grp1' grp9, 9 step9
from a1, g1
where c1 between g1.avrg-5 and g1.avrg+5
),
g2 as
(select distinct average8 avrg from a1 where grp8='Grp2' and step8=8
),
g29 as
(select c1, 'Grp2' grp9, 9 step9
from a1, g2
where c1 between g2.avrg-5 and g2.avrg+5
),
g3 as
(select distinct average8 avrg from a1 where grp8='Grp3' and step8=8
),
g39 as
(select c1, 'Grp3' grp9, 9 step9
from a1, g3
where c1 between g3.avrg-5 and g3.avrg+5
),
g4 as
(select distinct average8 avrg from a1 where grp8='Grp4' and step8=8
),
g49 as
(select c1, 'Grp4' grp9, 9 step9
from a1, g4
where c1 between g4.avrg-5 and g4.avrg+5
),
g5 as
(select distinct average8 avrg from a1 where grp8='Grp5' and step8=8
),
g59 as
(select c1, 'Grp5' grp9, 9 step9
from a1, g5
where c1 between g5.avrg-5 and g5.avrg+5
),
g6 as
(select distinct average8 avrg from a1 where grp8='Grp6' and step8=8
),
g69 as
(select c1, 'Grp6' grp9, 9 step9
from a1, g6
where c1 between g6.avrg-5 and g6.avrg+5
)
select a.*, round(avg(c1) over (order by grp9),3) average9 from g19 a
union
select a.*, round(avg(c1) over (order by grp9),3) average9 from g29 a
union
select a.*, round(avg(c1) over (order by grp9),3) average9 from g39 a
union
select a.*, round(avg(c1) over (order by grp9),3) average9 from g49 a
union
select a.*, round(avg(c1) over (order by grp9),3) average9 from g59 a
union
select a.*, round(avg(c1) over (order by grp9),3) average9 from g69 a
order by 2, 1
);
================

After Creating all sqls, call all sqls from n.sql

Create n.sql
------------

@n1
@n2
@n3
@n4
@n5
@n6
@n7
@n8
@n9

==============

Create table n0 with one column C1 and insert test values as column c1;

Now run from sql prompt n.sql and tables n1-n9 are created.

And you find that last tables values remain in same groups (when I tested it was same
in 7,8,9 tables)
``````
Now i want to implement the dynamic k mean crusting algorithm, so plz help me in this regards.

Thanks.

Regards

Thanks
• ###### 2. Re: Dynamic K mean clustring
Hello every body,
please suggest me to create function for the dynamic k mean clustring.
• ###### 3. Re: Dynamic K mean clustring
Hi,
If you are asking for advice on how to put together your own KMeans implementation, I don't think this is the right forum. If you want to understand how to use the Oracle KMeans implementation, then you are at the right place.
Here is a sample script that runs on 11.2:

set serveroutput on
-- Create the settings table
CREATE TABLE km_mk_model_settings (
setting_name VARCHAR2(30),
setting_value VARCHAR2(30));

BEGIN
INSERT INTO km_mk_model_settings (setting_name, setting_value) VALUES
(dbms_data_mining.algo_name, dbms_data_mining.ALGO_KMEANS);
INSERT INTO km_mk_model_settings (setting_name, setting_value) VALUES
(dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
COMMIT;
END;
/
-- Create the model using the specified settings
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'km_mk_model',
mining_function => dbms_data_mining.clustering,
data_table_name => 'mining_data_build_v',
case_id_column_name => 'cust_id',
settings_table_name => 'km_mk_model_settings');
END;
/

Also, you can get the sample code, although it does not highlight the use of dbms_data_mining.prep_auto_on option shown above (delivered in 11.1).