数据挖掘成果固化_聚类分析_数据分析师

--聚类样本数据模拟
--BY:@ETwise
--输入表1：cluster_sample
--输入表2：cluster_center
--20141213

create table cluster_sample
(
  serv_id     NUMBER ,
  label_1     number,
  label_2     number,
  label_3     number,
  label_4     number
);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (1,2,3,4,5);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (2,2.5,4.2,4.2,5.2);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (3,3.2,4.1,2.3,5.1);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (4,1.1,1.2,2.2,3.2);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (5,1.7,1.75,1.35,4.1);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (6,1.5,1.2,0.62,3.38);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (7,1.3,0.65,-0.11,3);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (8,1.1,0.1,-0.84,2.62);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (9,0.9,-0.45,-1.57,2.24);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (11,0.5,-1.55,-3.03,1.48);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (12,0.3,-2.1,-3.76,1.1);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (13,0.1,-2.65,-4.49,0.72);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (14,-0.1,-3.2,-5.22,0.34);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (15,-0.3,-3.75,-5.95,-0.04);
INSERT INTO cluster_sample (serv_id,label_1,label_2,label_3,label_4) VALUES (16,-0.5,-4.3,-6.68,-0.42);

--创建聚类分析所得到的中心点数据
create table cluster_center
(
  row_1     number,
  row_2     number,
  row_3     number,
  row_4     number,
  type_id   VARCHAR2(20) not null
);
INSERT INTO cluster_center (row_1,row_2,row_3,row_4,type_id) VALUES (0,0,0,0,'t1');
INSERT INTO cluster_center (row_1,row_2,row_3,row_4,type_id) VALUES (1,1,1,1,'t2');
INSERT INTO cluster_center (row_1,row_2,row_3,row_4,type_id) VALUES (2,2,2,2,'t3');
INSERT INTO cluster_center (row_1,row_2,row_3,row_4,type_id) VALUES (3,3,3,3,'t4');

--聚类分析成果系统固化相关说明(K-means)
--第一步：对计算每个点与各个中心点的距离，并对应得到相应的分类type_id
select serv_id,
       sqrt(power((label_1 - row_1), 2) + power((label_2 - row_2), 2) +
            power((label_3 - row_3), 2) + power((label_4 - row_4), 2)) OS,
       type_id
  from cluster_sample a, cluster_center b
  ;
 
--第二步：使用开窗函数对各serv_id的各个中心点的距离进行升序排序，并打上相应的编号
select serv_id,
       os,
       row_number() over(partition by serv_id order by os asc) myrow_1,
       type_id
  from (select serv_id,
               sqrt(power((label_1 - row_1), 2) +
                    power((label_2 - row_2), 2) +
                    power((label_3 - row_3), 2) +
                    power((label_4 - row_4), 2)) OS,
               type_id
          from cluster_sample a, cluster_center b)
          ;
         
--第三步：提取各个serv_id的最小距离数据，即可得到各个serv_id的类别
select *
  from (select serv_id,
               os,
               row_number() over(partition by serv_id order by os asc) myrow_1,
               type_id
          from (select serv_id,
                       sqrt(power((label_1 - row_1), 2) +
                            power((label_2 - row_2), 2) +
                            power((label_3 - row_3), 2) +
                            power((label_4 - row_4), 2)) OS,
                       type_id
                  from cluster_sample a, cluster_center b))
 where myrow_1 = 1
 ;

--其他办法:一步到位,直接代入中心点进行计算
select serv_id,
       case
         when least(os1, os2, os3, os4) = os1 then
          't1'
         when least(os1, os2, os3, os4) = os2 then
          't2'
         when least(os1, os2, os3, os4) = os3 then
          't3'
         when least(os1, os2, os3, os4) = os4 then
          't4'
         else
          '-1'
       end type_id
  from (select serv_id,
               sqrt(power((label_1 - 0), 2) + power((label_2 - 0), 2) +
                    power((label_3 - 0), 2) + power((label_4 - 0), 2)) os1,
               sqrt(power((label_1 - 1), 2) + power((label_2 - 1), 2) +
                    power((label_3 - 1), 2) + power((label_4 - 1), 2)) os2,
               sqrt(power((label_1 - 2), 2) + power((label_2 - 2), 2) +
                    power((label_3 - 2), 2) + power((label_4 - 2), 2)) os3,
               sqrt(power((label_1 - 3), 2) + power((label_2 - 3), 2) +
                    power((label_3 - 3), 2) + power((label_4 - 3), 2)) os4
          from cluster_sample t)
          ;