dbplot包学习

#sudo apt-get install sqlitebrowser sqlite3
#remotes::install_github("edgararuiz/dbplot")
library(DBI)
library(tidyverse)
library(janitor)
library(dbplot)
library(modeldb)
library(tidypredict)
# Create an ephemeral in-memory RSQLite database
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbListTables(con)
dbWriteTable(con, "flights", nycflights13::flights)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
dbReadTable(con, "flights")
res <- dbSendQuery(con, "SELECT * FROM flights")
dbFetch(res)
dbListFields(con,"flights")



flight <- tbl(con,"flights")

flight %>% dbplot_histogram(distance,binwidth = 700)+
  labs(title = "Flights - Distance traveled") +
  theme_classic()

flight %>%
  dbplot_raster(sched_dep_time, sched_arr_time)

flight %>%
  dbplot_raster(
    sched_dep_time,
    sched_arr_time,
    mean(distance, na.rm = TRUE)
  )

flight %>%
  dbplot_bar(origin)

flight %>%
  dbplot_bar(origin, mean(dep_delay))

flight %>%
  dbplot_line(month)

flight %>%
  dbplot_line(month, mean(dep_delay))

flight %>%
  dbplot_boxplot(origin, dep_delay)

flight %>%
  db_compute_bins(arr_delay)

flight %>%
  filter(arr_delay < 100 , arr_delay > -50) %>%
  db_compute_bins(arr_delay) %>%
  ggplot() +
  geom_col(aes(arr_delay, count, fill = count))

flight %>%
  group_by(x = !! db_bin(arr_delay)) %>%
  tally()

flight %>%
  filter(!is.na(arr_delay)) %>%
  group_by(x = !! db_bin(arr_delay)) %>%
  tally()%>%
  collect %>%
  ggplot() +
  geom_col(aes(x, n))

#############################################
mtcar <- tbl(con,'mtcars')
model <- lm(mpg ~ wt + cyl, data = mtcars)

#Data Modeling inside Database
mtcar %>%
  select(mpg,wt,cyl) %>%
  linear_regression_db(mpg)

km <-mtcar%>%
  simple_kmeans_db(mpg, wt)
colnames(km)

#Predict inside Database
tidypredict_sql(model, dbplyr::simulate_mssql())

pm <- parse_model(lm(wt ~ ., mtcar))
#lm(wt ~ ., mtcars)
tidy(pm)

model <- lm(mpg ~ wt + cyl * disp, offset = am, data = mtcar)
tidypredict_fit(model)
parse_model(model)