具体操作
1、连接oracle数据库,提取所需数据
library(DBI)
library(ROracle)
drv=dbDriver('Oracle')
conn=dbConnect(drv,'AQTS_ZHZX','AQTS_ZHZX','191.100.10.00:1521/jgyw')
# get the flux of the DL
rs=dbSendQuery(conn,"SELECT substr(D.GCSJRQ,1,4) nfbm,D.DLBH,D.MARKV,SUM(D.ZLL) zll FROM DW_FACT_HOLIDAYS D
GROUP BY substr(D.GCSJRQ,1,4),D.DLBH,D.MARKV ")
data=fetch(rs)
data=as.matrix(data)
2、dlbh对应7个不同值,以及生成对应笛卡尔向量
dlbh=as.array(unique(data[,2]))
dlbh1=as.numeric(length(dlbh))*7
3、生成对应7个值的向量
hol=c(1:7)
#get the current year
nfbm=as.numeric(substr(Sys.Date(),1,4))
4、零矩阵生成,向量合并,修改列名
#get the predict value
decare<-merge(dlbh,hol)
predictv=cbind(matrix(nfbm,dlbh1,1),decare,matrix(0,dlbh1,1))
colnames(predictv)=c('NFBM','DLBH','MARKV','ZLL')
#temporary use the data as the value of predict
tempy=2016
#
5、生成的预测矩阵与原矩阵进行多列比较,如果预测矩阵比较行有值,则用自身值,否则用源矩阵赋值当前矩阵值
for(i in 1:nrow(data))
{
tempv=nrow(subset(predictv, NFBM==data[i,1] & DLBH ==data[i,2] & MARKV==data[i,3],select=c(NFBM,DLBH,MARKV,ZLL)))
if(tempv==1)
{
predictv[which(predictv[,3]==data[i,3] & predictv[,2]==data[i,2] & predictv[,1]==data[i,1]),4]=data[i,4]
}
else
{
predictv[which(predictv[,3]==data[i,3] & predictv[,2]==data[i,2] & predictv[,1]==tempy),4]=data[i,4]
}
}
6、写入库
#dbRemoveTable(conn, 'DW_FACT_HOLY_PREDICT_J')
dbWriteTable(conn,'DW_FACT_HOLY_PREDICT_DL',predictv, row.names = F, append = TRUE)
7、over(不喜勿碰)