Use SQL em um banco de dados de filmes para decidir o que assistir

Demonstraremos como usar SQL para analisar grandes conjuntos de dados e obter insights valiosos, neste caso, para ajudá-lo a escolher o filme a ser visto a seguir usando um conjunto de dados IMDb.

Neste artigo, estaremos baixando um diretório de conjuntos de dados do IMDb. Não tem certeza do que assistir hoje à noite? Você está navegando infinitamente pela Netflix? Decida o que assistir usando o poder do SQL! Estaremos carregando um conjunto de dados IMDb de um filme já existente em SQL. Analisaremos os dados de diferentes maneiras como classificar os filmes por sua classificação, pelo que os atores protagonizam no filme, ou por outros critérios similares.

Como mencionado neste post do blog sobre como praticar SQL, a melhor maneira de praticar SQL é ganhar experiência prática na resolução de problemas do mundo real, que é exatamente o que estaremos fazendo.

Se você tem um conhecimento básico de SQL, você deve ser capaz de seguir este artigo facilmente. Se você não tem qualquer experiência em TI, considere começar com esta Trilha de Aprendizagem SQL A a Z projetada para pessoas que não têm experiência em TI e querem começar sua aventura com SQL.

Vamos começar aprendendo como colocar os dados do filme em nosso banco de dados SQL.

Completando o download do banco de dados de filmes SQL

Vamos percorrer passo a passo o processo de download de nossos dados e carregá-los em um sistema de gerenciamento de banco de dados (SGBD). Os SGBD comuns incluem MySQL, Oracle DB, PostgreSQL, e SQL Server.

Embora este artigo se concentre em dados de filmes, você pode escolher um conjunto de dados totalmente diferente. Confira esta lista de conjuntos de dados online gratuitos que você pode usar e encontre aquele que lhe interessa. A importação destes conjuntos de dados será semelhante, independentemente do conjunto de dados que você utilizar.

Abra qualquer variedade de SQL que você estiver usando. Para este exemplo, estarei usando o SQL Server Management Studio, mas os passos devem ser semelhantes para todas as outras variedades de SQL por aí. Vamos começar:

1. Os arquivos do conjunto de dados podem ser acessados e baixados de https://datasets.imdbws.com/. Os dados são atualizados diariamente.
2. Faça o download de todos os arquivos listados:
   1. basics.tsv.gz
   2. akas.tsv.gz
   3. basics.tsv.gz
   4. crew.tsv.gz
   5. episódio.tsv.gz
   6. principals.tsv.gz
   7. classificações.tsv.gz
3. Extraia os arquivos zip baixados. O resultado final será um arquivo TSV (separado por tabulação) para cada tabela.
4. Vamos limpar esses dados e convertê-los em CSV para que fiquem em um estado mais funcional:
   1. Abra cada arquivo em um aplicativo de planilhas como o Google Sheets ou o Microsoft Excel.
   2. Encontre e substitua todas as ocorrências de "\N" por uma célula vazia.
   3. Salve o arquivo como um arquivo CSV. Isto facilitará a importação para o SGBD de sua escolha.
5. Abra o seu SGBD.
6. Crie um novo esquema ou tabela clicando com o botão direito do mouse no painel esquerdo e selecionando "Novo Banco de Dados".
7. Clique com o botão direito do mouse no banco de dados → Tarefas → Importar arquivo plano e siga o Assistente de Importação para criar uma tabela para cada arquivo:
   1. Defina tipos de dados válidos para cada coluna que você está importando. Recomendo usar nvarchar(MAX) para colunas de strings, já que você não sabe quanto tempo as strings serão para cada campo. Você pode alterar o tipo de dados da coluna mais tarde, se necessário.
   2. Permitir valores nulos para todas as linhas. Isto evitará problemas com a importação.
8. Repita este processo para cada um dos arquivos que você baixou.

Após completar estas etapas, seu banco de dados de filmes SQL estará no lugar! Agora você está pronto para começar a analisar e consultar os dados.

Exercícios SQL em um banco de dados de filmes

Felizmente, este conjunto de dados veio com alguma documentação descritiva. Para ter uma idéia ainda melhor dos dados, você pode selecionar rapidamente as 1000 primeiras linhas de cada tabela.

Vamos começar a procurar por nosso primeiro filme. Imagine que você quer assistir a um filme de terror. Como podemos isolar apenas os filmes de terror? Felizmente, esta tarefa é assustadoramente simples.

SELECT *
FROM title_basics
WHERE genres LIKE '%Horror%'

Se esta consulta causar alguma confusão, abra esta folha de consultas SQL para refrescar seus conhecimentos. Faça com que esta folha de consulta seja aberta durante o resto do tutorial para ajudá-lo!

E se quiséssemos refinar ainda mais esta lista de filmes de terror? Poderíamos restringir os resultados aos filmes de terror criados depois de 1990, com uma classificação média acima de 9,0 e pelo menos 10.000 votos.

Isto envolverá a obtenção de dados de várias tabelas. Abrindo cada tabela e dando uma olhada nos cabeçalhos das colunas, podemos ver que as seguintes tabelas estarão envolvidas:

• title_basics: trata do gênero de filme e do ano de lançamento (representado pela coluna startYear).
• title_ratings: lida com a classificação (averageRating) e votos (numVotes).

As duas tabelas podem ser unidas na coluna compartilhada, tconst. Como explicado na documentação do IMDb aqui, tconst é um identificador alfanumérico único do título. Vamos escrever nossa consulta:

SELECT titleType, primaryTitle, startYear, genres, averageRating, numVotes
FROM title_basics
INNER JOIN title_ratings ON title_basics.tconst = title_ratings.tconst
WHERE genres LIKE '%Horror%' AND startYear > 1989 AND averageRating > 9.0 AND numVotes > 10000

A execução desta consulta retorna um único resultado, mas não o resultado que desejamos! Em uma inspeção mais detalhada, podemos ver que este título é um videogame, não um filme. Vamos alterar nossa consulta para incluir apenas filmes e expandir a busca reduzindo o número mínimo de votos necessários para 1.000 e a classificação mínima exigida para 8,0.

SELECT *
FROM title_basics
INNER JOIN title_ratings ON title_basics.tconst = title_ratings.tconst
WHERE genres LIKE '%Horror%' AND startYear > 1989 AND averageRating > 8.0 AND numVotes > 1000 AND titleType = 'movie'

A execução desta consulta também produz um único resultado! Parece que não teremos mais que decidir o que assistir, já que só há uma opção que se encaixa em nossos critérios!

Encontrar todos os filmes para um determinado diretor

Vamos passar por outro cenário. E se quisermos ver todos os filmes que Steven Spielberg dirigiu? Como isso funcionaria?

Olhando através das tabelas, podemos determinar o seguinte:

• name_basics: Contém os nomes de todos os atores, escritores, diretores e outros envolvidos na criação de títulos de filmes e TV.
• title_crew: Atua como uma tabela de ligação para títulos, diretores e escritores. Utilizaremos esta tabela para conectar Steven Spielberg aos títulos com os quais ele está envolvido.
• title_basics: Já utilizamos esta tabela. Ela contém informações de título como nome, data de lançamento, classificação, etc.

Vamos ao trabalho! Vamos escrever uma pergunta para a tabela de nomes_básicos para tentar encontrar o famoso diretor Steven Spielberg.

SELECT nconst, primaryName, birthYear, deathYear, primaryProfession, knownForTitles
FROM name_basics
WHERE primaryName LIKE 'steven spielberg'

A execução desta consulta produz um único resultado:

Isto nos dá o importante valor de nconst. A partir da documentação, sabemos que nconst é o identificador alfanumérico único do nome/pessoa.

Podemos alimentar este valor na tabela de title_crew tabela, que contém as informações do diretor e escritor para todos os títulos no IMDb, e faz corresponder Steven Spielberg a todos os títulos com os quais ele está envolvido.

    SELECT * from title_crew where directors LIKE 'nm0000229'
		

A execução desta consulta resulta em uma lista de 45 títulos. Você pode ver pelo valor da coluna dos diretores que Steven Spielberg foi o diretor de todos eles.

Precisamos de uma maneira de usar esta lista de títulos ao lado da title_basics tabela para obter o nome dos filmes em vez de apenas o tconst. Vamos usar uma subconstituição para isso!

SELECT titleType, primaryTitle, startYear, genres
FROM title_basics
WHERE titleType LIKE 'movie'
AND tconst IN
(SELECT tconst FROM title_crew WHERE directors LIKE 'nm0000229')

Execute esta consulta para ver o resultado:

Aí está, todos os títulos dos filmes de Steven Spielberg de nosso banco de dados!

Não pare aqui! Escreva suas próprias consultas personalizadas para extrair mais informações a partir deste grande conjunto de dados. Há muitas maneiras de praticar SQL. Se você se sente cansado de trabalhar com este conjunto de dados, confira este post em 12 Maneiras de Aprender SQL Online para mais excelentes recursos de aprendizado.

Usando SQL em um Grande Banco de Dados de Filmes Existente

Você aprendeu a importar e analisar grandes conjuntos de dados existentes no SGBD de sua escolha e a usar SQL para analisar um banco de dados de filmes. Esta é uma ferramenta poderosa em seu arsenal SQL. Sem mencionar que você nunca mais terá que se preocupar em não poder escolher um filme para assistir novamente! Completar exercícios SQL em bancos de dados de filmes é uma maneira útil de aprender, mas se você quiser mais estrutura, confira este Curso de Práticas em SQL de LearnSQL.com.