MySQL: aplicado para análise de dados

Conhecendo os dados e projeto da Insight places - Apresentação

Olá! Você já se perguntou como as grandes descobertas de negócios acontecem?

Daremos uma dica. Elas podem ocorrer através dos dados. Os dados são capazes de responder grandes perguntas de negócio, para guiar as decisões das lideranças e diretorias das empresas.

Podemos fazer tudo isso através do MySQL, explorando esses dados com as diversas ferramentas e possibilidades que ele oferece.

Boas-vindas! Sou o Igor Nascimento, instrutor na Escola de Dados.

Audiodescrição: Igor é um homem branco, de cabelos e olhos pretos. Usa uma camiseta cinza e na sua frente há um microfone. Ao fundo, uma parede lisa com uma porta na lateral direita e uma iluminação degradê do azul ao verde.

Neste curso de MySQL para análise de dados, você será uma pessoa responsável por gerar insights para a Insights Places, uma empresa que trabalha com aluguel de imóveis.

Nossa responsabilidade nesse time será fornecer métricas e insights para as pessoas proprietárias, a partir dos dados. Queremos ajudar essas pessoas a tomarem melhores decisões, guiadas por dados.

O que você aprenderá

Nesse curso, você desenvolverá a habilidade de fazer uma análise de dados, utilizando funções matemáticas do MySQL, além de criar suas próprias funções, procedimentos e disponibilizar e atualizar esses dados por meio de Views e Triggers.

Pré-requisitos

Para que você tenha o melhor proveito do curso, é muito importante que conheça SQL com o MySQL. Portanto, saber utilizar joins, views, triggers e funções, pois aplicaremos todos esses conhecimentos nesse contexto.

Estamos muito animados para desenvolver esse projeto com você. Vamos lá?

Conhecendo os dados e projeto da Insight places - Carregando os dados

Neste projeto, somos analistas de dados da empresa Insight Places. Sendo assim, precisamos pegar os dados e gerar insights e métricas. Mas, quem será o nosso público?

Esse novo projeto que a Insight Places propôs é de ajudar as pessoas proprietárias que disponibilizam seus imóveis para aluguel na plataforma.

Essas pessoas precisam de métricas e insights dos imóveis para tomar decisões, como se devem aumentar o preço em determinada época do ano ou não. Outro dado importante é saber a variação de valor referente a cada região do país, por exemplo.

Carregando os dados da Insight Places

Para começar essa análise, precisamos ter acesso aos dados. Nesse caso, fizemos uma cópia dos dados que estão no banco de dados da Insight Places e carregaremos localmente na nossa máquina.

Importante: Se você já fez os outros cursos dessa formação, já teve contato com a base de dados da Insight Places e pode já ter instalado em sua máquina.

Porém, precisamos que você carregue esses dados novamente na sua máquina. Isso porque os dados da Insight Places foram atualizados.

Isso é necessário para que você tenha os mesmos resultados e não enfrente nenhum problema nos códigos.

Para facilitar esse processo, temos alguns arquivos que vão te ajudar. O primeiro deles será o que criará a base de dados. Para carregá-lo, no MySQL Workbench, clique em "File", na barra de menu superior esquerda.

Depois, clique em "Open SQL Script". Feito isso, abre uma janela. Nela, acessamos a pasta em que foi feito o download do material do curso e seguimos o caminho projeto_formacao_mysql > scripts > 0-criacao_banco.sql.

Feito isso, o arquivo é aberto na ferramenta. Encontramos algumas orientações. Se já existia a tabela, será feito o drop, então criará a base de dados Insight Places e utilizá-la para criar todas as tabelas que já exploramos nos outros cursos da formação.

Então, a tabela de proprietários, clientes, endereços, hospedagens, as casas que vão ser alugadas, os aluguéis e as avaliações que esses aluguéis receberam. É com todas essas informações que faremos nossa análise.

Como já recarregamos o banco de dados aqui, não rodaremos esse script. Mas, para você que rodará, pode apertar a tecla "Ctrl + A" para selecionar todas as consultas e clicar no ícone de raio, na lateral superior esquerda do campo de código.

Carregando os dados no banco de dados

O próximo passo é carregar os dados no banco de dados. Na lateral superior direita, clicamos no ícone identificado por um quadrado com um retângulo em tom escuro na lateral esquerda, em vertical.

Feito isso, na lateral esquerda abre a aba de Schemas do banco de dados. Já temos a tabela insightplaces e as tabelas alugueis, avaliacoes, clientes, enderecos, hospedagens e proprietarios.

Para carregar esses dados, clicamos na tabela endereco. Depois clicamos com o botão direito e em "Table Data Import Wizard", esse é um facilitador para importar dados para a tabela.

Assim, abre uma nova janela de Table Data Import, onde faremos a seleção de um arquivo CSV. Para isso, clicamos no botão "Browse", na janela que abre encontramos seis arquivos numerados.

Mas, por que estão numerados? Pois as tabelas têm relacionamentos, isso significa que uma depende da outra. Então, precisamos criar esses dados em uma determinada ordem.

Por exemplo, clientes, proprietários e hospedagens podem ter o endereço. Então, temos uma tabela onde fazemos o registro dos endereços, e a tabela clientes, proprietários e hospedagens podem referir-se a essa tabela. Então, ela precisa ser criada primeiro.

Por exemplo, a hospedagem e o local que será alocado, tem um proprietário e um endereço. Então, existe esse relacionamento conectando as tabelas. Por isso, foram criadas em uma ordem específica, conforme abaixo.

• 1-enderecos.csv
• 2-clientes.csv
• 3-proprietarios.csv
• hospedagens.csv
• alugueis.csv
• avaliacoes.csv
• clientesNovo1.csv

Sabendo disso, clicamos em 1-enderecos.csv e depois no botão "Next", na lateral inferior direita da tela. Nisso, a ferramenta pergunta se queremos criar uma nova tabela ou inserir dados em uma existente.

Como clicamos direto na tabela, o campo de Usar a tabela existente com a opção insightplaces.enderecos já vem pré-selecionado. Mantemos e clicamos em "Next".

Na página seguinte, no campo Encoding, mantemos a opção utf-8, que é o padrão, selecionada. Abaixo, é definido o relacionamento entre o que ele encontrou no CSV e as colunas que existem na tabela. Então, a Source coluna é Endereço, e a coluna Endereço é a rua.

Como nosso CSV tem os mesmos nomes das colunas, que é o recomendado, ele fará essa associação corretamente, então, podemos manter todas as opções pré-selecionadas.

Abaixo, temos uma prévia da tabela, de como estão sendo carregadas essas informações. É muito importante analisar para verificar se está tudo correto.

Nesse caso, temos a coluna endereco_id, rua, numero, bairro, cidade, estado e o ceo. Todas essas informações parecem corretas, então, clicamos em "Next". Assim, as informações serão carregadas.

No nosso caso, não clicamos, pois nossos dados já foram carregados. Mas, você pode seguir esse passo. Lembrando que esse processo levará um tempo. É por isso que adiantamos anteriormente.

Você deve fazer isso para todas as tabelas, depois disso podemos começar a análise.

Fazendo as primeiras consultas

Com todos os dados carregados, como podemos confirmar se está tudo certo? Faremos as primeiras consultas e conheceremos um pouco como funciona o banco de dados.

Na barra de menu superior, clicamos no botão identificado pelo ícone de uma folha escrito "SQL" e um símbolo de "+", para criar um novo script.

Agora, começaremos a escrever a consulta. Primeiro, usaremos o banco de dados Insight Places, então escrevemos USE insightplaces; e pressionamos "Enter".

USE insightplaces;

Queremos analisar os proprietários, então escrevemos SELECT *, pois queremos visualizar todas as colunas, seguido de from proprietarios limit 10. Então, queremos apenas uma amostra desses dados para analisar. Depois, pressionamos "Ctrl + Enter".

SELECT from proprietarios limit 10;

Assim, temos o seguinte retorno:

Durante o curso, você percebera que ocultaremos algumas janelas que podem vir para o padrão no MySQL Workbench. Faremos isso, pois atrapalham a visualização. Nesse caso, você pode optar por mantê-las ou não no seu computador.

Nesse caso, minimizamos o painel lateral esquerdo e o inferior. Para isso clicamos no primeiro e segundo botão quadrados, localizados na lateral superior direita da tela.

Verificaremos se os dados foram carregados corretamente. Temos proprietario_id, nome, cpf_cnpj e contato. Aparentemente os dados foram carregados corretamente.

Recomendamos que você faça essa análise em todas as tabelas. Queremos que você explore as outras colunas dos clientes, imóveis, aluguéis, avaliações. Assim, você se sentirá mais confortável com essa base de dados.

Outro desafio que deixamos para você é verificar a quantidade de linhas. Esperamos que tenha 10 mil linhas para cada uma dessas tabelas.

Mas, não se preocupe. Também faremos isso juntos a seguir. Até o vídeo seguinte!

Conhecendo os dados e projeto da Insight places - Explorando os dados

Conseguiu elaborar consultas para conhecer melhor a base de dados? Agora, faremos isso juntos.

Analisando os dados de todas as tabelas

Como mencionamos anteriormente, queríamos analisar todas as tabelas. Portanto, fizemos o mesmo SELECT que fizemos para a tabela proprietario, com um limite de 10 linhas, para o restante das tabelas. O código ficou da seguinte forma:

USE insightplaces;

-- Display five rows from each table
SELECT * FROM proprietarios LIMIT 10;

SELECT * FROM clientes LIMIT 10;

SELECT * FROM enderecos LIMIT 10;

SELECT * FROM hospedagens LIMIT 10;

SELECT * FROM alugueis LIMIT 10;

SELECT * FROM avaliacoes LIMIT 10;

Selecionamos essas consultas e executamos, clicando no ícone identificado por um raio, na lateral superior esquerda da tela. Feito isso, na parte inferior da tela, temos separado por abas, todas as tabelas.

Tabela proprietarios

Na tabela proprietarios, temos informações como o nome do proprietário, CPF, CNPJ e o e-mail de contato. Em clientes temos o ID do cliente, nome, CPF e também o contato desse cliente.

Nenhum deles usa o endereço, inicialmente pensamos que talvez houvesse associação com a tabela endereco, mas não há. Nossa associação com a tabela endereco é justamente com a tabela hospedagens.

Tabela enderecos

A tabela endereco exploramos quando fizemos a prévia dos dados ao carregar o arquivo CSV, temos várias informações sobre o endereço. Quem utiliza esse endereço é a tabela hospedagem. E como ela faz isso? Ela tem um relacionamento com o ID.

Na tabela hospedagem, temos algumas informações. A hospedagem_id, um número que identificará cada uma das hospedagens.

Também temos o tipo, então se ela é uma casa, um apartamento, um hotel. O endereco_id, que está fazendo esse relacionamento com a tabela de endereço. O primeiro endereço da tabela de endereços é referente a esse imóvel, nesse caso, uma casa.

A coluna seguinte é referente ao proprietario_id, ou seja, quem está disponibilizando essa casa para alugar e se está ativo ou não. Essa última coluna serve para casos, por exemplo, caso uma casa esteja em manutenção e não deva aparecer no site.

Tabela aluguel

Na tabela aluguel temos a coluna aluguel_id, id_cliente e hospedagem_id. Além de informações importantes relacionadas ao negócio, como quando a pessoa começou a hospedagem data_inicio, quando terminou data_fim e quanto custou essa hospedagem preco_total.

Essas informações são muito importantes, vão nos ajudar a fazer análises, como, por exemplo, quando o aluguel dos imóveis está acontecendo com mais frequência, em quais datas. Além do preço dos imóveis em relação à época e local. Esses dados podem responder várias perguntas de negócio.

Tabela avaliacao

Por fim, temos a tabela avaliação. Nela, temos novamente o avaliacao_id, cliente_id referente ao cliente que fez essa avaliação. Além de hospedage_id referente a qual das hospedagens que recebeu a nota, a nota e comentario.

Um ponto de atenção é que temos que avaliar as notas e os comentários para entender como que funcionou a estadia desse cliente. Então, essa é uma das análises possíveis que podemos fazer, considerando trazer esses insights para os proprietários.

Feita essa análise, notamos como já temos perguntas que podem ajudar as pessoas proprietárias a tomar suas decisões. Uma última verificação que queremos fazer, antes de começarmos a responder essas perguntas é em relação à quantidade de linhas nessas tabelas.

Analisando a quantidade de linhas das tabelas

Montamos uma consulta que responde justamente isso.

SELECT
    (SELECT COUNT(*) FROM proprietarios) AS total_proprietarios,
    (SELECT COUNT(*) FROM clientes) AS total_clientes,
    (SELECT COUNT(*) FROM enderecos) AS total_enderecos,
    (SELECT COUNT(*) FROM hospedagens) AS total_hospedagens,
    (SELECT COUNT(*) FROM alugueis) AS total_alugueis,
    (SELECT COUNT(*) FROM avaliacoes) AS total_avaliacoes;

Ao executá-la, temos o seguinte retorno.

Essa consulta mostra que o total de proprietários tem 10 mil linhas, assim como o total de clientes, endereço, hospedagem, aluguéis e avaliações.

Essa é uma parecida com a que fizemos anteriormente. Porém, ao invés de apenas o asterisco, colocamos a função COUNT(). Essa função consegue contar a quantidade de linhas.

Temos seis tabelas, então são seis consultas que, na verdade, estão fazendo o papel de sub-consulta. Estamos fazendo cada uma dessas consultas, atribuindo a ela um apelido, então colocamos AS total, referente a contagem, e o nome da tabela.

Fazemos tudo isso entro de um SELECT. Assim, temos como resultado uma tabela que exibe a quantidade de linhas. Fica bem prático para fazer a leitura desses dados.

Essa é uma dica que deixamos para você e que pode ser utilizada em outras situações.

Lembre-se desse recurso de combinar diversos resultados em apenas uma visualização. Poderíamos exibir cada uma em um resultado diferente, mas seria mais trabalhoso de ler e analisar.

Concluímos a análise inicial. Esperamos que você tenha se desafiado a fazer outras análises iniciais importantes. Compartilhe conosco no Discord ou no fórum as ideias que você teve.

Agora, queremos fazer uma análise mais profunda, começar a responder nossos primeiros questionamentos em relação à base de dados.

Uma coisa que nos chamou a atenção é em relação a essas hospedagens. Quanto tempo esses imóveis estão ficando disponíveis para serem alugados e quantos desses dias estão sendo, de fato, utilizados?

Com as informações que temos, já é possível identificar como podemos chegar até essas respostas. Queremos que você pense sobre isso também, como você acha que, considerando as tabelas, podemos chegar nesses dados?

Te esperamos no vídeo seguinte!

Sobre o curso MySQL: aplicado para análise de dados

O curso MySQL: aplicado para análise de dados possui 104 minutos de vídeos, em um total de 47 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Conhecendo os dados e projeto da Insight places
• Criando métricas
• Simplificando a análise com procedures
• Criando views que respondem as dúvidas de negócio
• Entregando nosso projeto para outros times