问题描述
我正在学习使用闪亮的仪表板,并使用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)