R Academy · Lekcja

dbplyr: SQL za pomocą składni dplyr

Pisz kod dplyr, który jest tłumaczony na SQL i wykonywany w bazie danych.

dbplyr: SQL za pomocą składni dplyr to bezpłatna lekcja R Academy na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej R Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs R Academy zawiera 4 lekcji w sumie.

Czym jest dbplyr?

dbplyr to pakiet R, który automatycznie tłumaczy czasowniki dplyr na język SQL. Zamiast pisać surowe zapytania SQL, pisze się znajomy kod dplyr, a dbplyr konwertuje go na właściwy dialekt SQL dla używanego backendu bazy danych. Zapytanie jest wykonywane w bazie danych, a nie w pamięci 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')

Nawiązywanie połączenia z bazą danych

dbplyr działa na połączeniu DBI. Połączenie należy utworzyć za pomocą DBI::dbConnect(), używając odpowiedniego pakietu sterownika, a następnie przekazać obiekt tego połączenia do funkcji 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() — odwołanie do tabeli bazy danych

tbl(con, 'table_name') tworzy leniwe odwołanie do tabeli bazy danych. Dane nie są jeszcze pobierane — otrzymuje się jedynie wskaźnik. Następnie można łączyć z nim czasowniki dplyr, a dbplyr będzie stopniowo budować zapytanie 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')

Zastosowanie filter() do tabeli bazy danych

Do odwołania tbl() można dołączyć filter() tak samo jak w przypadku lokalnej ramki danych. dbplyr tłumaczy tę operację na klauzulę SQL WHERE. Filtrowanie odbywa się w bazie danych — do R zostaną przesłane tylko pasujące wiersze.

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

Zastosowanie select() i mutate()

select() odpowiada instrukcji SQL SELECT, a mutate() — kolumnom obliczanym w klauzuli SELECT. dbplyr obsługuje tłumaczenie, w tym wiele typowych wyrażeń R, które mają odpowiedniki w 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() i summarise() — agregacja

group_by() i summarise() są tłumaczone na instrukcję SQL GROUP BY z funkcjami agregującymi. Umożliwia to obliczanie podsumowań w bazie danych bez wcześniejszego pobierania wszystkich wierszy do R — ma to kluczowe znaczenie w przypadku dużych tabel.

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() — wyświetlanie wygenerowanego kodu SQL

show_query() wyświetla kod SQL, który dbplyr prześle do bazy danych. Jest to niezwykle przydatne podczas debugowania, dostrajania wydajności i nauki SQL, ponieważ pokazuje, jak kod dplyr jest tłumaczony.

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() — pobieranie danych do R

collect() wykonuje leniwe zapytanie i pobiera wyniki do lokalnej ramki danych R (tibble). Dopóki nie zostanie wywołane collect(), żadne dane nie są przesyłane z bazy danych do R — wszystkie operacje są tłumaczone na SQL i wykonywane po stronie serwera.

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() — przesyłanie lokalnej ramki danych do bazy

copy_to() zapisuje lokalną ramkę danych R w bazie danych jako (zwykle tymczasową) tabelę. Jest to przydatne podczas łączenia lokalnych tabel słownikowych z dużymi tabelami zdalnymi lub podczas testowania bez wcześniej utworzonej bazy danych.

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

Łączenie tabel bazy danych

Dwa odwołania tbl() można łączyć za pomocą tych samych funkcji left_join(), inner_join() itd. z pakietu dplyr. dbplyr tłumaczy je na klauzule SQL JOIN — łączenie jest wykonywane w bazie danych.

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

Kiedy NIE używać dbplyr

dbplyr nie potrafi przetłumaczyć każdego wyrażenia R na SQL. Złożone funkcje niestandardowe, operacje na datach w podstawowym R lub funkcje statystyczne specyficzne dla R mogą nie mieć odpowiedników w SQL. Najpierw należy użyć collect(), aby pobrać dane do R, a następnie wykonać lokalnie operacje dostępne wyłącznie w 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

Szybkie sprawdzenie

Po zbudowaniu łańcucha czasowników dplyr na odwołaniu tbl() do bazy danych, która funkcja wykonuje zapytanie i zwraca wyniki jako lokalną ramkę danych R?

dbplyr — najważniejsze informacje

dbplyr umożliwia odpytywanie baz danych za pomocą składni dplyr — bez konieczności pisania SQL:

tbl(con, 'table') — leniwe odwołanie do tabeli w bazie danych
filter(), select(), mutate() — tłumaczone na klauzule SQL
group_by() |> summarise() — staje się instrukcją SQL GROUP BY
show_query() — wyświetlanie wygenerowanego kodu SQL (świetne do nauki)
collect() — wykonanie zapytania i pobranie danych do R
copy_to() — przesłanie lokalnej ramki danych do bazy danych
Łączenia również działają: left_join(), inner_join() itd.
Filtrowanie i agregowanie należy wykonywać w bazie danych, a R używać tylko do operacji, których SQL nie potrafi wykonać

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

Często zadawane pytania

Czy lekcja „dbplyr: SQL za pomocą składni dplyr” jest bezpłatna?

Tak — pełny tekst „dbplyr: SQL za pomocą składni dplyr” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu R Academy, przejdź na CoddyKit PRO. Kurs R Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „dbplyr: SQL za pomocą składni dplyr”?

Pisz kod dplyr, który jest tłumaczony na SQL i wykonywany w bazie danych. Ćwiczysz R Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć R Academy?

Nie wymagamy żadnego doświadczenia. R Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.

Ile czasu zajmuje lekcja „dbplyr: SQL za pomocą składni dplyr”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji R Academy?

Tak. Każda lekcja R Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

← Powrót do R Academy