我正在尝试使用BigQuery查询来填充Shiny中的图。该查询包括使用selectInput来自ui的输入值。如果用户选择数据库中存在的值,例如year是2014,则查询可以正常工作,但是,我希望用户也能够选择“全部”。“全部”应该是所有值的选择,但是,我不确定如何在使用selectInput的查询中表示该值。
服务器
data1 <- eventReactive(input$do_sql, {
bqr_auth(token = NULL, new_user = FALSE, verbose = FALSE)
query = paste('select month, event, partner_name, sum(f0_) from [dataset.table] where year =',input$year1,' and partner_name = \"',input$partner_name,'\"
GROUP by 1,2,3
ORDER by 1 asc
LIMIT 10000', sep="")
bqr_query(projectId, datasetId, query, maxResults =2000)
})
用户界面
(
selectInput("year1",
"Year:",
c("All",2014,2015
))
),
(
selectInput("partner_name",
"Partner:",
c("All",
unique(as.character(data5$partner_name))))
您应该稍微更改正在构造的查询,
因此,当前
SELECT month, event, partner_name, SUM(f0_)
FROM [dataset.table]
WHERE year = selected_year
AND partner_name = "selected_partner_name"
GROUP BY 1,2,3
ORDER BY 1 ASC
LIMIT 10000
与分别:
selected_year --> input$year1
selected_partner_name --> input$partner_name
相反,您应该构造以下查询
SELECT month, event, partner_name, SUM(f0_)
FROM [dataset.table]
WHERE (year = selected_year OR "selected_year" = "All")
AND (partner_name = "selected_partner_name" OR "selected_partner_name" = "All")
GROUP BY 1,2,3
ORDER BY 1 ASC
LIMIT 10000
我根本不是个闪亮的用户-请原谅我的语法-以下只是我对实现上述建议的猜测
query = paste('SELECT month, event, partner_name, sum(f0_)
FROM [dataset.table]
WHERE (year =',input$year1,' OR "All" ="',input$year1,'")
AND (partner_name = \"',input$partner_name,'\" OR "All" = \"',input$partner_name,'\")
GROUP by 1,2,3
ORDER by 1 asc
LIMIT 10000', sep="")
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句