Rでデータベース(sqlite3)に逐次的にデータを詰める．
- 行うこと
（１）更新したい日が、更新中のデータベースの更新日付と同じかを確認->異なるなら新しいtableに更新tableを置き換える．
（２）更新tableをにデータを追加していく．（データに列がたりない場合は追加）．
- tableは
ID|日付1|日付2|日付3|...という形式
(IDをprimary keyとすることでreplace文を利用する)．
- Rコード
library("RSQLite")
data_list<-list()
####疑似データ生成（いらない）###
for(i in 1:10){
date1=as.Date("2006-11-1")+1:10
val<-1:10+rnorm(10)
d_trend<-data.frame(1:length(date1),format(date1),val)
data_list[[i]]<-d_trend
}
####疑似データ#########
#更新した日が、更新中のデータベースの日付と同じかを確認
drv<-dbDriver("SQLite")
con<-dbConnect(drv,"syoku_tmp.sqlite3")
now_update<-unlist(dbGetQuery(con,"select now_update from update_info"))
want_update="2011-01-01"
if(want_update != now_update){
cat(now_update,"\n")
cat("test\n")
try(dbGetQuery(con,"drop table trend_time_series_new;"))
try(dbGetQuery(con,"drop table freq_mean_time_series_new;"))
dbGetQuery(con,"create table trend_time_series_new(id primary key);")
dbGetQuery(con,"create table freq_mean_time_series_new(id primary key);")
}
for(i in 1:length(data_list)){
id=i
d<-data_list[[i]]
column_names_trend=d[,2]
trend_data=d[,3]
#tableに列があるかどうか調べる．なければ追加．
v<-length(dbGetQuery(con,"pragma table_info(trend_time_series_new)")[,1])
if(v==1){
for(iiii in 1:length(column_names_trend)){
query=paste(collapse="",sep="","alter table trend_time_series_new add [",column_names_trend[iiii],"];")
cat(query,"\n")
dbGetQuery(con,query)
}
}
keys<-(dbGetQuery(con,"pragma table_info(trend_time_series_new)")[,2])
#keys_date<-paste(collapse=",","[",keys[-1],"]")
keys_date<-paste(collapse=",",sep="","[",column_names_trend,"]")
keys<-paste(collapse=",",keys[1],",",keys_date)
values<-paste(collapse=",",c(id,trend_data))
query=paste(sep="","replace into trend_time_series_new(",keys,")"," values(",values,")")
#cat(query)
cat("data_inasert",i,"\n")
dbGetQuery(con,query)
}
#cat(d,"\n")
- 結果のtable
select * from trend_time_series_new;
sqlite> select * from trend_time_series_new;
1|1.74216426642007|1.15746096911096|1.22604090764894|5.74384946199283|4.16048344102|6.35242713746178|6.53509265750246|8.32309181282388|9.69234347526966|9.1514501713778
2|0.688924429592484|2.28650453208968|3.79120527680377|3.74733483142553|4.52796319479229|4.68702036633908|7.20527548613953|8.04196013134207|9.65045919152365|9.71001102355752
3|1.10974853832513|1.61144018668012|1.5606415540936|4.75323024946711|3.6497842071526|6.71511555342707|6.182777490438|8.18276764840514|8.57641822395217|7.84627050663797
4|1.20730610096204|0.281577834874096|3.17703408481475|5.18286980965751|4.94179548197136|4.83402588687983|8.51900812416311|6.90504033171872|9.49530425149758|7.14295646107687
5|-0.134263141140184|0.855261155079369|3.37719644963614|4.73046226157005|4.14067542615129|5.40822985121723|6.76285129799583|7.34177145603216|8.72744714385967|10.0117366284731
6|2.30932391204585|3.17027510763729|3.37304652930384|4.56961553857534|5.22315801114618|5.37398822282897|7.50701131302169|9.22080579004208|9.59761139805944|10.0453363696911
7|-0.47419040940387|1.00486064112253|4.11148038417412|3.7703426730129|5.96332733459318|5.50658959889634|8.29465887067891|8.02579060977058|8.4711712672579|8.28311446962813
8|1.86615705093297|1.54959822340292|3.59102997187421|4.27356722126329|4.18571516042845|4.12003377550779|6.49102002199568|7.27969332422389|7.71675369855051|10.3055700402934
9|-0.0543844169168486|1.53941205253435|2.56373064913614|3.77976797328231|4.3256378014893|5.35177659593993|7.64559406325345|8.48013065052815|10.4075950737314|10.2263907468294
10|1.07984537947647|2.48262887205794|3.44561959903089|2.37301569285974|4.62380489538891|5.87463538778615|8.00319173291311|7.22764965875162|9.41912984414329|10.0110703209104
