使查询和数据帧响应并每5分钟刷新一次

本文介绍了使查询和数据帧响应并每5分钟刷新一次的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 // library(plyr) library(shiny) library(ggplot2) library(scales) library(shinydashboard) library(gridExtra) library(DT) library(ggthemes) library(plotly) library(data.table) library(plotrix) library(shinyjs) library(shinycssloaders) # connection with dash db shinyServer(function(input, output, session) { # withProgress(message = 'Data Downloading', # detail = 'This may take a while...', value = 0, { # for (i in 1:15) { # incProgress(1/15) # Sys.sleep(10) # }}) dsn_driver = "" dsn_database = "" # e.g. "BLUDB" dsn_hostname = "" # e.g.: "awh-yp-small03.services.dal.bluemix" dsn_port = "50000" # e.g. "50000" dsn_protocol = "TCPIP" # i.e. "TCPIP" dsn_uid = "" # e.g. "dash104434" dsn_pwd = "" jcc = JDBC("com.ibm.db2.jcc.DB2Driver", "db2jcc4.jar"); jdbc_path = paste("jdbc:db2://", dsn_hostname, ":", dsn_port, "/", dsn_database, sep=""); conn = dbConnect(jcc, jdbc_path, user=dsn_uid, password=dsn_pwd)

I想要使此查询每5分钟更新一次

I want to make this query to be updated on every 5 min

query="select RETAIL_STORE.STR_NM as STR_NM,year(RETAIL_STR_SALES_DETAIL.SALE_DATE) as YEAR,month(retail_str_sales_detail.sale_date) as Monthnumber, monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE) AS MONTHNAME,WEEK(RETAIL_STR_SALES_DETAIL.SALE_DATE) AS WEEKNAME ,RETAIL_STR_SALES_DETAIL.prod_id ,RETAIL_STR_SALES_DETAIL.PROD_NM as PROD_NM ,retail_store_area_wise.area_name AS Area_Name ,SUM(RETAIL_STR_SALES_DETAIL.qty) AS QTY ,round(sum(RETAIL_STR_SALES_DETAIL.total),2) as TOTAL ,RETAIL_STORE_PRODUCT_HEMAS.MFG as MFG from RETAIL_STORE_PRODUCT_HEMAS INNER JOIN RETAIL_STR_SALES_DETAIL ON RETAIL_STORE_PRODUCT_HEMAS.prod_id = RETAIL_STR_SALES_DETAIL.prod_id INNER JOIN retail_dstr_prod ON retail_dstr_prod.prod_id = RETAIL_STR_SALES_DETAIL.prod_id INNER JOIN retail_store ON retail_store.store_id = RETAIL_STR_SALES_DETAIL.store_id INNER JOIN retail_store_area_wise ON retail_store_area_wise.store_id = RETAIL_STR_SALES_DETAIL.store_id where retail_dstr_prod.dstr_id='1495220190' group by RETAIL_STORE.STR_NM,RETAIL_STR_SALES_DETAIL.SALE_DATE ,year(RETAIL_STR_SALES_DETAIL.SALE_DATE) , monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE) , RETAIL_STR_SALES_DETAIL.prod_id , RETAIL_STR_SALES_DETAIL.PROD_NM , retail_store_area_wise.area_name , RETAIL_STORE_PRODUCT_HEMAS.MFG , RETAIL_STR_SALES_DETAIL.store_id , retail_store.store_id, WEEK(RETAIL_STR_SALES_DETAIL.SALE_DATE) ORDER BY year(RETAIL_STR_SALES_DETAIL.SALE_DATE),month(retail_str_sales_detail.sale_date),WEEK(RETAIL_STR_SALES_DETAIL.SALE_DATE)"; rs=dbSendQuery(conn,query) query1 <- fetch(rs, -1)

，并通过查询刷新数据框

and also refresh the data frame with query

biz=data.frame( year=query1$YEAR, ProdNm=query1$PROD_NM, Total = as.numeric(as.character(query1$TOTAL)), Sold_that_day = query1$QTY, Month = query1$MONTHNAME, Weekand= query1$WEEKNAME, AreaName=query1$AREA_NAME, Manufacturer=query1$MFG, stringsAsFactors = FALSE ) # Total sales By year In 2017 # totalsales="select year(RETAIL_STR_SALES_DETAIL.SALE_DATE) as YEAR, monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE) AS MONTHNAME ,round(sum(RETAIL_STR_SALES_DETAIL.total),2) as TOTAL from retail_str_sales_detail where year(RETAIL_STR_SALES_DETAIL.SALE_DATE)='2017' group by year(RETAIL_STR_SALES_DETAIL.SALE_DATE), monthname(RETAIL_STR_SALES_DETAIL.SALE_DATE)"; totalsalesbyyear <- fetch(dbSendQuery(conn,totalsales), -1) bizmonthly=data.frame( MonthName=factor(totalsalesbyyear$MONTHNAME,levels = month.name), Year=totalsalesbyyear$YEAR, MonthTotal=as.numeric(as.character(totalsalesbyyear$TOTAL)) ) print(bizmonthly)

推荐答案

类似的事情应该可以解决。请注意，它将每隔5分钟全局更新一次，因此不会在每次会话中触发。根据 reactiveTimer ，每10秒钟检查一次时间。确保通过 biz（）

Something like this should do the trick. Note that it will update globally once every 5 mins so its not going to fire on every session. The time checking is every 10 seconds as per reactiveTimer. Make sure you access the data for biz via biz()

library(shiny) autoInvalidate <- reactiveTimer(10000,session = NULL) Getupdates <- function(qfrequency){ rs <- dbSendQuery(conn,query) if(!exists("nextCall")){ message("Initiating") query1 <<- fetch(rs, -1) nextCall <<- Sys.time() + qfrequency message("Got Initial Data") } else if (Sys.time() >= nextCall){ message(paste0(Sys.time(), " Querying Periodically")) query1 <<- fetch(rs, -1) nextCall <<- Sys.time() + qfrequency } else{ return() } } ui <- fluidPage(tableOutput("table")) server <- function(input, output, session) { observe({ autoInvalidate() # 300 is 5 mins Getupdates(300) }) biz <- reactive({ bizdata <- data.frame( year=query1$YEAR, ProdNm=query1$PROD_NM, Total = as.numeric(as.character(query1$TOTAL)), Sold_that_day = query1$QTY, Month = query1$MONTHNAME, Weekand= query1$WEEKNAME, AreaName=query1$AREA_NAME, Manufacturer=query1$MFG, stringsAsFactors = F ) bizdata }) output$table <- renderTable({biz()}) } shinyApp(ui, server)