0Pricing
R Academy · Lektion

dbplyr: SQL über dplyr-Syntax

Schreiben Sie dplyr-Code, der in SQL übersetzt und auf der Datenbank ausgeführt wird.

dbplyr: SQL über dplyr-Syntax ist eine kostenlose R Academy-Lektion auf CoddyKit. Dies ist Lektion 3 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des R Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der R Academy-Kurs umfasst insgesamt 4 Lektionen.

Was ist dbplyr?

dbplyr ist ein R-Paket, das dplyr-Verben automatisch in SQL übersetzt. Statt SQL direkt zu schreiben, verwenden Sie vertrauten dplyr-Code, den dbplyr in den passenden SQL-Dialekt Ihres Datenbank-Backends umwandelt. Die Abfrage wird in der Datenbank ausgeführt – nicht im R-Arbeitsspeicher.

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

Verbindung zu einer Datenbank herstellen

dbplyr baut auf einer DBI-Verbindung auf. Stellen Sie die Verbindung mit DBI::dbConnect() und dem passenden Treiberpaket her und übergeben Sie anschließend das Verbindungsobjekt an die dbplyr-Funktionen.

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() – Eine Datenbanktabelle referenzieren

tbl(con, 'table_name') erstellt eine verzögerte Referenz auf eine Datenbanktabelle. Es werden noch keine Daten abgerufen – Sie erhalten lediglich einen Verweis. Anschließend können Sie dplyr-Verben daran anreihen, und dbplyr baut die SQL-Abfrage schrittweise auf.

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() auf eine Datenbanktabelle anwenden

Sie können filter() genauso an eine tbl()-Referenz anreihen wie bei einem lokalen Dataframe. dbplyr übersetzt den Aufruf in eine SQL-WHERE-Klausel. Die Filterung erfolgt in der Datenbank – nur passende Zeilen werden an R übertragen.

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() und mutate() anwenden

select() entspricht SQL-SELECT, und mutate() entspricht berechneten Spalten in der SELECT-Klausel. dbplyr übernimmt die Übersetzung, einschließlich vieler gängiger R-Ausdrücke, für die es entsprechende SQL-Ausdrücke gibt.

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() und summarise() – Aggregation

group_by() und summarise() werden in SQL-GROUP BY mit Aggregatfunktionen übersetzt. So können Sie Zusammenfassungen in der Datenbank berechnen, ohne zunächst alle Zeilen nach R zu übertragen – besonders wichtig bei großen Tabellen.

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() – Generiertes SQL untersuchen

show_query() gibt das SQL aus, das dbplyr an die Datenbank senden wird. Das ist für das Debugging, die Leistungsoptimierung und das Erlernen von SQL äußerst nützlich, weil Sie sehen, wie Ihr dplyr-Code übersetzt wird.

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() – Daten nach R übertragen

collect() führt die verzögerte Abfrage aus und ruft die Ergebnisse als lokalen R-Dataframe (Tibble) ab. Bis zum Aufruf von collect() werden keine Daten aus der Datenbank nach R übertragen – alle Vorgänge werden in SQL übersetzt und serverseitig ausgeführt.

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() – Einen lokalen Dataframe in die DB übertragen

copy_to() schreibt einen lokalen R-Dataframe als (in der Regel temporäre) Tabelle in die Datenbank. Das ist nützlich, wenn Sie lokale Nachschlagetabellen mit großen entfernten Tabellen verknüpfen möchten oder für Tests ohne eine bereits vorhandene Datenbank.

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

Datenbanktabellen verknüpfen

Sie können zwei tbl()-Referenzen mit denselben left_join()-, inner_join()- usw. Funktionen aus dplyr verknüpfen. dbplyr übersetzt sie in SQL-JOIN-Klauseln – die Verknüpfung wird in der Datenbank ausgeführt.

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

Wann Sie dbplyr NICHT verwenden sollten

dbplyr kann nicht jeden R-Ausdruck in SQL übersetzen. Komplexe benutzerdefinierte Funktionen, die Datumsverarbeitung mit Base R oder R-spezifische statistische Funktionen haben möglicherweise kein SQL-Gegenstück. Rufen Sie zuerst collect() auf, um die Daten nach R zu übertragen, und wenden Sie anschließend die ausschließlich in R möglichen Operationen lokal an.

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

Kurzer Wissenstest

Welche Funktion führt nach dem Erstellen einer Kette von dplyr-Verben auf einer tbl()-Datenbankreferenz die Abfrage aus und gibt die Ergebnisse als lokalen R-Dataframe zurück?

dbplyr – Die wichtigsten Punkte

Mit dbplyr können Sie Datenbanken mit dplyr-Syntax abfragen – ganz ohne SQL:

  • tbl(con, 'table') – verzögerte Referenz auf eine DB-Tabelle
  • filter(), select(), mutate() – werden in SQL-Klauseln übersetzt
  • group_by() |> summarise() – wird zu SQL-GROUP BY
  • show_query() – generiertes SQL untersuchen (hervorragend zum Lernen)
  • collect() – Abfrage ausführen und Daten nach R übertragen
  • copy_to() – einen lokalen Dataframe in die Datenbank übertragen
  • Auch Joins funktionieren: left_join(), inner_join() usw.
  • Filtern und aggregieren Sie in der DB; verwenden Sie R nur für Vorgänge, die SQL nicht ausführen kann
# 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()

Häufig gestellte Fragen

Ist die Lektion „dbplyr: SQL über dplyr-Syntax“ kostenlos?

Ja — der vollständige Text von „dbplyr: SQL über dplyr-Syntax“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des R Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der R Academy-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „dbplyr: SQL über dplyr-Syntax“?

Schreiben Sie dplyr-Code, der in SQL übersetzt und auf der Datenbank ausgeführt wird. Du übst R Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um R Academy zu starten?

Keine Vorkenntnisse erforderlich. R Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 3 von 4.

Wie lange dauert die Lektion „dbplyr: SQL über dplyr-Syntax“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser R Academy-Lektion Code schreiben und ausführen?

Ja. Jede R Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

  1. Grundlagen von DBI und RSQLite
  2. Mit PostgreSQL und MySQL verbinden
  3. dbplyr: SQL über dplyr-Syntax
  4. Parametrisierte Abfragen und Transaktionen
← Zurück zu R Academy