0Pricing
R Academy · Pelajaran

dbplyr: SQL melalui Sintaks dplyr

Tulis kode dplyr yang diterjemahkan ke SQL dan dijalankan di database.

dbplyr: SQL melalui Sintaks dplyr adalah pelajaran R Academy gratis di CoddyKit. Ini adalah pelajaran 3 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar R Academy, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus R Academy mencakup 4 pelajaran total.

Apa Itu dbplyr?

dbplyr adalah paket R yang secara otomatis menerjemahkan verba dplyr menjadi SQL. Alih-alih menulis SQL mentah, Anda menulis kode dplyr yang sudah dikenal, lalu dbplyr mengonversinya ke dialek SQL yang tepat untuk backend basis data Anda. Kueri dijalankan di dalam basis data — bukan di memori 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')

Menghubungkan ke Basis Data

dbplyr bekerja di atas koneksi DBI. Anda membuat koneksi dengan DBI::dbConnect() menggunakan paket pengandar yang sesuai, lalu meneruskan objek koneksi tersebut ke fungsi 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() — Merujuk ke Tabel Basis Data

tbl(con, 'table_name') membuat rujukan malas ke tabel basis data. Belum ada data yang diambil — Anda hanya mendapatkan penunjuk. Selanjutnya, Anda dapat merangkai verba dplyr ke dalamnya, dan dbplyr akan membangun kueri SQL secara bertahap.

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

Menerapkan filter() pada Tabel Basis Data

Anda dapat merangkai filter() ke rujukan tbl(), sama seperti saat menggunakannya pada bingkai data lokal. dbplyr menerjemahkannya menjadi klausa SQL WHERE. Pemfilteran berlangsung di dalam basis data — hanya baris yang cocok yang akan dikirim ke 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))

Menerapkan select() dan mutate()

select() dipetakan ke SQL SELECT, sedangkan mutate() dipetakan ke kolom terhitung dalam klausa SELECT. dbplyr menangani penerjemahan tersebut, termasuk banyak ekspresi R umum yang memiliki padanan 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() dan summarise() — Agregasi

group_by() dan summarise() diterjemahkan menjadi SQL GROUP BY dengan fungsi agregat. Dengan demikian, Anda dapat menghitung ringkasan di dalam basis data tanpa terlebih dahulu menarik semua baris ke R — hal yang sangat penting untuk tabel besar.

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() — Memeriksa SQL yang Dihasilkan

show_query() mencetak SQL yang akan dikirim dbplyr ke basis data. Fitur ini sangat berguna untuk men-debug, menyetel performa, dan mempelajari SQL dengan melihat cara kode dplyr Anda diterjemahkan.

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() — Menarik Data ke R

collect() menjalankan kueri malas dan mengambil hasilnya ke dalam bingkai data R lokal (tibble). Sebelum Anda memanggil collect(), tidak ada data yang berpindah dari basis data ke R — semua operasi diterjemahkan ke SQL dan dijalankan di sisi server.

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() — Mengirim Bingkai Data Lokal ke Basis Data

copy_to() menulis bingkai data R lokal ke dalam basis data sebagai tabel (biasanya sementara). Ini berguna untuk menggabungkan tabel pencarian lokal dengan tabel jarak jauh berukuran besar, atau untuk pengujian tanpa basis data yang sudah tersedia.

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

Menggabungkan Tabel Basis Data

Anda dapat menggabungkan dua rujukan tbl() menggunakan fungsi left_join(), inner_join(), dan sebagainya yang sama dari dplyr. dbplyr menerjemahkannya menjadi klausa SQL JOIN — penggabungan berlangsung di dalam basis data.

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

Kapan Tidak Menggunakan dbplyr

dbplyr tidak dapat menerjemahkan setiap ekspresi R ke SQL. Fungsi khusus yang kompleks, manipulasi tanggal dengan R dasar, atau fungsi statistik khusus R mungkin tidak memiliki padanan SQL. Panggil collect() terlebih dahulu untuk menarik data ke R, lalu terapkan operasi yang hanya dapat dilakukan R secara lokal.

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

Pemeriksaan Singkat

Setelah membangun rangkaian verba dplyr pada rujukan basis data tbl(), fungsi mana yang menjalankan kueri dan mengembalikan hasilnya sebagai bingkai data R lokal?

dbplyr — Hal-Hal Penting

dbplyr memungkinkan Anda melakukan kueri pada basis data dengan sintaks dplyr — tanpa perlu SQL:

  • tbl(con, 'table') — rujukan malas ke tabel basis data
  • filter(), select(), mutate() — diterjemahkan menjadi klausa SQL
  • group_by() |> summarise() — menjadi SQL GROUP BY
  • show_query() — memeriksa SQL yang dihasilkan (sangat baik untuk belajar)
  • collect() — menjalankan kueri dan menarik data ke R
  • copy_to() — mengirim bingkai data lokal ke basis data
  • Penggabungan juga berfungsi: left_join(), inner_join(), dan sebagainya
  • Lakukan pemfilteran dan agregasi di DB; gunakan R hanya untuk hal-hal yang tidak dapat dilakukan 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()

Pertanyaan yang Sering Diajukan

Apakah pelajaran “dbplyr: SQL melalui Sintaks dplyr” gratis?

Ya — teks lengkap “dbplyr: SQL melalui Sintaks dplyr” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus R Academy, upgrade ke CoddyKit PRO. Kursus R Academy mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “dbplyr: SQL melalui Sintaks dplyr”?

Tulis kode dplyr yang diterjemahkan ke SQL dan dijalankan di database. Kamu berlatih R Academy dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai R Academy?

Tidak diperlukan pengalaman sebelumnya. R Academy di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 3 dari 4.

Berapa lama pelajaran “dbplyr: SQL melalui Sintaks dplyr” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran R Academy ini?

Ya. Setiap pelajaran R Academy menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

  1. Dasar-Dasar DBI dan RSQLite
  2. Menghubungkan ke PostgreSQL dan MySQL
  3. dbplyr: SQL melalui Sintaks dplyr
  4. Kueri Berparameter dan Transaksi
← Kembali ke R Academy