0Pricing
R Academy · Урок

dbplyr: SQL через синтаксис dplyr

Пишите код dplyr, который преобразуется в SQL и выполняется в базе данных.

«dbplyr: SQL через синтаксис dplyr» — бесплатный урок R Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения R Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс R Academy содержит 4 уроков всего.

Что такое dbplyr

dbplyr — это пакет R, который автоматически преобразует глаголы 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 выполняет преобразование, включая многие распространенные выражения R, для которых существуют эквиваленты в SQL.

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() выводит SQL-код, который dbplyr отправит в базу данных. Это очень полезно для отладки, оптимизации производительности и изучения SQL: Вы видите, как код dplyr преобразуется в запрос.

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())

Объединение таблиц базы данных

Две ссылки tbl() можно объединить с помощью тех же функций left_join(), inner_join() и других из dplyr. 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 не может преобразовать в SQL любое выражение R. Для сложных пользовательских функций, стандартных операций 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

Быстрая проверка

После построения цепочки глаголов dplyr для ссылки tbl() на базу данных какая функция выполняет запрос и возвращает результаты в виде локального кадра данных 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() и другие
  • Выполняйте фильтрацию и агрегацию в базе данных, а R используйте только для операций, которые SQL выполнить не может
# 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()

Часто задаваемые вопросы

Урок «dbplyr: SQL через синтаксис dplyr» бесплатный?

Да — полный текст урока «dbplyr: SQL через синтаксис dplyr» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс R Academy, подпишись на CoddyKit PRO. Курс R Academy содержит 4 уроков всего.

Чему я научусь в уроке «dbplyr: SQL через синтаксис dplyr»?

Пишите код dplyr, который преобразуется в SQL и выполняется в базе данных. Ты практикуешь R Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать R Academy?

Предыдущий опыт не требуется. R Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «dbplyr: SQL через синтаксис dplyr»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке R Academy?

Да. Каждый урок R Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Основы DBI и RSQLite
  2. Подключение к PostgreSQL и MySQL
  3. dbplyr: SQL через синтаксис dplyr
  4. Параметризованные запросы и транзакции
← Назад к R Academy