library(RMySQL)

library(DBI)

#資料分散在兩個資料庫
conn <- dbConnect(MySQL(), dbname = "stream", username = "streamusr" ,password = "00000000" , host = "140.120.55.97" , port = 3306)
conn1 <- dbConnect(MySQL(), dbname = "topmso", username = "streamusr" ,password = "00000000" , host = "140.120.55.97" , port = 3306)
dbSendQuery(conn,'set names utf8')
dbSendQuery(conn1,'set names utf8')

datapart1<-dbGetQuery(conn, "SELECT * FROM `stream_view_temp`")
datapart2<-dbGetQuery(conn1, "SELECT * FROM `stream_view_temp`")

#合併兩資料檔
datawip<-datapart2[,-9]
colnames(datawip)<-c("session_id","cust_no","create_time","destroy_time","ei_start_time","ei_end_time","ei_event_name","SD_SERVICE_NAME")
finaldata<-rbind(datapart1,datawip)

#判定開始時間即結束時間
start_time = as.POSIXct('2015-09-01 00:00:00',format = "%Y-%m-%d %H:%M:%S")
end_time = as.POSIXct('2016-01-31 23:59:59',format = "%Y-%m-%d %H:%M:%S")

i=1
#每半小時計算一次
interval=1800
#所希望收集資料
freq<-cbind("時間","第1四分位數","中位數","第3四分位數")
#迴圈用虛擬時間判斷
sitime=start_time
eitime=sitime+interval
WIP1<-subset(finaldata,finaldata[,3]>=sitime)

while(sitime<=end_time){
  WIP2<-subset(WIP1,WIP1[,3]<=eitime)
  j=1
  g<-c(0)
  Frequency=nrow(WIP2)
  while(j<= Frequency){
    g<-cbind(g,as.numeric(strptime(WIP2[j,4], "%Y-%m-%d %H:%M:%S"))-as.numeric(strptime(WIP2[j,3], "%Y-%m-%d %H:%M:%S")))
    j=j+1}

  x<-sort(g)
  q1 = quantile(x, 0.25)
  med = median(x)
  q3 = quantile(x, 0.75)

  starttime<-strftime(sitime)
  h<-cbind(starttime,q1,med,q3)
  freq<-rbind(freq,h)
  sitime<-sitime+interval
  eitime<-eitime+interval
  if(sitime<=end_time)WIP1<-subset(finaldata,finaldata[,3]>=sitime)
}
freqtry<-freq[-1,]
write.csv(freqtry,file="duration-2016ALLup.csv",fileEncoding = "big5")