--创建临时表

DROP TABLE if exists tmp.t2_collect;

CREATE TABLE tmp.t2_collect(

    h int,

    cnt int

) COMMENT '用户点击数据临时表';

--插入临时表

insert overwrite table tmp.t2_collect

--分组

select a1.h, count(1) as cnt from(

    --取出时

    select hour(createtime) as h from bdm.husercollect

)a1

group by a1.h;

#!/usr/bin/Rscript

library(RHive)

rhive.connect(host ='192.168.107.82')

data <- rhive.query('select h,cnt from tmp.t2_collect limit 6000')

x <- data$h

y <- data$cnt

--组合成数据框

df <- data.frame(x, y)

--添加列名

colnames(df) <- c("hour", "cnt")

--cluster.stats函数需要使用fpc库

library(fpc)

--k取2到8评估K

K <- 2:8

--每次迭代30次，避免局部最优

round <- 30

rst <- sapply(K, function(i){

    print(paste("K=",i))

    mean(sapply(1:round,function(r){

        print(paste("Round",r))

        result <- kmeans(df, i)

        stats <- cluster.stats(dist(df), result$cluster)

        stats$avg.silwidth

    }))

})

--加载图形库

library(Cairo)

png("k-points-pic.png", width=800, height=600)

plot(K, rst, type='l', main='outline & R relation', ylab='outline coefficient')

dev.off()

rhive.close()

#!/usr/bin/Rscript

library(RHive)

rhive.connect(host ='192.168.107.82')

data <- rhive.query('select h,cnt from tmp.t2_collect limit 6000')

x <- data$h

y <- data$cnt

--组合成数据框

df <- data.frame(x, y)

--添加列名

colnames(df) <- c("hour", "cnt")

--Kmeans

kc <- kmeans(df, 3);

--具体分类情况

--fitted(kc);

library(Cairo)

png("k-means-pic.png", width=800, height=600)

plot(df[c("hour", "cnt")], col = kc$cluster, pch = 8);

points(kc$centers[,c("hour", "cnt")], col = 1:3, pch = 8, cex=2);

dev.off()

rhive.close()