r-如何将RpostgreSQL查询设置为RpivotTable的数据帧对象

Question

我正试图使用RpostgreSQL来获取数据，这些数据将被输入rpivotTable包，并使用shine显示给用户。

错误：数据应该是data.frame、data.table或表

不幸的是，我是R的新手，我无法解决如何将postgreSQL查询设置为数据帧、表或data.table，以便创建rpivotTable。

library(shiny)
library(DBI)
library(RPostgreSQL)
library(DT)
library(plotly)
library(rjson)
library(pool)
library(dplyr)
library(dbplyr)
library(rpivotTable)

ui <- fluidPage(

##DEBUGGING##
tableOutput("tbl"),
rpivotTable(Titanic),
### sql & rpivottable attempts ###
rpivotTable("OverallPivot"),
rpivotTableOutput("tbl2"),

output$pivtbl2 <- renderRpivotTable(rpivotTable(data = DataSet,
aggregatorName = "Sum",
vals = "Count",
cols = "order_date",
rows = "product_id",
menuLimit = 1200,
rendererName = "Line Chart"))
)

server <- function(input, output, session) {

###DEBUGGING SERVER
output$tbl <- renderTable({
conn <- dbConnect(
drv = dbDriver("PostgreSQL"),
dbname = "store",
host = "localhost",
user = "postgres",
password = "123456")
on.exit(dbDisconnect(conn), add = TRUE)
dbGetQuery(conn, paste0(
"SELECT * FROM orders;"))
})
###Attempted sql & rpivotTable attempts SERVER
OverallPivot <- renderRpivotTable({    conn <- dbConnect(
drv = dbDriver("PostgreSQL"),
dbname = "store",
host = "localhost",
user = "postgres",
password = "123456")
on.exit(dbDisconnect(conn), add = TRUE)
dbGetQuery(conn, paste0(
"SELECT * FROM orders;"))
})
DataSet <- renderRpivotTable({    conn <- dbConnect(
drv = dbDriver("PostgreSQL"),
dbname = "store",
host = "localhost",
user = "postgres",
password = "123456")
on.exit(dbDisconnect(conn), add = TRUE)
dbGetQuery(conn, paste0(
"SELECT * FROM orders;"))
})

tbl2 <- renderRpivotTable({
conn <- dbConnect(
drv = dbDriver("PostgreSQL"),
dbname = "store",
host = "localhost",
user = "postgres",
password = "123456")
on.exit(dbDisconnect(conn), add = TRUE)
dbGetQuery(conn, paste0(
"SELECT * FROM orders;"))
})
}

shinyApp(ui, server)

我能够以"tbl"格式显示postgres-sql中的表，并以R格式显示使用Titanic数据帧的透视表。

我只是不知道如何将两者结合在一起，并使用postgres-sql查询在shine中显示rpivottable。

我在调试部分分为我的成功例子和我的失败尝试

谢谢你的帮助！

Answer 1

您需要在renderRpivotTable函数之外创建R对象。

服务器.R

library(shiny)
library(rpivotTable)
library(RPostgreSQL)
function(input, output, session) {
conn <- dbConnect(
drv = dbDriver("PostgreSQL"),
dbname = "store",
host = "localhost",
user = "postgres",
password = "123456")
on.exit(dbDisconnect(conn), add = TRUE)
db_data <- dbGetQuery(conn, paste0("SELECT * FROM orders;"))
output$pivot <- renderRpivotTable({
rpivotTable(data = db_data)
})
}

ui.R

library(shiny)
library(rpivotTable)
test_page <- fluidPage(
rpivotTableOutput('pivot')
)
test_page