# Obtém o diretório de trabalho atual e combina com o nome do arquivo
path <- getwd()
fname <- file.path(path, "disco.db")Desafio06
SQLite
O SQLite é uma biblioteca embutida em um processo único que é autônomo, não necessita de servidor e exige zero de configuração. O código é de domínio público e seu uso é gratuito em qualquer tipo de utilização. Trata-se de um sistema SQL completo, capaz de utilizar múltiplas tabelas, índices, gatilhos e visões. Ele funciona, virtualmente, em qualquer plataforme (incluindo móveis) e pesa cerca de 600KB.
Objetivos
Ao fim deste laboratório, você deverá ser capaz de: - Conectar-se a um banco de dados do tipo SQLite utilizando o R como interface; - Explorar quais são as tabelas disponíveis no referido banco de dados; - Identificar quais são as colunas de uma dada tabela existente no banco de dados; - Realizar pesquisas simples; - Extrair registros do banco de dados e armazená-las em objetos do R; - Realizar pesquisas mais complexas, utilizando WHERE, GROUP BY, INNER JOIN, HAVING, LIMIT, DISTINCT e GLOB.
Recomendações Gerais
Toda manipulação de dados deve ocorrer com verbos do pacote dplyr. O livro R for Data Science possui um excelente capítulo sobre strings.
Atividade
- Baixe o arquivo disco.db e armazene na variável path o caminho completo (pasta) na qual o arquivo foi gravado. Utilize o comando file.path() para combinar a variável path com o nome do arquivo (disco.db) e obter o nome do arquivo com seu respectivo caminho. Armazene este resultado na variável fname.
- Utilizando o pacote RSQLite, conecte-se ao arquivo de banco de dados. Armazene a conexão na variável conn.
# Estabelece conexão com o arquivo disco.db
library(RSQLite)Warning: pacote 'RSQLite' foi compilado no R versão 4.4.3conn <- dbConnect(RSQLite::SQLite(), fname)- Liste as tabelas existentes no banco de dados.
# Mostra todas as tabelas disponíveis no banco
tabelas <- dbListTables(conn)
tabelas [1] "albums" "artists" "customers" "employees"
[5] "genres" "invoice_items" "invoices" "media_types"
[9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
[13] "tracks" - Identifique os nomes de todas as colunas existentes na tabela customers.
# Mostra todos os campos/colunas da tabela customers
colunas_customers <- dbListFields(conn, "customers")
colunas_customers [1] "CustomerId" "FirstName" "LastName" "Company" "Address"
[6] "City" "State" "Country" "PostalCode" "Phone"
[11] "Fax" "Email" "SupportRepId"- Utilizando apenas SQLite, com o apoio do comando dbGetQuery, identifique quantos clientes estão atualmente cadastrados neste banco de dados.
# Consulta SQL para contar registros na tabela customers
total_clientes <- dbGetQuery(conn, "SELECT COUNT(*) as total FROM customers")
total_clientes$total[1] 59- Utilizando apenas SQLite, identifique o número de países diferentes em que moram os clientes encontrados acima.
# Consulta que conta valores únicos na coluna Country
paises_diferentes <- dbGetQuery(conn, "SELECT COUNT(DISTINCT Country) as paises FROM customers")
paises_diferentes$paises[1] 24- Utilizando apenas SQLite, quantos clientes existem por país? A tabela resultante deve conter o nome do país e a respectiva contagem, além de ser ordenada de maneira decrescente pela referida contagem.
# Agrupa por país e conta clientes, ordenando do maior para menor
clientes_por_pais <- dbGetQuery(conn, "
SELECT Country, COUNT(*) as total_clientes
FROM customers
GROUP BY Country
ORDER BY total_clientes DESC")
clientes_por_pais Country total_clientes
1 USA 13
2 Canada 8
3 France 5
4 Brazil 5
5 Germany 4
6 United Kingdom 3
7 Portugal 2
8 India 2
9 Czech Republic 2
10 Sweden 1
11 Spain 1
12 Poland 1
13 Norway 1
14 Netherlands 1
15 Italy 1
16 Ireland 1
17 Hungary 1
18 Finland 1
19 Denmark 1
20 Chile 1
21 Belgium 1
22 Austria 1
23 Australia 1
24 Argentina 1- Quais são os 5 países com mais clientes registrados? Use apenas SQLite.
# Similar ao anterior mas limitando a 5 resultados
top5_paises <- dbGetQuery(conn, "
SELECT Country, COUNT(*) as total_clientes
FROM customers
GROUP BY Country
ORDER BY total_clientes DESC
LIMIT 5")
top5_paises Country total_clientes
1 USA 13
2 Canada 8
3 France 5
4 Brazil 5
5 Germany 4- Quais são os países registrados que possuem apenas 6 letras no nome?
# Filtra países onde o nome tem comprimento igual a 6 caracteres
paises_6_letras <- dbGetQuery(conn, "
SELECT DISTINCT Country
FROM customers
WHERE LENGTH(Country) = 6
ORDER BY Country
")
paises_6_letras Country
1 Brazil
2 Canada
3 France
4 Norway
5 Poland
6 Sweden- Quais foram as músicas compradas por clientes brasileiros?
# Consulta com JOIN entre 4 tabelas (tracks, invoice_items, invoices e customers) para relacionar clientes brasileiros com músicas compradas
musicas_brasileiros <- dbGetQuery(conn, "
SELECT DISTINCT t.Name as musica
FROM tracks t
INNER JOIN invoice_items ii ON t.TrackId = ii.TrackId
INNER JOIN invoices i ON ii.InvoiceId = i.InvoiceId
INNER JOIN customers c ON i.CustomerId = c.CustomerId
WHERE c.Country = 'Brazil'
ORDER BY t.Name
")
head(musicas_brasileiros) musica
1 1/2 Full
2 2 Minutes To Midnight
3 A Cor Do Sol
4 A Cura
5 A Menina Dança
6 Abraham, Martin And John- Qual o álbum mais tocado por país? (Pergunta 1 - Desafio 06)
# Consulta aninhada que encontra o álbum com mais reproduções por país
album_mais_tocado <- dbGetQuery(conn, "
SELECT c.Country, a.Title as album, COUNT(*) as reproducoes
FROM albums a
INNER JOIN tracks t ON a.AlbumId = t.AlbumId
INNER JOIN invoice_items ii ON t.TrackId = ii.TrackId
INNER JOIN invoices i ON ii.InvoiceId = i.InvoiceId
INNER JOIN customers c ON i.CustomerId = c.CustomerId
GROUP BY c.Country, a.AlbumId
HAVING reproducoes = (
SELECT COUNT(*)
FROM tracks t2
INNER JOIN invoice_items ii2 ON t2.TrackId = ii2.TrackId
INNER JOIN invoices i2 ON ii2.InvoiceId = i2.InvoiceId
INNER JOIN customers c2 ON i2.CustomerId = c2.CustomerId
WHERE c2.Country = c.Country AND t2.AlbumId = a.AlbumId
GROUP BY t2.AlbumId
ORDER BY COUNT(*) DESC
LIMIT 1
)
ORDER BY c.Country, reproducoes DESC
")
head(album_mais_tocado) Country album reproducoes
1 Argentina Acústico 4
2 Argentina ReLoad 3
3 Argentina Serie Sem Limite (Disc 2) 2
4 Argentina Serie Sem Limite (Disc 1) 2
5 Argentina Master Of Puppets 2
6 Argentina Appetite for Destruction 2- Qual o artista mais tocado por país? (Pergunta 2 - Desafio 06)
# Consulta aninhada similar à anterior, mas para artistas
artista_mais_tocado <- dbGetQuery(conn, "
SELECT c.Country, ar.Name as artista, COUNT(*) as reproducoes
FROM artists ar
INNER JOIN albums a ON ar.ArtistId = a.ArtistId
INNER JOIN tracks t ON a.AlbumId = t.AlbumId
INNER JOIN invoice_items ii ON t.TrackId = ii.TrackId
INNER JOIN invoices i ON ii.InvoiceId = i.InvoiceId
INNER JOIN customers c ON i.CustomerId = c.CustomerId
GROUP BY c.Country, ar.ArtistId
HAVING reproducoes = (
SELECT COUNT(*)
FROM artists ar2
INNER JOIN albums a2 ON ar2.ArtistId = a2.ArtistId
INNER JOIN tracks t2 ON a2.AlbumId = t2.AlbumId
INNER JOIN invoice_items ii2 ON t2.TrackId = ii2.TrackId
INNER JOIN invoices i2 ON ii2.InvoiceId = i2.InvoiceId
INNER JOIN customers c2 ON i2.CustomerId = c2.CustomerId
WHERE c2.Country = c.Country AND ar2.ArtistId = ar.ArtistId
GROUP BY ar2.ArtistId
ORDER BY COUNT(*) DESC
LIMIT 1
)
ORDER BY c.Country, reproducoes DESC
")
head(artista_mais_tocado) # Exibe primeiras linhas Country artista reproducoes
1 Argentina Metallica 6
2 Argentina Titãs 5
3 Argentina Foo Fighters 5
4 Argentina Tim Maia 4
5 Argentina Guns N' Roses 2
6 Argentina Funk Como Le Gusta 2- Desconecte do banco de dados.
dbDisconnect(conn) # Encerra a conexão com o banco de dados