drop procedure if exists kmeans;


drop table if exists km_data;      -- contains initial data, only 2 dimensions
drop table if exists km_clusters;  -- contains the final clusters centres
drop table if exists km_steps;     -- shows intermediate steps
create table km_data (id int primary key, cluster_id int, x1 float, x2 float);
create table km_clusters (id int auto_increment primary key, x1 float, x2 float);
create table km_steps (id int, x1 float, x2 float);


DELIMITER //
CREATE PROCEDURE kmeans(v_K int)
BEGIN
TRUNCATE km_clusters;
truncate km_steps;

-- initialize cluster centers
INSERT INTO km_clusters (x1, x2) SELECT x1, x2 FROM km_data LIMIT v_K;
REPEAT
    insert into km_steps select * from km_clusters;
    -- assign clusters to data points
    UPDATE km_data d SET cluster_id = (SELECT id FROM km_clusters c 
        ORDER BY POW(d.x1-c.x1,2)+POW(d.x2-c.x2,2) ASC LIMIT 1);
    -- calculate new cluster center
    UPDATE km_clusters C, (SELECT cluster_id, 
        AVG(x1) AS x1, AVG(x2) AS x2 
        FROM km_data GROUP BY cluster_id) D 
  SET C.x1=D.x1, C.x2=D.x2 WHERE C.id=D.cluster_id;
UNTIL ROW_COUNT() = 0 END REPEAT;
END//