Traduza suas queries SQL para o universo tidyverse
Fique por dentro das aulas, conteúdos, newsletter!
Que cada gole desperte uma nova ideia.
Que cada script abra uma nova conversa.
Que o Café com R, se torne um ponto de encontro nosso!
Se você já sabe SQL e está começando com R, esta apresentação é para você!
Spoiler: Você vai escrever código R que gera SQL automaticamente! 🎉
dbplyr é um pacote que traduz código dplyr (R) para SQL.
Por que isso é fantástico:
“Pense em R, execute em SQL, aprenda os dois!”
A cada query você escreve, está praticando DUAS linguagens ao mesmo tempo!
Imagem: Allison Horst.
Mesma lógica, sintaxe diferente!
Primeiro, instale os pacotes necessários:
DBI: Interface com bancos de dadosRSQLite: Para trabalhar com SQLitedbplyr: O tradutor SQL ↔︎ Rdplyr: Manipulação de dadosLembre-se: Instalar é como baixar o app, carregar é como abrir o app!
Vamos criar um banco SQLite em memória para praticar:
Pronto! Agora temos um banco de dados para trabalhar.
Para usar dbplyr, conecte-se à tabela:
# Source: table<`clientes`> [?? x 4]
# Database: sqlite 3.51.2 [:memory:]
id nome idade cidade
<int> <chr> <dbl> <chr>
1 1 Ana 25 RJ
2 2 Jennifer 33 SP
3 3 Carla 22 SP
4 4 Daniel 35 MG
5 5 Elena 28 RJ
Importante: Os dados ainda estão no banco, não na memória do R!
SQL:
dbplyr (R):
SQL:
dbplyr (R):
| SQL | R (dplyr) | Significado |
|---|---|---|
= |
== |
Igual |
<> ou != |
!= |
Diferente |
> |
> |
Maior que |
< |
< |
Menor que |
>= |
>= |
Maior ou igual |
<= |
<= |
Menor ou igual |
Atenção: Em R use == para comparação, não =
SQL:
dbplyr (R):Use & para AND (E)
SQL:
dbplyr (R): Use | para OR (OU)
SQL:
dbplyr (R):%in% verifica se o valor está no vetor
SQL:
dbplyr (R): Use arrange() para ordenar, desc() para decrescente
SQL:
dbplyr (R):
SQL:
dbplyr (R):
# Source: SQL [?? x 1]
# Database: sqlite 3.51.2 [:memory:]
total
<int>
1 5
n() conta o número de linhas
Vamos criar uma tabela de vendas para exemplos mais interessantes:
SQL:
dbplyr (R):
SQL:
dbplyr (R):
SQL:
dbplyr (R):
| SQL | R (dplyr) | Função |
|---|---|---|
COUNT(*) |
n() |
Contar linhas |
SUM() |
sum() |
Somar valores |
AVG() |
mean() |
Calcular média |
MIN() |
min() |
Valor mínimo |
MAX() |
max() |
Valor máximo |
Dica: Sempre use na.rm = TRUE em R para ignorar valores NA
SQL:
dbplyr (R):
SQL:
dbplyr (R):
# Source: SQL [?? x 3]
# Database: sqlite 3.51.2 [:memory:]
nome produto valor
<chr> <chr> <dbl>
1 Ana A 100
2 Ana A 200
3 Ana C 90
4 Jennifer B 120
5 Jennifer B 150
6 Carla C 80
7 Daniel A 110
8 Elena B 160
SQL:
dbplyr (R):
| SQL | R (dplyr) | O que faz |
|---|---|---|
INNER JOIN |
inner_join() |
Apenas registros que combinam |
LEFT JOIN |
left_join() |
Todos da esquerda + combinações |
RIGHT JOIN |
right_join() |
Todos da direita + combinações |
FULL JOIN |
full_join() |
Todos os registros |
Memória: O nome indica qual tabela mantém todos os registros
SQL:
dbplyr (R): Use TRUE como “ELSE”
clientes_db |>
mutate(
faixa_etaria = case_when(
idade < 25 ~ "Jovem",
idade < 30 ~ "Adulto",
TRUE ~ "Experiente")) |>
select(nome, idade, faixa_etaria)# Source: SQL [?? x 3]
# Database: sqlite 3.51.2 [:memory:]
nome idade faixa_etaria
<chr> <dbl> <chr>
1 Ana 25 Adulto
2 Jennifer 33 Experiente
3 Carla 22 Jovem
4 Daniel 35 Experiente
5 Elena 28 Adulto
SQL:
dbplyr (R):
# Source: SQL [?? x 6]
# Database: sqlite 3.51.2 [:memory:]
id cliente_id produto valor quantidade total
<int> <dbl> <chr> <dbl> <dbl> <dbl>
1 1 1 A 100 2 200
2 2 2 B 150 1 150
3 3 1 A 200 3 600
4 4 3 C 80 1 80
5 5 2 B 120 2 240
6 6 1 C 90 1 90
7 7 4 A 110 2 220
8 8 5 B 160 1 160
mutate() cria ou modifica colunas
SQL:
dbplyr (R):
SQL:
dbplyr (R):
# Calcular a média
valor_medio <- vendas_db |>
summarise(media = mean(valor, na.rm = TRUE)) |>
pull(media)
# Filtrar
vendas_db |>
filter(valor > valor_medio)# Source: SQL [?? x 5]
# Database: sqlite 3.51.2 [:memory:]
id cliente_id produto valor quantidade
<int> <dbl> <chr> <dbl> <dbl>
1 2 2 B 150 1
2 3 1 A 200 3
3 8 5 B 160 1
SQL:
Use str_detect() do pacote stringr (parte do tidyverse)
Quer ver qual SQL o dbplyr está gerando? Use show_query():
<SQL>
SELECT `nome`, `cidade`
FROM `clientes`
WHERE (`idade` > 25.0)
Perfeito para aprender! Compare seu código R com o SQL gerado.
O dbplyr é “lazy” (preguiçoso) - só executa quando necessário.
Sem collect():
Dados ficam no banco
Operações rápidas (SQL é otimizado)
Economiza memória
Com collect():
Dados vêm para o R
Use quando precisar processar no R
Use depois de filtrar para trazer menos dados
Dica: Filtre e agregue NO BANCO, só depois traga para o R!
# 1. Fazer query complexa no banco
relatorio <- clientes_db |>
left_join(vendas_db, by = c("id" = "cliente_id")) |>
filter(!is.na(valor)) |>
group_by(nome, cidade) |>
summarise(
total_compras = n(),
valor_total = sum(valor, na.rm = TRUE),
ticket_medio = mean(valor, na.rm = TRUE)) |>
arrange(desc(valor_total))<SQL>
SELECT
`nome`,
`cidade`,
COUNT(*) AS `total_compras`,
SUM(`valor`) AS `valor_total`,
AVG(`valor`) AS `ticket_medio`
FROM (
SELECT `q01`.*
FROM (
SELECT
`clientes`.*,
`vendas`.`id` AS `id.y`,
`produto`,
`valor`,
`quantidade`
FROM `clientes`
LEFT JOIN `vendas`
ON (`clientes`.`id` = `vendas`.`cliente_id`)
) AS `q01`
WHERE (NOT((`valor` IS NULL)))
) AS `q01`
GROUP BY `nome`, `cidade`
ORDER BY `valor_total` DESC
# 3. Executar e trazer para o R
resultado_final <- relatorio |> collect()
# 4. Agora você pode usar ggplot2, etc
print(resultado_final)# A tibble: 5 × 5
# Groups: nome [5]
nome cidade total_compras valor_total ticket_medio
<chr> <chr> <int> <dbl> <dbl>
1 Ana RJ 3 390 130
2 Jennifer SP 2 270 135
3 Elena RJ 1 160 160
4 Daniel MG 1 110 110
5 Carla SP 1 80 80
Perfeito! Processamos tudo no banco e só trouxemos o resultado final.
PostgreSQL:
MySQL:
1. Sempre feche a conexão:
2. Filtre ANTES de collect():
3. Use índices no banco de dados:
4. Teste com LIMIT primeiro:
| SQL | dplyr | Função |
|---|---|---|
SELECT |
select() |
Escolher colunas |
WHERE |
filter() |
Filtrar linhas |
ORDER BY |
arrange() |
Ordenar |
GROUP BY |
group_by() |
Agrupar |
Agregações + GROUP BY |
summarise() |
Resumir |
HAVING |
filter() após summarise() |
Filtrar grupos |
| SQL | dplyr | Função |
|---|---|---|
LIMIT |
head() |
Limitar resultados |
DISTINCT |
distinct() |
Valores únicos |
INNER JOIN |
inner_join() |
Interseção |
LEFT JOIN |
left_join() |
Esquerda + combinação |
RIGHT JOIN |
right_join() |
Direita + combinação |
FULL JOIN |
full_join() |
União completa |
CASE WHEN |
case_when() |
Condicionais |
| Criar coluna | mutate() |
Nova variável |
Desafio: Traduza este SQL para dplyr:
SELECT cidade, COUNT(*) as total
FROM clientes
WHERE idade >= 25
GROUP BY cidade
HAVING COUNT(*) > 1
ORDER BY total DESC;Tente fazer sozinho antes de ver a resposta!
Leia em voz alta: “Pegue clientes, filtre idade >= 25, agrupe por cidade, conte quantos em cada, filtre grupos com mais de 1, ordene por total decrescente”
Desafio: Crie uma query que:
Tente fazer sozinho!
clientes_db |>
inner_join(vendas_db, by = c("id" = "cliente_id")) |>
group_by(nome) |>
summarise(valor_total = sum(valor, na.rm = TRUE)) |>
filter(valor_total > 200) |>
arrange(desc(valor_total))# Source: SQL [?? x 2]
# Database: sqlite 3.51.2 [:memory:]
# Ordered by: desc(valor_total)
nome valor_total
<chr> <dbl>
1 Ana 390
2 Jennifer 270
1. Projete (select) cedo:
2. Filtre no banco, não no R
3. Use índices nas colunas de JOIN e WHERE
Erro: “no applicable method” -Você tentou usar uma função que não funciona com banco
collect() antes OU use função equivalente do SQLDados não aparecem: Lembre-se: dbplyr é lazy!
collect() ou apenas visualize com print()Query muito lenta: Está trazendo dados demais?
collect()Não use dbplyr quando:
read_csv()Documentação:
Livros gratuitos:
Prática:
Mini projeto para praticar:
show_query() para ver o SQL geradoIsso vai consolidar seu aprendizado!
# Conectar
con <- dbConnect(RSQLite::SQLite(), "banco.db")
tabela <- tbl(con, "nome_tabela")
# Operações básicas
tabela |> filter(col > 10) # WHERE
tabela |> select(col1, col2) # SELECT
tabela |> arrange(col) # ORDER BY
tabela |> distinct(col) # DISTINCT
# Agregações
tabela |> group_by(col) |>
summarise(total = n()) # COUNT
# Joins
left_join(tab1, tab2, by = "id") # LEFT JOIN
# Executar
tabela |> collect() # Trazer para R
tabela |> show_query() # Ver SQLVocê aprendeu:
dbplyr e como funcionacollect() e show_query()O melhor: Você pode escrever R e deixar o banco fazer o trabalho pesado!
Fluxo ideal:
show_query() para ver o SQLcollect() só no final“Pense em R, execute em SQL, aprenda os dois!”
A cada query você escreve, está praticando DUAS linguagens ao mesmo tempo!
SQL + R = 💪
Você não precisa escolher entre SQL e R.
Com dbplyr, você tem o melhor dos dois mundos:
Continue praticando e explorando!
Esta apresentação é parte do projeto Café com R
É OPEN, USE, COMPARTILHE!
Perguntas? Dúvidas? Sugestões?
Próximo passo: Abra seu RStudio e comece a praticar!
Não esqueça de fechar a conexão ao terminar:
Boa prática: Sempre feche conexões para liberar recursos!
Fique por dentro das aulas, conteúdos, newsletter!
Que cada gole desperte uma nova ideia.
Que cada script abra uma nova conversa.
Que o Café com R, se torne um ponto de encontro nosso!

Jennifer Lopes • Café com R