dbplyr：dplyr構文でSQLを扱う
SQLに変換されてデータベース上で実行されるdplyrコードを書きます。
「dbplyr：dplyr構文でSQLを扱う」はCoddyKit上の無料R Academyレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはR Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 R Academyコースには全4レッスンが含まれています。
dbplyrとは何か
dbplyrは、dplyrの動詞を自動的にSQLへ変換するRパッケージです。生の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 cylcollect() — データを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() — ローカルのデータフレームをDBに送る
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()などの関数を使って、2つの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を使うと、SQLを直接書かずにdplyrの構文でデータベースをクエリできます。
tbl(con, 'table')— DBテーブルへの遅延参照filter()、select()、mutate()— SQLの句に変換されるgroup_by() |> summarise()— SQLのGROUP BYになるshow_query()— 生成されたSQLを確認する（学習にも最適）collect()— クエリを実行してデータをRに取得するcopy_to()— ローカルのデータフレームをデータベースに送る- 結合も利用できる：
left_join()、inner_join()など - DB内でフィルタリングと集約を行い、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()よくある質問
「dbplyr：dplyr構文でSQLを扱う」レッスンは無料ですか？
はい。「dbplyr：dplyr構文でSQLを扱う」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、R Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 R Academyコースには全4レッスンが含まれています。
「dbplyr：dplyr構文でSQLを扱う」で何を学びますか？
SQLに変換されてデータベース上で実行されるdplyrコードを書きます。 ブラウザで直接実行するハンズオンコードでR Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
R Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのR Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。
「dbplyr：dplyr構文でSQLを扱う」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このR Academyレッスンでコードを書いて実行できますか？
はい。すべてのR Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- DBIとRSQLiteの基礎
- PostgreSQLとMySQLへの接続
- dbplyr：dplyr構文でSQLを扱う
- パラメーター化クエリとトランザクション