将SQL输入日期范围传递到SHINY中的查询中

问题描述

我正在学习使用闪亮的仪表板，并使用SQL数据库SQL服务器，我希望在其中直接从数据库中提取数据。其基本思想是合并到表中具有日期范围的起始点和终止点的列，将它们制表，然后用图表表示它们。

我发现以下帖子是关于如何将SQL输入语句传递到SHILY中的： How to pass input variable to SQL statement in R shiny?

不幸的是，当我尝试并应用这一点时，我收到一个错误：下标超出范围；看起来查询没有被拉入。我已经单独测试了它，并且能够拉出数据并运行每一步，没有问题。我正在使用RODBC包，我想知道这是否是问题所在。以下是我的代码：

         library(stringr)
        library(RODBC)
          library(circlize)
         library(shinydashboard)
         library(shiny)

                ui <- dashboardPage(skin = "blue",
                dashboardHeader(title = "sample"),
                dashboardSidebar(disable = TRUE),
                dashboardBody(
                  # Boxes need to be put in a row (or column)
                  fluidRow(
                    box(title = "Route Volume", background = "green", solidHeader = TRUE,
                        plotOutput(outputId= 'plot2'))),
                    fluidRow( 
                      box(background= "green", dateRangeInput("dates", label = h3("Date Range"),start = '2016-06-01',
                                                              end = '2016-06-05')), width = 4

                      ))))

              server <- function(input, output) {
            database = odbcConnect("datatbase")
            output$plot2 = renderPlot({

            d = paste0("SELECT 
               top 30
           convert(char(10),datetime,121) as date, 
           cast(start_destination as varchar(3)) 
           + (',') + cast(final_destination as varchar(3)) as combo,
           count(cast(start_destination as varchar(3)) 
           + (',') + cast(final_destination as varchar(3))) as volume
           FROM
           trips
           WHERE datetime >= ",input$dates[1]," AND
           datetime < ",input$dates[2],"
           GROUP BY
           cast(start_destination as varchar(3)) 
           + (',') + cast(final_destination as varchar(3)),
           convert(char(10),datetime,121);")

        sql = sqlQuery(database, d)

           sql = data.frame(sql, do.call(rbind, str_split(sql$combo, ',')))
       colnames(sql)[colnames(sql)=="X1"] <- "From"
         colnames(sql)[colnames(sql)=="X2"] <- "To"
         sql = sql[,c(4,5,3)]
         sql = sql[order(sql$volume, decreasing = T),]
          chordDiagram(sql)
          circos.clear()

          })

          }


          shinyApp(ui, server)

我确信这是一些愚蠢的错误、一个漏掉的引号或我对如何应用这些技术的误解。感谢您的帮助！！

                ##adding edits by Dean to test



              database = odbcConnect("database")
             output$plot2 = renderPlot({
              if(input$dates[1]!= "") {
               d = paste0("SELECT 
            top 30
          convert(char(10),datetime,121) as date, 
          cast(start_destination as varchar(3)) 
          + (',') + cast(final_destination as varchar(3)) as combo,
          count(cast(start_destination as varchar(3)) 
          + (',') + cast(final_destination as varchar(3))) as volume
          FROM
           trips
           WHERE 
           datetime >= ",input$dates[1]," AND
           datetime < ",input$dates[2],"
           GROUP BY
          cast(start_destination as varchar(3)) 
          + (',') + cast(final_destination as varchar(3)),
           convert(char(10),datetime,121);")
         sql = sqlQuery(database, d) 

        #i assumed the if statement ended here so I put the 
        #bracket below 
           sql = data.frame(sql, do.call(rbind, str_split(sql$combo, ',')))
       colnames(sql)[colnames(sql)=="X1"] <- "From"
         colnames(sql)[colnames(sql)=="X2"] <- "To"
         sql = sql[,c(4,5,3)]
         sql = sql[order(sql$volume, decreasing = T),]
          chordDiagram(sql)
          circos.clear() 

}
    })

   }

                       database = odbcConnect("database")
                           output$plot2 = renderPlot({
                           dates = as.Date(input$dates)
                           d = paste0("SELECT 
                       top 30
                      convert(char(10),datetime,121) as date, 
                     cast(start_destination as varchar(3)) 
                      + (',') + cast(final_destination as varchar(3)) as combo,
                       count(cast(start_destination as varchar(3)) 
                     + (',') + cast(final_destination as varchar(3))) as volume  
                     FROM
                    trips
                      WHERE 
                      datetime >= {d '",input$dates[1],"'} AND
                       datetime < {d '",input$dates[2],"'}
                        GROUP BY
                      cast(start_destination as varchar(3)) 
                       + (',') + cast(final_destination as varchar(3)),
                        convert(char(10),datetime,121);")
                   sql = sqlQuery(database, d) 


                   sql = data.frame(sql, do.call(rbind, str_split(sql$combo, ',')))
                 colnames(sql)[colnames(sql)=="X1"] <- "From"
                 colnames(sql)[colnames(sql)=="X2"] <- "To"
                 sql = sql[,c(4,5,3)]
                 sql = sql[order(sql$volume, decreasing = T),]
               chordDiagram(sql)
                   circos.clear() 

               })

             }

推荐答案

                server <- function(input, output) {

             output$plot2 = renderPlot({
           database = odbcConnect("database")
              start_date = print(input$dates[1])
              end_date = print(input$dates[2])
              my_query="SELECT 
              top 30
                        convert(char(10),datetime,121) as date, 
                         cast(start_destination as varchar(3)) 
                        + (',') + cast(final_destination as varchar(3)) as combo,
                           count(cast(start_destination as varchar(3)) 
                           + (',') + cast(final_destination as varchar(3))) as volume  
                          FROM
                          trips
                              WHERE 
                           datetime >= DATE1 AND
                           datetime < DATE2
                            GROUP BY
                             cast(start_destination as varchar(3)) 
                           + (',') + cast(final_destination as varchar(3)),
                             convert(char(10),datetime,121);"

                my_query <- sub("DATE1",as.Date(start_date),my_query);
              my_query <- sub("DATE2",as.Date(end_date),my_query)
             sql = sqlQuery(database, paste(my_query))
            sql = data.frame(sql, do.call(rbind, str_split(sql$combo, ',')))
           colnames(sql)[colnames(sql)=="X1"] <- "From"
           colnames(sql)[colnames(sql)=="X2"] <- "To"
           sql = sql[,c(4,5,3)]
            sql = sql[order(sql$volume, decreasing = T),]
            chordDiagram(sql)
             circos.clear()
               })


             }


             shinyApp(ui, server)