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.
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.
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.
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.path <- "." # define o caminho como ".", que representa a pasta atual onde o R está trabalhando
fname <- file.path(path, "disco.db") # cria o caminho completo do arquivo "disco.db" dentro da pasta definida em 'path'
fname [1] "./disco.db"RSQLite, conecte-se ao arquivo de banco de dados. Armazene a conexão na variável conn.# carrega os pacotes necessários
library(DBI)
library(RSQLite)
# conecta ao banco de dados usando o caminho salvo em fname
conn <- dbConnect(SQLite(), dbname = fname)# lista as tabelas do banco
dbListTables(conn) [1] "albums" "artists" "customers" "employees"
[5] "genres" "invoice_items" "invoices" "media_types"
[9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
[13] "tracks" customers.dbListFields(conn,'customers') # lista todos os nomes dos campos (colunas) da tabela "customers" [1] "CustomerId" "FirstName" "LastName" "Company" "Address"
[6] "City" "State" "Country" "PostalCode" "Phone"
[11] "Fax" "Email" "SupportRepId"SQLite, com o apoio do comando dbGetQuery, identifique quantos clientes estão atualmente cadastrados neste banco de dados.total_clientes <- dbGetQuery(conn, "
SELECT COUNT(*) AS total_clientes
FROM customers;
")
total_clientes total_clientes
1 59# executa a consulta SQL no banco
# conta quantos clientes existem e dá o nome 'total_clientes' ao resultado
# usa a tabela 'customers'SQLite, identifique o número de países diferentes em que moram os clientes encontrados acima.num_paises <- dbGetQuery(conn, "
SELECT COUNT(DISTINCT Country) AS total_paises
FROM customers;
")
num_paises total_paises
1 24# executa uma consulta SQL no banco de dados conectado pela variável 'conn' e armazena o resultado em 'num_paises'
# conta o número de valores distintos na coluna 'Country' e nomeia o resultado como 'total_paises'
# indica que a contagem deve ser feita na tabela 'customers'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.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# seleciona o país e conta quantos clientes há em cada país
# usa a tabela customers
# agrupa os resultados por país
# ordena de forma decrescente pela contagem de clientesSQLite.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# seleciona o país e conta quantos clientes há em cada país
# usa a tabela customers
# agrupa os resultados por país
# ordena de forma decrescente pela contagem de clientes
# limita a saída aos 5 primeiros resultadospaises_6_letras <- dbGetQuery(conn, "
SELECT DISTINCT Country
FROM customers
WHERE LENGTH(Country) = 6;
")
paises_6_letras Country
1 Brazil
2 Canada
3 Norway
4 France
5 Poland
6 Sweden# seleciona apenas os nomes de países únicos
# considera os dados da tabela 'customers'
# mantém apenas os países cujo nome tem exatamente 6 letrasmusicas_brasil <- dbGetQuery(conn, "
SELECT DISTINCT t.Name AS Musica
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
JOIN tracks t ON ii.TrackId = t.TrackId
WHERE c.Country = 'Brazil'
ORDER BY t.Name
")
musicas_brasil 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
7 Aces High
8 Admirável Gado Novo
9 All Along The Watchtower
10 All I Want Is You
11 Aloha
12 Amor De Muito
13 Animal
14 Any Colour You Like
15 Aos Leões
16 Babyface
17 Back off Bitch
18 Bad
19 Bad Seed
20 Banditismo Por Uma Questa
21 Be Good Johnny
22 Be Mine
23 Big Wave
24 Binky The Doormat
25 Bittersweet Me
26 Black
27 Black Light Syndrome
28 Bora-Bora
29 Bossa
30 Burden In My Hand
31 Calling Dr. Love
32 Carolina Hard-Core Ecstasy
33 Casa
34 Caso Você Queira Saber
35 Child In Time
36 Cinema Mudo
37 Cold Gin
38 Coma
39 Communication Breakdown(2)
40 Cristina Nº 2
41 Cropduster
42 Cry For Love
43 Cérebro Eletrônico
44 D'Yer Mak'er
45 Damage Inc.
46 Dance
47 Demorou!
48 Dissident
49 Don't Cry (Original)
50 Don't Look Back
51 Doutor
52 Down Under
53 Down by the Sea
54 Drifter
55 Duelists
56 Dust N' Bones
57 Electrolite
58 Eu Amo Você
59 Everything I Need
60 Experiment In Terra
61 Eye
62 Fantasia On Greensleeves
63 Fast As a Shark
64 Flower
65 Flying High Again
66 Fool In The Rain
67 Garden of Eden
68 Get Down, Make Love
69 Get Up
70 Girl From A Pawnshop
71 Go Down
72 Green Disease
73 Grito De Alerta
74 Heart Of Lothian: Wide Boy / Curtain Call
75 Hell Ain't A Bad Place To Be
76 Help Yourself
77 Home Sweet Home
78 I Belong To You
79 I Feel Good (I Got You) - Sossego
80 In Bloom
81 In The Evening
82 Interlude Zumbi
83 Into The Light
84 Intro
85 Ipiranga 2001
86 Is This Love (Live)
87 It's a Mistake
88 Jesus Christ Pose
89 Karelia Suite, Op.11: 2. Ballade (Tempo Di Menuetto)
90 Knocking At Your Back Door
91 Lay Down Sally
92 Leash
93 Leper Messiah
94 Like A Song...
95 Lindo Lago Do Amor
96 Linha Do Equador
97 Live and Let Die
98 Lixo Do Mangue
99 Losfer Words
100 Mama, I'm Coming Home
101 Maracatu De Tiro Certeiro
102 Mateus Enter
103 Maybe I'm A Leo
104 Mensagen De Amor (2000)
105 Meu Caro Amigo
106 Meu Erro
107 Midnight From The Inside Out
108 Mis Penas Lloraba Yo (Ao Vivo) Soy Gitano (Tangos)
109 Morena De Angola
110 Most High
111 Mundaréu
112 Music for the Funeral of Queen Mary: VI. "Thou Knowest, Lord, the Secrets of Our Hearts"
113 Nega Do Cabelo Duro
114 No No No
115 No Quarter
116 Nossa Gente (Avisa Là)
117 Nosso Adeus
118 Não Vou Ficar
119 O Descobridor Dos Sete Mares
120 O Leaozinho
121 O Que Me Importa
122 Oceans
123 On A Plain
124 Pais E Filhos
125 Paranoid
126 Pick Myself Up
127 Plot 180
128 Podes Crer
129 Ponto De Interrogação
130 Powerslave
131 Praise
132 Red Light
133 Redundant
134 Refavela (Live)
135 Reggae Tchan
136 Revolta Olodum
137 Right Next Door to Hell
138 Rios Pontes & Overdrives
139 Rock And Roll Is Dead
140 Rocket Queen
141 Saber Amar
142 Samba Do Lado
143 Sanctuary
144 Saudade Dos Aviões Da Panair (Conversando No Bar)
145 Scentless Apprentice
146 Selvagem
147 Sereia
148 Será
149 Será Que Vai Chover?
150 Seven Seas Of Rhye
151 Shakes and Ladders
152 Shout It Out Loud
153 Sincero Breu
154 Slither
155 Snowballed
156 So Fast, So Numb
157 Sometimes Salvation
158 Soul Singing
159 Stand Inside Your Love
160 Stir It Up (Live)
161 String Quartet No. 12 in C Minor, D. 703 "Quartettsatz": II. Andante - Allegro assai
162 Strutter
163 Suite No. 3 in D, BWV 1068: III. Gavotte I & II
164 Sweetest Thing
165 Symphony No. 2, Op. 16 - "The Four Temperaments": II. Allegro Comodo e Flemmatico
166 Take the Celestra
167 The Day I Tried To Live
168 The Great Gig In The Sky
169 The Memory Remains
170 The Unforgiven II
171 The Unwritten Law
172 Think About You
173 Title Song
174 Top Top
175 TriboTchan
176 Um Satélite Na Cabeça
177 Untitled
178 Vai Passar
179 Vai Valer
180 Vamo Batê Lata
181 Vavoom : Ted The Mechanic
182 Voce Nao Entende Nada - Cotidiano
183 Wanted Dread And Alive
184 Why Go
185 X-9 2001
186 You're My Best Friend
187 You've Been A Long Time Coming
188 Your Blue Room
189 Água de Beber# seleciona os nomes das músicas compradas, sem repetições
# usa a tabela 'customers' com apelido 'c'
# junta com 'invoices' para relacionar clientes e faturas
# junta com 'invoice_items' para relacionar faturas e músicas
# junta com 'tracks' para obter os nomes das músicas
# filtra apenas clientes do Brasil
# ordena os nomes das músicas em ordem alfabéticaQuestões Extras
Qual o álbum mais tocado por pais?
album_mais_tocado_pais <- dbGetQuery(conn, "
SELECT Country, Album, TotalVendas
FROM (
SELECT c.Country,
a.Title AS Album,
COUNT(*) AS TotalVendas,
RANK() OVER (
PARTITION BY c.Country
ORDER BY COUNT(*) DESC
) AS rnk
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
JOIN tracks t ON ii.TrackId = t.TrackId
JOIN albums a ON t.AlbumId = a.AlbumId
GROUP BY c.Country, a.Title
)
WHERE rnk = 1
ORDER BY TotalVendas DESC, Country;
")
album_mais_tocado_pais Country
1 USA
2 Canada
3 Brazil
4 France
5 France
6 Ireland
7 India
8 Czech Republic
9 Finland
10 Finland
11 Germany
12 Germany
13 Spain
14 United Kingdom
15 United Kingdom
16 Argentina
17 Australia
18 Austria
19 Belgium
20 Belgium
21 Denmark
22 Denmark
23 Hungary
24 Italy
25 Netherlands
26 Norway
27 Norway
28 Poland
29 Portugal
30 Portugal
31 Sweden
32 Chile
33 Chile
34 Chile
Album TotalVendas
1 The Office, Season 3 14
2 Arquivo II 9
3 Use Your Illusion I 7
4 Minha Historia 7
5 Chronicle, Vol. 1 7
6 Lost, Season 2 7
7 Up An' Atom 6
8 Prenda Minha 5
9 Greatest Kiss 5
10 Greatest Hits 5
11 Unplugged 5
12 Djavan Ao Vivo - Vol. 1 5
13 My Generation - The Very Best Of The Who 5
14 No Prayer For The Dying 5
15 Load 5
16 Acústico 4
17 The X Factor 4
18 My Generation - The Very Best Of The Who 4
19 Rotten Apples: Greatest Hits 4
20 Big Ones 4
21 Mezmerize 4
22 Chronicle, Vol. 1 4
23 Pop 4
24 Acústico 4
25 In Step 4
26 For Those About To Rock We Salute You 4
27 Cássia Eller - Coleção Sem Limite [Disc 2] 4
28 Rattle And Hum 4
29 Raul Seixas 4
30 Instant Karma: The Amnesty International Campaign to Save Darfur 4
31 Volume Dois 4
32 My Way: The Best Of Frank Sinatra [Disc 1] 3
33 Heroes, Season 1 3
34 Battlestar Galactica, Season 3 3# seleciona o país, o álbum e o total de vendas
# calcula a contagem de vendas por país e álbum
# usa a função RANK() para numerar os álbuns dentro de cada país
# mantém apenas os álbuns mais vendidos (rnk = 1) em cada país
# ordena pelo total de vendas (decrescente) e depois pelo paísQual o artista mais tocado por pais?
artista_mais_tocado_pais <- dbGetQuery(conn, "
SELECT Country, Artista, TotalVendas
FROM (
SELECT c.Country,
ar.Name AS Artista,
COUNT(*) AS TotalVendas,
RANK() OVER (
PARTITION BY c.Country
ORDER BY COUNT(*) DESC
) AS rnk
FROM customers c
JOIN invoices i ON c.CustomerId = i.CustomerId
JOIN invoice_items ii ON i.InvoiceId = ii.InvoiceId
JOIN tracks t ON ii.TrackId = t.TrackId
JOIN albums a ON t.AlbumId = a.AlbumId
JOIN artists ar ON a.ArtistId = ar.ArtistId
GROUP BY c.Country, ar.Name
)
WHERE rnk = 1
ORDER BY TotalVendas DESC, Country;
")
artista_mais_tocado_pais Country Artista TotalVendas
1 USA Iron Maiden 34
2 Australia Iron Maiden 18
3 Canada Os Paralamas Do Sucesso 16
4 Portugal Iron Maiden 16
5 Germany Iron Maiden 14
6 Brazil Pearl Jam 11
7 Brazil Os Paralamas Do Sucesso 11
8 France Creedence Clearwater Revival 11
9 Austria U2 9
10 Belgium Faith No More 9
11 Czech Republic U2 9
12 Czech Republic Iron Maiden 9
13 Norway Led Zeppelin 9
14 United Kingdom Metallica 9
15 India Iron Maiden 8
16 Ireland Lost 8
17 Denmark Creedence Clearwater Revival 7
18 Argentina Metallica 6
19 Chile Led Zeppelin 6
20 Finland Van Halen 6
21 Netherlands Red Hot Chili Peppers 6
22 Poland U2 6
23 Spain Pearl Jam 6
24 Hungary The Office 5
25 Hungary Jamiroquai 5
26 Italy The Rolling Stones 5
27 Italy Faith No More 5
28 Sweden Titãs 5# seleciona o país, o artista e o total de vendas
# calcula a contagem de vendas por país e artista
# usa a função RANK() para numerar os artistas dentro de cada país
# mantém apenas os artistas mais vendidos (rnk = 1) em cada país
# ordena pelo total de vendas (decrescente) e depois pelo paísdbDisconnect(conn) # fecha a conexão com o banco de dados