进行查询和数据帧的反应，并刷新它每隔5分钟

Z时代
2024-01-10
分类：问答

// 
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.net" 
    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)

我想使这个查询要在每5分钟更新进行查询和数据帧的反应，并刷新它每隔5分钟

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)

，并刷新与查询

数据帧

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()

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)