PricingLoginStart free
R Academy · 课时

dbplyr：使用 dplyr 语法执行 SQL

编写可转换为 SQL 并在数据库中运行的 dplyr 代码

第 3 / 4 课13 个步骤

dbplyr：使用 dplyr 语法执行 SQL 是 CoddyKit 上的免费 R Academy 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 R Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 R Academy 课程共包含 4 节课。

什么是 dbplyr？

dbplyr 是一个 R packages，可以自动将 dplyr 动词转换为 SQL。您无需编写原始 SQL，而是编写熟悉的 dplyr 代码，再由 dbplyr 将其转换为适合数据库后端的正确 SQL 方言。查询会在数据库中运行，而不是在 R 内存中运行。

# install.packages(c('dbplyr', 'DBI', 'RSQLite'))
library(DBI)
library(dbplyr)
library(dplyr)

# dbplyr sits between dplyr and your database:
# Your dplyr code -> dbplyr -> SQL -> Database -> result

# Supported backends: PostgreSQL, MySQL, SQLite,
#                     SQL Server, BigQuery, Snowflake, ...

cat('dbplyr translates dplyr to SQL')

连接到数据库

dbplyr 构建在 DBI 连接之上。您可以使用相应的驱动程序包通过 DBI::dbConnect() 建立连接，然后将该连接对象传递给 dbplyr 函数。

library(DBI)
library(dplyr)
library(dbplyr)

# SQLite example (no server needed — great for demos)
con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Write a test table to the in-memory database
copy_to(con, nycflights13::flights, 'flights',
        temporary = FALSE, overwrite = TRUE)

# PostgreSQL example (real server):
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'db.example.com',
#   dbname   = 'analytics',
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS')
# )

cat('Connected to database')

tbl()——引用数据库表

tbl(con, 'table_name') 会创建对数据库表的惰性引用。此时还不会获取数据——您只是得到了一个指针。之后，您可以在其上连续使用 dplyr 动词，dbplyr 会逐步构建 SQL 查询。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
mtcars_df <- mtcars
dbWriteTable(con, 'cars', mtcars_df)

# Create a lazy table reference
cars_tbl <- tbl(con, 'cars')

# Printing shows top rows and indicates it is a database source
print(cars_tbl)
# Source: table<cars> [?? x 11]
# Database: sqlite 3.x [:memory:]

cat('tbl() = lazy reference, no data fetched yet')

对数据库表应用 filter()

您可以像操作本地数据框一样，在 tbl() 引用上连续使用 filter()。dbplyr 会将其转换为 SQL 的 WHERE 子句。筛选操作在数据库中进行——只有匹配的行会被发送到 R。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Filter in the database (generates WHERE clause)
high_mpg <- cars_tbl |>
  filter(mpg > 25, cyl == 4)

# Still lazy! No data fetched yet.
cat('Class:', class(high_mpg)[1], '\n')

# Collect to pull data into R:
result <- collect(high_mpg)
cat('Rows matching filter:', nrow(result))

应用 select() 和 mutate()

select() 对应 SQL 的 SELECT，而 mutate() 对应 SELECT 子句中的计算列。dbplyr 会处理转换，包括许多在 SQL 中有对应写法的常见 R 表达式。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Select specific columns + add a computed column
result <- cars_tbl |>
  filter(am == 1) |>              # manual transmission
  select(mpg, cyl, hp, wt) |>    # pick columns
  mutate(wt_kg = wt * 453.592)   # add computed column

# Pull into R
df <- collect(result)
cat('Columns:', names(df), '\n')
cat('Rows:', nrow(df))

group_by() 和 summarise()——聚合

group_by() 和 summarise() 会转换为带聚合函数的 SQL GROUP BY。这样，您无需先将所有行提取到 R 中，就可以在数据库中计算汇总结果；对于大型表来说，这一点至关重要。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Aggregate in the database
summary_tbl <- cars_tbl |>
  group_by(cyl) |>
  summarise(
    avg_mpg  = mean(mpg, na.rm = TRUE),
    max_hp   = max(hp),
    n_models = n()
  )

result <- collect(summary_tbl)
print(result)

show_query()——查看生成的 SQL

show_query() 会打印 dbplyr 将发送到数据库的 SQL。这对于调试、性能调优以及通过查看 dplyr 代码的转换结果来学习 SQL 都非常有帮助。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Build a query
q <- cars_tbl |>
  filter(mpg > 20) |>
  group_by(cyl) |>
  summarise(avg_mpg = mean(mpg, na.rm = TRUE))

# See the SQL dbplyr generated:
show_query(q)
# <SQL>
# SELECT cyl, AVG(mpg) AS avg_mpg
# FROM cars
# WHERE mpg > 20.0
# GROUP BY cyl

collect()——将数据提取到 R 中

collect() 会执行惰性查询，并将结果提取到本地 R 数据框（tibble）中。在调用 collect() 之前，不会有数据从数据库传输到 R；所有操作都会转换为 SQL，并在服务器端运行。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Build up a lazy query chain
lazy_q <- cars_tbl |>
  filter(hp > 100) |>
  select(mpg, hp, cyl) |>
  arrange(desc(hp))

# Nothing fetched yet
cat('Is lazy?', inherits(lazy_q, 'tbl_sql'), '\n')

# NOW pull data into R
local_df <- collect(lazy_q)
cat('Class after collect:', class(local_df)[1], '\n')
cat('Rows:', nrow(local_df))

copy_to()——将本地数据框推送到数据库

copy_to() 会将本地 R 数据框写入数据库，作为一个（通常是临时的）表。这对于将本地查找表与大型远程表连接，或在没有预先存在的数据库时进行测试非常有用。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Push a local data frame into the database
local_df <- data.frame(
  cyl       = c(4, 6, 8),
  category  = c('efficient', 'balanced', 'powerful')
)

# copy_to creates a temporary table in the DB
copy_to(con, local_df, name = 'cyl_labels', temporary = TRUE)

# Now reference it with tbl()
labels_tbl <- tbl(con, 'cyl_labels')
cat('Rows in DB table:', collect(labels_tbl) |> nrow())

连接数据库表

您可以使用 dplyr 中相同的 left_join()、inner_join() 等函数，连接两个 tbl() 引用。dbplyr 会将它们转换为 SQL 的 JOIN 子句，并在数据库中执行连接。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
copy_to(con, data.frame(cyl = c(4,6,8),
                         label = c('four','six','eight')),
        'cyl_ref', temporary = TRUE)

cars_tbl  <- tbl(con, 'cars')
labels_tbl <- tbl(con, 'cyl_ref')

# Join in the database
joined <- left_join(cars_tbl, labels_tbl, by = 'cyl') |>
  select(mpg, cyl, label, hp)

show_query(joined)   # See the SQL JOIN
result <- collect(joined)
cat('Joined rows:', nrow(result))

不应使用 dbplyr 的情况

dbplyr 无法将每个 R 表达式都转换为 SQL。复杂的自定义函数、基础 R 日期处理或 R 特有的统计函数可能没有对应的 SQL 写法。请先使用 collect() 将数据提取到 R 中，再在本地执行只能由 R 完成的操作。

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Do as much as possible in the database:
prepped <- cars_tbl |>
  filter(mpg > 15) |>
  select(mpg, hp, cyl) |>
  collect()               # <- pull only what you need

# Now apply R-only operations locally:
cor_result <- cor(prepped$mpg, prepped$hp)
cat('Correlation mpg~hp:', round(cor_result, 3))

# Rule: filter and aggregate in DB, model in R

快速检查

在 tbl() 数据库引用上连续使用一系列 dplyr 动词后，哪个函数会执行查询，并将结果作为本地 R 数据框返回？

在交互式课程中回答

dbplyr——要点

dbplyr 让您可以使用 dplyr 语法查询数据库，而无需编写 SQL：

  • tbl(con, 'table')——对数据库表的惰性引用
  • filter()、select()、mutate()——转换为 SQL 子句
  • group_by() |> summarise()——转换为 SQL GROUP BY
  • show_query()——查看生成的 SQL（非常适合学习）
  • collect()——执行查询并将数据提取到 R 中
  • copy_to()——将本地数据框推送到数据库
  • 连接操作同样有效：left_join()、inner_join() 等
  • 在数据库中进行筛选和聚合；只有 SQL 无法完成的操作才使用 R
# Complete dbplyr workflow example:
library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'sales', data.frame(
  region  = c('North','South','North','East','South'),
  revenue = c(100, 200, 150, 300, 250),
  year    = c(2023, 2023, 2024, 2024, 2024)
))

tbl(con, 'sales') |>
  filter(year == 2024) |>
  group_by(region) |>
  summarise(total = sum(revenue, na.rm = TRUE)) |>
  arrange(desc(total)) |>
  collect() |>
  print()
免费开始

用 AI 导师学习 R — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程
43
课程
159
免费开始学习查看完整课程

常见问题解答

「dbplyr：使用 dplyr 语法执行 SQL」课时是免费的吗？

是的 — 「dbplyr：使用 dplyr 语法执行 SQL」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 R Academy 课程的其余内容，请升级到 CoddyKit PRO。 R Academy 课程共包含 4 节课。

「dbplyr：使用 dplyr 语法执行 SQL」这节课中我会学到什么？

编写可转换为 SQL 并在数据库中运行的 dplyr 代码 你通过在浏览器中直接运行的动手代码来练习 R Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 R Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 R Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

「dbplyr：使用 dplyr 语法执行 SQL」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 R Academy 课中编写并运行代码吗？

能。每节 R Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. DBI 与 RSQLite 基础
  2. 连接 PostgreSQL 与 MySQL
  3. dbplyr：使用 dplyr 语法执行 SQL
  4. 参数化查询与事务
← 返回 R Academy