本文介紹了將SQL輸入日期范圍傳遞到SHINY中的查詢中的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！
問(wèn)題描述
我正在學(xué)習(xí)使用閃亮的儀表板，并使用SQL數(shù)據(jù)庫(kù)SQL服務(wù)器，我希望在其中直接從數(shù)據(jù)庫(kù)中提取數(shù)據(jù)。其基本思想是合并到表中具有日期范圍的起始點(diǎn)和終止點(diǎn)的列，將它們制表，然后用圖表表示它們。
我發(fā)現(xiàn)以下帖子是關(guān)于如何將SQL輸入語(yǔ)句傳遞到SHILY中的：
How to pass input variable to SQL statement in R shiny?
不幸的是，當(dāng)我嘗試并應(yīng)用這一點(diǎn)時(shí)，我收到一個(gè)錯(cuò)誤：下標(biāo)超出范圍；看起來(lái)查詢沒(méi)有被拉入。我已經(jīng)單獨(dú)測(cè)試了它，并且能夠拉出數(shù)據(jù)并運(yùn)行每一步，沒(méi)有問(wèn)題。我正在使用RODBC包，我想知道這是否是問(wèn)題所在。以下是我的代碼：
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)
我確信這是一些愚蠢的錯(cuò)誤、一個(gè)漏掉的引號(hào)或我對(duì)如何應(yīng)用這些技術(shù)的誤解。感謝您的幫助！！
##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()
}
})
}
根據(jù)NJburgo的建議將編輯內(nèi)容上傳到服務(wù)器
#
#我收到錯(cuò)誤：不知道如何將輸入$DATES轉(zhuǎn)換為課程日期
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()
})
}
推薦答案
#它需要你們所有人的兩個(gè)建議的組合，確保打印日期并進(jìn)行轉(zhuǎn)換。謝謝大家！下面是工作代碼
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)
這篇關(guān)于將SQL輸入日期范圍傳遞到SHINY中的查詢中的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
