使查询和数据帧响应并每5分钟刷新一次 [英] make a query and data frame reactive and refresh it on every 5 minutes

问题描述

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

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)

这篇关于使查询和数据帧响应并每5分钟刷新一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！