SQLDF em R para Ciência de Dados.

O SQLDF é um pacote que permite manipular objetos data.frame de R através de argumentos SQL simples. Argumentos simples são uma instrução SQL, em que nomes de tabelas são nomes de objetos data.frame. Este pacote permite, da mesma forma que PostgreSQL, Sqlite, MySQL, manipular um conjunto de dados, de forma simples e prática.

Antes de começar nosso tutorial devemos fazer download de nossa base de dados de música disponível no seguinte link: https://github.com/lerocha/chinook-database. Esta base de dados está composta por onze tabelas com suas respectivas relações. Para maiores informações consultei o seguinte link: https://archive.codeplex.com/?p=chinookdatabase.

A seguir nosso primeiro passo neste tutorial é instalar o pacote SQLDF fazendo uso do comando na consola de trabalho de R ou Rstudio: install.packages(“sqldf”). Uma vez instalado o pacote digitamos nossa área de trabalho o comando: library(sqldf).

Para o desenvolvimento do tutorial usaremos as tabelas artist e album, as quais estão relacionadas através do campo artistid. Antes de qualquer manipulação de dados devemos conhecer nossa base de dados, como ela é estruturada e organizada, de modo que podamos realizar sem problemas nossas devidas consultas (Query) para qualquer análise de interesse. Neste sentido, usaremos a palavra, frequentemente, usada de SQL a qual nós permite lê os dados: sendo esta palavra SELECT.

A palavra-chave é combinada com a cláusula LIMIT. Esta cláusula permite limitar o número de visualizações de nossos dados, no console de trabalho em R. Para começar digitalizamos:

sqldf("SELECT * FROM album LIMIT 10;", dbname = "")

sqldf("SELECT * FROM artist LIMIT 10;", dbname = "")

Figura 1. Tabela Artist e Album

A saída destas duas linhas de comandos, permitem visualizar as primeiras 10 linhas da nossa tabela album e artist. Antes de continuar com o seguinte exemplo, veremos como a partir da função COUNT(*) podemos saber a quantidade exata de dados em cada tabela. Isto é alcançado digitalizando os seguintes comandos:

sqldf("SELECT COUNT(*) FROM album;", dbname = "")

sqldf("SELECT COUNT(*) FROM artist;", dbname = "")

No seguinte exemplo, veremos como selecionar uma coluna de interesse da tabela. A tabela album está composta por 347 dados e 3 variáveis. A tabela artist está composta por 275 dados e 2 variáveis, de onde estas duas tabelas estão sendo relacionadas pelo campo ou atributo artistid. É fácil listar outras colunas de interesse, na ordem que as quer, separadas por virgula. Neste caso, apenas estamos interessados em listar a coluna title e name das tabelas album e artist, respectivamente, como:

sqldf("SELECT title FROM album LIMIT 10;", dbname = "")

sqldf("SELECT name FROM artist LIMIT 10;", dbname = "")

Uma vez familiarizado com algumas cláusulas e consultas, realizadas de nossas tabelas. Procederemos com aplicação da cláusula WHERE. Está cláusula é uma ferramenta importante que permite escolher quais linhas serão selecionadas numa declaração SELECT. Está cláusula é a mais simples e mais usada numa consulta, devido a que admite exatamente um único valor. Assim, consideremos o seguinte exemplo: Desejamos descobrir o artistid do artista com o nome de “Iron Maiden” usando a tabela artist, e uma vez você conheça o valor de artistid, este deverá ser usado para retornar o title e albumid, correspondente da tabela album. Isto é alcançado mediante o uso das seguintes linhas SQL:

sqldf("SELECT artistid FROM artist WHERE name = 'Iron Maiden'", dbname = "")

sqldf("SELECT albumid, title FROM album WHERE artistid = 90;", dbname = "")

Na primeira consulta (name = ‘Iron Maiden’) a função SQLDF retorna todas as linhas que combinem com o critério de busca, mas, neste caso, apenas uma única linha e todas suas colunas são retornadas. O resultado para o artista Iron Maiden podemos observar que seu artistid é igual a 90. Na segunda consulta (artistid = 90) na tabela album a função SQLDF retorna 21 linhas as quais combinem com o critério de busca. Além disso, nesta consulta são retornadas unicamente duas das três colunas da tabela: albumid e title.

O exemplo anterior pode ser realizado através de uma subconsulta mediante a palavra-chave SELECT. Uma subconsulta é uma consulta dentro de outra consulta, o qual é comumente realizado quando nosso banco de dados é relacionado. Desta forma, para o artista com o nome de “Iron Maiden” temos:

sqldf("SELECT albumid, title FROM album WHERE artistid IN (SELECT artistid FROM artist WHERE name = 'Iron Maiden')", dbname = "")

Figura 2. Albumid e Title para o artista Iron Maiden.

Como dito anteriormente nosso banco de dados é relacional, ou seja, podemos trabalhar com os diferentes relacionamentos entre as tabelas. Nosso caso, as duas tabelas (album e artist) são relacionadas através do campo chamado artistid. Assim, podemos realizar consultas combinadas. Para alcançar nosso objetivo neste tutorial usaremos unicamente a sintaxe INNER JOIN. Considere o seguinte exemplo:

sqldf("SELECT albumid, name, title FROM album

INNER JOIN artist ON album.artistid = artist.artistid LIMIT 15;", dbname = "")

Figura 3. INNER JOIN para ambas as tabelas (Album e Artist).

A saída mostra os registros selecionados que tem valores iguais em ambas as tabelas. No casso, as colunas retornadas são o albumid, o nome do artista e o título do álbum. O comado ON dentro da função SQLDF é usado com o alvo de instruir, mediante a estrutura SQL, ao R qual é a coluna que possui o relacionamento entre as tabelas.

Para finalizar nosso tutorial suponha que você deseja conhecer para cada um dos artistas da tabela artist, o número total de álbuns para cada um. Para isto é necessário conversar um pouco sobre as cláusulas HAVING, ORDER BY e GROUP BY.

1. A clausula HAVING: Esta cláusula permite adicionar um controle extra as agregações de linhas em uma operação de GROUP BY.

2. A clausula GROUP BY: Esta declaração classifica nossos dados em grupos com o objetivo principal de agregar. Esta declaração é executada antes de cláusulas como WHERE, ORDER BY e funções.

3. A clausula GROUP BY: Esta declaração classifica nossos dados em grupos com o objetivo principal de agregar. Esta declaração é executada antes de cláusulas como WHERE, ORDER BY e funções.

Assim, a partir da explicação anterior e mediante a digitação da seguinte linha de comando no console de trabalho de R podemos obter para cada artista o número total de álbuns:

sqldf("SELECT name, COUNT(1) AS albuns FROM (SELECT name, title FROM album, artist

WHERE album.artistid = artist.artistid)

AS ntable GROUP BY name

HAVING COUNT(1)>=1 ORDER BY COUNT(1) DESC LIMIT 15;", dbname = "")

Figura 4. Número total de albuns por artista.

De esta forma terminamos nosso tutorial.