转载请注明来源
https://www.jianshu.com/p/c8bef3a0a50f
前言
dbplyr相比传统数据库操作的优势：
① 可使用dplyr语句操作数据库中的表
② 可直接将dplyr语句转换为SQL语句
③ 基于数据库底层的虚拟tbl类操作，几乎不占用本地计算资源
github 项目地址：
https://github.com/tidyverse/dbplyr
1.加载必要包
#需先安装pacman包
> pacman::p_load(RPostgreSQL,
dplyr,
dbplyr,
stringr)
这里stringr包是用于处理字符串而加载的，如不需可删除。
2.连接数据库
> pgdriver<-dbDriver("PostgreSQL")
con <- dbConnect(pgdriver,
host="数据库ip地址",
port="访问端口",
dbname="数据库名称",
user="用户名",
password="密码")
这里以Postgresql为例
3.查看该数据库中表名
> dbListTables(con)
......
[109] "stock20191227" "dbplyr_data"
这里获取到我预先写入的表："dbplyr_data"
4.将表"dbplyr_data"定义为dbplyr兼容的tbl数据库底层操作格式
> data <- tbl(con, "dbplyr_data")
> class(data)
[1] "tbl_PostgreSQLConnection" "tbl_dbi"
[3] "tbl_sql" "tbl_lazy"
[5] "tbl"
这里可以看到data已经变成tbl格式，模式为PostgreSQLConnection
接着我们print一下data看输出
> print(data)
# Source: table<dbplyr_data> [?? x 12]
# Database: postgres 10.0.11
# [postgres@106.54.32.120:5432/item_info_base]
row.names id total_sales_pri~ total_sales_qua~ click_count
<chr> <chr> <int> <int> <int>
1 1 2152 25464 2 191
2 2 2157 26278 1 42
3 3 1582 0 0 17
4 4 725 0 0 17
5 5 1610 0 0 16
6 6 2117x 0 0 15
7 7 984 0 0 15
8 8 2151 0 0 13
9 9 2051 0 0 13
10 10 1774 0 0 13
# ... with more rows, and 7 more variables:
# visitor_count <int>, conversion_rate <dbl>,
# unit_price <int>, order_count <int>, review_points <dbl>,
# review_count <int>, date <date>
这是一个类似tibble类型的数据框
*同时对于data.frame的统计函数对其不再适用，该类型只接受dplyr函数
例：查看变量名
> names(data)
[1] "src" "ops"
可以用：
> data$ops$vars
[1] "row.names" "id" "total_sales_price"
[4] "total_sales_quantity" "click_count" "visitor_count"
[7] "conversion_rate" "unit_price" "order_count"
[10] "review_points" "review_count" "date"
下面看下从dplyr代码传回的数据
> data %>%
group_by(id) %>%
filter(!str_detect(id, "ss_")) %>%
summarise(sale_n = sum(order_count, na.rm = T)) %>%
arrange(desc(sale_n)) -> info
1.以“id”列进行分组
2.筛选“id”列不包含“ss_”字符的行
3.计算各“id”组下“order_count”之和，计算结果传入新建
列“sale_n”，并忽略缺失值
4.根据“sale_n”的值倒序排序
5.最后赋值到info
> print(info)
# Source: lazy query [?? x 2]
# Database: postgres 10.0.11
# [postgres@106.54.32.120:5432/item_info_base]
# Ordered by: desc(sale_n)
id sale_n
<chr> <dbl>
1 2053 75
2 2152 53
3 2151 53
4 1698 45
5 1582 38
6 1660 29
7 1314 28
8 2157 25
9 907 24
10 2104 22
# ... with more rows
> class(info)
[1] "tbl_PostgreSQLConnection" "tbl_dbi"
[3] "tbl_sql" "tbl_lazy"
[5] "tbl"
返回数据类型仍然为tbl不变
下面将dplyr代码转译成SQL代码
> sql_code <- sql_render(info)
> print(sql_code)
<SQL> SELECT "id", SUM("order_count") AS "sale_n"
FROM "dbplyr_data"
WHERE (NOT(STRPOS("id", 'ss_') > 0))
GROUP BY "id"
ORDER BY "sale_n" DESC
由于上述SQL代码已经赋值在变量sql_code内，可以直接使用dbGetQuery函数将数据从数据库导入到R内存中
> real_data <- dbGetQuery(con, sql_code)
> head(real_data)
id sale_n
1 2053 75
2 2151 53
3 2152 53
4 1698 45
5 1582 38
6 1660 29
> class(real_data)
[1] "data.frame"
我们看到得到的是实际数据，类型为data.frame
我们再比较一下info与real_data的实际内存占用
> object.size(info)
13192 bytes
> object.size(real_data)
110272 bytes
由于实际数据内存占用也较低，比较不明显
下面将实际data.frame下载下来与tbl类虚拟数据框data比较
> real_frame <- dbGetQuery(con, "select * from dbplyr_data")
> object.size(real_frame)
9244240 bytes
> object.size(data)
4464 bytes
可以看出，利用虚拟tbl类型能几乎不占用计算资源进行数据操作
#养成良好习惯，关闭数据库连接
> dbDisconnect(con)
[1] TRUE
最后
不得不感慨一下dbplyr给R用户带来对数据库操作的极简设计
