本文介紹了將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中的查詢中的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
