R Academy · Lektion

dbplyr: SQL via dplyr-syntax

Skriv dplyr-kod som översätts till SQL och körs i databasen.

Lektion 3 av 413 steg

dbplyr: SQL via dplyr-syntax är en gratis lektion i R Academy på CoddyKit. Detta är lektion 3 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för R Academy, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i R Academy innehåller totalt 4 lektioner.

Vad är dbplyr?

dbplyr är ett R-paket som automatiskt översätter verb från dplyr till SQL. I stället för att skriva SQL direkt skriver du välbekant dplyr-kod, som dbplyr omvandlar till rätt SQL-dialekt för din databasmotor. Frågan körs i databasen – inte i R:s minne.

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

Ansluta till en databas

dbplyr bygger ovanpå en DBI-anslutning. Du upprättar anslutningen med DBI::dbConnect() och lämpligt drivrutinspaket och skickar sedan anslutningsobjektet till dbplyr-funktionerna.

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() – Referera till en databastabell

tbl(con, 'table_name') skapar en lat referens till en databastabell. Inga data hämtas ännu – du får bara en pekare. Därefter kan du kedja dplyr-verb till den, så bygger dbplyr stegvis upp SQL-frågan.

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

Använda filter() på en databastabell

Du kan kedja filter() till en tbl()-referens på samma sätt som med en lokal data frame. dbplyr översätter den till en SQL-sats med WHERE. Filtreringen sker i databasen – endast matchande rader skickas till 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))

Använda select() och mutate()

select() motsvarar SQL-SELECT, och mutate() motsvarar beräknade kolumner i SELECT-satsen. dbplyr hanterar översättningen, inklusive många vanliga R-uttryck som har motsvarigheter i 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() och summarise() – Aggregering

group_by() och summarise() översätts till SQL-GROUP BY med aggregatfunktioner. På så sätt kan du beräkna sammanfattningar i databasen utan att först hämta alla rader till R – något som är avgörande för stora tabeller.

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() – Granska genererad SQL

show_query() skriver ut den SQL som dbplyr kommer att skicka till databasen. Det är ovärderligt vid felsökning, prestandajustering och när du lär dig SQL genom att se hur din dplyr-kod översätts.

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() – Hämta data till R

collect() kör den lata frågan och hämtar resultaten till en lokal R-data frame (tibble). Innan du anropar collect() flyttas inga data från databasen till R – alla åtgärder översätts till SQL och körs på serversidan.

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() – Skicka en lokal data frame till databasen

copy_to() skriver en lokal R-data frame till databasen som en (vanligtvis temporär) tabell. Det är användbart när du vill koppla lokala uppslagstabeller till stora fjärrtabeller eller testa utan en befintlig databas.

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

Koppla samman databastabeller

Du kan koppla samman två tbl()-referenser med samma funktioner från dplyr, till exempel left_join() och inner_join(). dbplyr översätter dem till SQL-satser med JOIN – kopplingen körs i databasen.

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

När du INTE ska använda dbplyr

dbplyr kan inte översätta alla R-uttryck till SQL. Komplexa anpassade funktioner, datumhantering i base R eller R-specifika statistiska funktioner kanske inte har motsvarigheter i SQL. Använd först collect() för att hämta data till R och utför sedan R-specifika åtgärder lokalt.

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

Snabbkontroll

När du har byggt en kedja av dplyr-verb på en tbl()-databasreferens, vilken funktion kör frågan och returnerar resultaten som en lokal R-data frame?

Besvara frågan i den interaktiva lektionen

dbplyr – Viktiga slutsatser

Med dbplyr kan du fråga databaser med dplyr-syntax – utan att behöva skriva SQL:

tbl(con, 'table') – en lat referens till en databastabell
filter(), select(), mutate() – översätts till SQL-satser
group_by() |> summarise() – blir SQL-GROUP BY
show_query() – granska den genererade SQL-koden (utmärkt för att lära sig)
collect() – kör frågan och hämtar data till R
copy_to() – skickar en lokal data frame till databasen
Kopplingar fungerar också: left_join(), inner_join() med flera
Filtrera och aggregera i databasen; använd R endast för sådant som SQL inte kan göra

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

Gratis att börja

Lär dig R med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 43
Lektioner: 159

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”dbplyr: SQL via dplyr-syntax” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen R Academy, inklusive ”dbplyr: SQL via dplyr-syntax”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i R Academy innehåller totalt 4 lektioner.

Vad lär jag mig i ”dbplyr: SQL via dplyr-syntax”?

Skriv dplyr-kod som översätts till SQL och körs i databasen. Ni övar på R Academy med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig R Academy?

Du behöver inga förkunskaper. Utbildningen i R Academy på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 3 av 4.

Hur lång tid tar lektionen ”dbplyr: SQL via dplyr-syntax”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här R Academy-lektionen?

Ja. Varje R Academy-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Grunderna i DBI och RSQLite
Ansluta till PostgreSQL och MySQL
dbplyr: SQL via dplyr-syntax
Parametriserade frågor och transaktioner

← Tillbaka till R Academy