8.7 dbplyr

dbplyr将dplyr包的函数转化为SQL语句去服务器获取数据；在数据量较大、计算较多时，可以将远程连接数据库中的表当作内存中的数据框使用，当本机内存不够大时，这样做的好处不言而喻。

至于为什么使用dbplyr而不是直接编写SQL,因为：

  • dbplyr写起来简洁高效，基本跟用dplyr没有差别

  • 能利用数据库所在服务器的算力，配合上并行计算，在处理大量数据时，大大加快速度。

  • 不同数据库的语法存在差异，当源数据存在不同数据库时，用R的dbplyr包清洗数据时能加快效率

  • 通过dplyr动词方便实现复杂的逻辑，当过程越多越复杂时dbplyr的优势越明显，不用一层层嵌套语句。

dbplyr项目介绍

8.7.1 安装

dbplyr包是tidyverse系列核心包之一，可以通过安装tidyverse或仅安装dbplyr。

install.packages("tidyverse")

# 从CRAN安装发行版
install.packages("dbplyr")

# 开发版
library(devtools)
devtools::install_github("tidyverse/dbplyr")

8.7.2 基础用法

library(dplyr)
library(dbplyr)

mf <- memdb_frame(x = 1, y = 2)

mf %>% 
  mutate(
    a = y * x, 
    b = a ^ 2,
  ) %>% 
  show_query()
library(dplyr)
#connect database
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
# 上传数据
copy_to(con, nycflights13::flights, "flights",
  temporary = FALSE, 
  indexes = list(
    c("year", "month", "day"), 
    "carrier", 
    "tailnum",
    "dest"
  )
)

# 查看库中全部表名
#dbListTables(con)

#tbl()引用表flights

flights_db <- tbl(con, "flights")
flights_db

# 开始查询
flights_db %>% select(year:day, dep_delay, arr_delay)
flights_db %>% filter(dep_delay > 240)
flights_db %>% 
  group_by(dest) %>%
  summarise(delay = mean(dep_time))

部分简单不复杂的sql语句可以用dplyr的语法代替.

tailnum_delay_db <- flights_db %>% 
  group_by(tailnum) %>%
  summarise(
    delay = mean(arr_delay,na.rm = T),
    n = n()
  ) %>% 
  arrange(desc(delay)) %>%
  filter(n > 100)
tailnum_delay_db
tailnum_delay_db %>% show_query()
tailnum_delay <- tailnum_delay_db %>% collect() #把数据从数据库加载到R内存中

8.7.3 无法正确转化

在使用过程中发现无法识别lubridate包的函数，但是dbplyr对于不认识的函数都将保留。利用这个特性，可以使用数据库中原生的相关函数：如下所示，在Oracle中to_date函数。

以下的自定义函数可以实现按照想要group_by的字段汇总金额、数量、吊牌额、折扣率等,其中关于时间周期的筛选就利用了该特性。

  • date
#个人写的针对目前公司数仓写的包中获取销售数据的一段代码

get_sales_data <- function(con,...,start_date,end_date,brand_name,channel_type = NULL ,area_name = NULL,boss_name = NULL,category_name = NULL,shop_no = NULL){

  store_table <- store(con,brand_name = brand_name,channel_type = channel_type ,area_name = area_name,boss_name = boss_name,shop_no = shop_no) #门店信息
  
  sku_table <- sku(con,category_name =  category_name ) #商品信息
  
  tbl(con, in_schema("DW", "DW_SALE_SHOP_F")) %>% #DW层
    select(BILL_DATE1, SKU_NO, SHOP_NO, BILL_QTY, BILL_MONEY2, PRICE) %>%
    filter(between(
      BILL_DATE1, to_date(start_date, "yyyy-mm-dd"),
      to_date(end_date, "yyyy-mm-dd")
    )) %>%
    mutate(年 = year(BILL_DATE1), 月 = month(BILL_DATE1)) %>%
    inner_join(store_table) %>%
    inner_join(sku_table) %>%
    group_by(...) %>%
    summarise(
      金额 = sum(BILL_MONEY2, na.rm = TRUE),
      数量 = sum(BILL_QTY, na.rm = TRUE),
      吊牌金额 = sum(BILL_QTY * PRICE, na.rm = TRUE)) %>%
    collect() %>%
    mutate(折扣率:= 金额 / 吊牌金额) %>% 
    arrange(...)

  # return(res)
}
  • like
mf %>% 
  filter(x %LIKE% "%foo%") %>% 
  show_query()
  • 特殊用法

特殊情况可以使用sql()函数

mf %>% 
  transmute(factorial = sql("x!")) %>% 
  show_query()