将SQL输入日期范围传递到SHINY中的查询中 [英] Passing a SQL input date range into a query in SHINY

查看:0
本文介绍了将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() 

}
    })

   }
根据NJburgo的建议将编辑内容上传到服务器 # #我收到错误:不知道如何将输入$DATES转换为课程日期

                       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)

这篇关于将SQL输入日期范围传递到SHINY中的查询中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆