0Pricing
R Academy · Lezione

dbplyr: SQL tramite la sintassi dplyr

Scriva codice dplyr che viene tradotto in SQL ed eseguito nel database.

dbplyr: SQL tramite la sintassi dplyr è una lezione R Academy gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento R Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso R Academy include 4 lezioni in totale.

Che cos'è dbplyr?

dbplyr è un pacchetto R che traduce automaticamente i verbi dplyr in SQL. Invece di scrivere SQL grezzo, scrive codice dplyr familiare e dbplyr lo converte nel dialetto SQL corretto per il backend del database. La query viene eseguita nel database, non nella memoria di 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')

Connessione a un database

dbplyr funziona sopra una connessione DBI. Stabilisca la connessione con DBI::dbConnect() utilizzando il pacchetto del driver appropriato, quindi passi l'oggetto connessione alle funzioni di 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() — Fare riferimento a una tabella del database

tbl(con, 'table_name') crea un riferimento lazy a una tabella del database. I dati non vengono ancora recuperati: si ottiene soltanto un puntatore. È quindi possibile concatenare i verbi dplyr e dbplyr costruirà progressivamente la query 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')

Applicare filter() a una tabella del database

È possibile concatenare filter() a un riferimento tbl() proprio come si farebbe con un data frame locale. dbplyr lo traduce in una clausola SQL WHERE. Il filtraggio avviene nel database: a R verranno inviate solo le righe corrispondenti.

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

Applicare select() e mutate()

select() corrisponde a SQL SELECT, mentre mutate() corrisponde alle colonne calcolate nella clausola SELECT. dbplyr gestisce la traduzione, comprese molte espressioni R comuni che hanno equivalenti 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() e summarise() — Aggregazione

group_by() e summarise() vengono tradotti in SQL GROUP BY con funzioni di aggregazione. In questo modo è possibile calcolare riepiloghi nel database senza trasferire prima tutte le righe in R: è fondamentale per le tabelle di grandi dimensioni.

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() — Esaminare l'SQL generato

show_query() stampa l'SQL che dbplyr invierà al database. È uno strumento prezioso per il debug, l'ottimizzazione delle prestazioni e l'apprendimento dell'SQL, perché mostra come viene tradotto il codice dplyr.

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() — Trasferire i dati in R

collect() esegue la query lazy e recupera i risultati in un data frame R locale (tibble). Finché non si chiama collect(), nessun dato viene trasferito dal database a R: tutte le operazioni vengono tradotte in SQL ed eseguite lato 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() — Inviare un data frame locale al database

copy_to() scrive un data frame R locale nel database come tabella (di solito temporanea). È utile per unire tabelle di ricerca locali a tabelle remote di grandi dimensioni o per eseguire test senza un database preesistente.

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

Unire tabelle del database

È possibile unire due riferimenti tbl() utilizzando le stesse funzioni left_join(), inner_join() ecc. di dplyr. dbplyr le traduce in clausole SQL JOIN: l'unione viene eseguita nel database.

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

Quando NON usare dbplyr

dbplyr non è in grado di tradurre in SQL ogni espressione R. Funzioni personalizzate complesse, la manipolazione delle date con R base o funzioni statistiche specifiche di R potrebbero non avere equivalenti SQL. Chiami prima collect() per trasferire i dati in R, quindi applichi localmente le operazioni disponibili solo in 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

Verifica rapida

Dopo aver costruito una catena di verbi dplyr su un riferimento tbl() del database, quale funzione esegue la query e restituisce i risultati come data frame R locale?

dbplyr — Punti chiave

dbplyr consente di interrogare i database con la sintassi dplyr, senza bisogno di SQL:

  • tbl(con, 'table') — riferimento lazy a una tabella del database
  • filter(), select(), mutate() — tradotti in clausole SQL
  • group_by() |> summarise() — diventa SQL GROUP BY
  • show_query() — esamina l'SQL generato, ottimo per imparare
  • collect() — esegue la query e trasferisce i dati in R
  • copy_to() — invia un data frame locale al database
  • Funzionano anche le unioni: left_join(), inner_join() ecc.
  • Filtri e aggregazioni nel database; usi R solo per ciò che SQL non può fare
# 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()

Domande Frequenti

La lezione «dbplyr: SQL tramite la sintassi dplyr» è gratuita?

Sì — il testo completo di «dbplyr: SQL tramite la sintassi dplyr» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso R Academy, passa a CoddyKit PRO. Il corso R Academy include 4 lezioni in totale.

Cosa imparerò in «dbplyr: SQL tramite la sintassi dplyr»?

Scriva codice dplyr che viene tradotto in SQL ed eseguito nel database. Eserciti R Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare R Academy?

Non è richiesta alcuna esperienza precedente. R Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «dbplyr: SQL tramite la sintassi dplyr»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione R Academy?

Sì. Ogni lezione R Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Basi di DBI e RSQLite
  2. Connessione a PostgreSQL e MySQL
  3. dbplyr: SQL tramite la sintassi dplyr
  4. Query parametrizzate e transazioni
← Torna a R Academy