目的:根据历史数据值,正对每一个dlbh,给定具体预测值,并且给的其预测值的浮动区间
代码如下:
1、连接Oracle数据库
library(DBI)
library(ROracle)
drv=dbDriver('Oracle')
conn=dbConnect(drv,'AQTS_ZHZX','AQTS_ZHZX','localhost:1521/jgyw')
2、查询需要的BASE数据(以七天数据为一个历史数据)
数据:gcsjrq为日期,dlbh与日期为多对多关系、dlbh与dlmc为一对一关系,zll 为总流量
# get the flux of the DL
rs=dbSendQuery(conn,"select d.gcsjrq,t2.dlbh, sum(ZLL) zll ,t4.dlmc from dw_fact_flux d
join Road_Place_Direct t2 on d.jkdbh=t2.jkdbh
join road_baseinfo t4 on t2.dlbh=t4.dlbh
where d.gcsjrq >= to_char(sysdate-8,'yyyymmdd') and d.gcsjrq<to_char(sysdate,'yyyymmdd')
group by t2.dlbh,d.gcsjrq,t4.dlmc
order by t2.dlbh,d.gcsjrq,t4.dlmc asc")
rsv=dbSendQuery(conn,"select to_char(sysdate,'yyyymmdd') from dual ")
datad=fetch(rsv)
datad=as.matrix(datad)
data=fetch(rs)
data=as.matrix(data)
3、对dlbh去重,获得起对应dlmc值
dluniq=as.numeric(unique(data[,2]))
dlmcuniq=unique(data[,4])
4、对每一个dlbh生成预测值
for ( i in 1:length(dluniq))
{
5、对每一个dlbh,取得其对应行,并设定进行下一步的判定条件
aa=data[data[,2]==dluniq[i],]
t=nrow(as.matrix(aa,3,1))/3
if (t>1)
{
6、 数据格式转变,以及生成x,y值
pre=as.matrix(as.numeric(aa[,3]))
z=as.data.frame(cbind(order(pre),pre))
zn=as.data.frame(c(nrow(pre)+1))
colnames(zn)=c('x')
colnames(z)=c('x','y')
7、 生成拟合函数,生成预测值
fit2 <- lm(y ~ x, z)
PRE_FLUX=round(predict(fit2,zn,interval = "prediction"))
#plot(z$x,z$y)
#abline(fit2)
8、把需要结果写入数据库
PRE_FLUX=cbind(datad,dluniq[i],PRE_FLUX,dlmcuniq[i])
colnames(PRE_FLUX)=c('YCRQ','DLBH','PREV','PREMIN','PREMAX','DLMC')
PRE_FLUX=as.data.frame(PRE_FLUX)
if(as.matrix(PRE_FLUX$PREMIN)<0)
PRE_FLUX$PREMIN=0
dbRemoveTable(conn, 'FLUX_PREDICT_DL')
dbWriteTable(conn,'FLUX_PREDICT_DL',PRE_FLUX, row.names = F, append = TRUE)
}
}
9、建模、报表展现
记录日常点滴,成长自己,方便他人,如有错误的地方欢迎提出宝贵意见!