Vetores
Um vetor em R é uma estrutura básica dentro da linguagem, que permite armazenar uma quantidade numérica ou string (letras ou caracteres), em um único objeto. O vetor possui apenas 01 única dimensão.
SQL
Structured query Language ou Linguagem de Consulta Estruturada ou SQL, é uma linguagem de pesquisa declarativa padrão para banco de dados relacionados. Muitas das características originais do SQL foram inspiradas na álgebra relacional.
O Artigo aqui tem como o principal objetivo demonstrar as principais estruturas dentro da linguagem SQL. Porém além de ensinarmos as principais estruturas, iremos trabalhar com a linguagem R. Se você não tiver o conhecimento de linguagem R, a cada semana será postado um artigo novo, com conhecimentos básicos de modelagem de dados, como carregar um arquivo, como salvar arquivo, como realizar uma análise exploratória, pré processamento de dados, construção de modelos preditivos e muito mais.
Cada semana temos um tema diferente, totalmente gratuito, e aceitamos também opniões de novos temas.
As bibliotecas são conjuntos de funções já desenvolvida que irá facilitar o nosso trabalho com algum
determinado assunto. Este artigo irá trabalhar expecificamente com o pacote sqldf, um fremework que nos
permite utilizar a semantica do SQL, dentro do RStudio. Se você nunca utilizou esta biblioteca primeiramente
você deve realizar a seguinte comando:
install.packages(“sqldf”)
Este comendo é realizado apenas uma vez, após ter instalado em sua máquina você nas próximas vezes só precisar carrega-lo.
library(sqldf)Carregando o conjunto de dados
Iremos carregar um conjunto de dados para conseguirmos trabalhar com SQL. A Função utilizada abaixo é uma função base do RStudio. Estes Conjuntos de dados, encontra-se no repositório no github.
tb_func<-read.csv('https://raw.githubusercontent.com/rodolffoterra/rodolffoterra.github.io/main/dados/funcionarios.csv')DQL Instructions
Linguagem de consulta de Dados (Data Query Language) faz parte do agrupamento básico de sub-linguagens SQL. Essas sub-linguagem são categorizadas principalmente em quatro categorias.
As instruções DQL são usadas para realizar consultas dos dados. O objetivo dos comandos DQL é obter a relação do esquema com base na consulta passada a ele.
Select
Cada tabela é dividida em entidades menores chamadas campos. Os campos da tabela tb_func consistem em ID, estado_civil, grau_instrucao, numero_filhos, salario_hora e idade. Um campo é uma coluna em uma tabela projetada para manter informações específicas sobre cada registro na tabela.
Um registro, também chamado de linha, é cada entrada individual que existe em uma tabela. Por exemplo, existem 10 registros na tabela Clientes acima. Um registro é uma entidade horizontal em uma tabela.
Uma coluna é uma entidade vertical em uma tabela que contém todas as informações associadas a um campo específico em uma tabela. Primeiramente iremos conhecer o nosso conjunto de dados.
sqldf("SELECT * FROM tb_func")| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 1 | casado | ensino fundamental | NA | 4.00 | 30 | interior |
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 3 | casado | ensino fundamental | 2 | 5.25 | 50 | capital |
| 4 | solteiro | ensino medio | NA | 5.73 | 25 | outra |
| 5 | solteiro | ensino fundamental | NA | 6.26 | 70 | outra |
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
| 7 | solteiro | ensino fundamental | NA | 6.86 | 30 | interior |
| 8 | solteiro | ensino fundamental | NA | 7.39 | 40 | capital |
| 9 | casado | ensino medio | 1 | 7.59 | 4.3 | capital |
| 10 | solteiro | ensino medio | NA | 7.44 | 60 | outra |
Podemos também observar a quantidade de linhas que possuimos em nosso conjunto de dados trocando o * por count(*)
sqldf("SELECT count(*) FROM tb_func")
| ## count ## 10 |
Iremos visualizar apenas a coluna salario_hora.
sqldf("SELECT salario_hora FROM tb_func")| ## salario_hora ## 1 4.00 ## 2 4.56 ## 3 5.25 ## 4 5.73 ## 5 6.26 ## 6 6.66 ## 7 6.86 ## 8 7.39 ## 9 7.59 ## 10 7.44 |
Distinct
Para eu saber todos os valores individuais de uma única coluna baste utilizar a função distinct.
sqldf("SELECT DISTINCT reg_procedencia from tb_func")| ## reg_procedencia ## 1 interior ## 2 capital ## 3 outra |
Order by
Agora iremos visualizar todas as colunas porém com a coluna salario_hora em ordem decrescente.
sqldf("SELECT * FROM tb_func ORDER BY salario_hora DESC")| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 9 | casado | ensino medio | 1 | 7.59 | 4.3 | capital |
| 10 | solteiro | ensino medio | NA | 7.44 | 60 | outra |
| 8 | solteiro | ensino fundamental | NA | 7.39 | 40 | capital |
| 7 | solteiro | ensino fundamental | NA | 6.86 | 30 | interior |
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
| 5 | solteiro | ensino fundamental | NA | 6.26 | 70 | outra |
| 4 | solteiro | ensino medio | NA | 5.73 | 25 | outra |
| 3 | casado | ensino fundamental | 2 | 5.25 | 50 | capital |
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 1 | casado | ensino fundamental | NA | 4.00 | 30 | interior |
Select com condicional (WHERE)
Utilizaremos a função SELECT porém uzando alguma condição para saber a quantidade de pessoas com idade igual a 40 anos, em nosso conjunto de dados:
sqldf("select count(*) FROM tb_func WHERE idade = 40")| ## count ## 3 |
Para saber a quantidade de pessoas com idade diferente bas eu utilizar o seguinte sinal: !=
sqldf("select count(*) FROM tb_func WHERE idade != 40")| ## count ## 8 |
Podemos usar o sinal e maior (>) e o sinal de menor (<), com condição de idade. Quantidade de pessoas maiores que 40 anos
sqldf("select count(*) FROM tb_func WHERE idade > 40")| ## count ## 4 |
Quantidade de pessoas menores ou igual a 40 anos
sqldf("select count(*) FROM tb_func WHERE idade <= 40")| ## count ## 6 |
Agora utilizaremos duas condições em conjunto: eu quero que me retorne as pessoas com idade menor que 30 anos e que sejam casadas.
And
sqldf("SELECT * FROM tb_func WHERE idade < 30 AND estado_civil = 'casado'")| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
Somente a coluna salario_hora, de pessoas com idade maior que 30 anos e que não são casados.
sqldf("SELECT (salario_hora) FROM tb_func WHERE idade > 40 AND estado_civil != 'casado'")| ## salario_hora ## 1 6.26 ## 2 7.44 |
Or
Quando utilizamos a condição OR, queremos ou 1 condição ou outra condição. Como por exemplo, abaixo, queremos todas as colunas ou pessoas que tenham acima de 50 anos, ou pessoas são são casadas. Tanto uma condição ou a outra serve. Não existindo a necessidade de ser as 02 condições juntas.
sqldf("SELECT * FROM tb_func WHERE idade > 50 OR estado_civil = 'solteiro'")| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 4 | solteiro | ensino medio | NA | 5.73 | 25 | outra |
| 5 | solteiro | ensino fundamental | NA | 6.26 | 70 | outra |
| 7 | solteiro | ensino fundamental | NA | 6.86 | 30 | interior |
| 8 | solteiro | ensino fundamental | NA | 7.39 | 40 | capital |
| 10 | solteiro | ensino medio | NA | 7.44 | 60 | outra |
Between
Podemos utilizar a condicional between, sendo que ela incluio a condição na contagem. Pessoas maiores ou iguais a 30 anos e menores ou iguais a 35 anos
sqldf('SELECT COUNT(*) FROM tb_func WHERE idade BETWEEN 30 AND 35')| ## count ## 2 |
Se a condição between não estiver clara, podemos utilizar a função and.
sqldf('SELECT COUNT(*) FROM tb_func WHERE idade BETWEEN 30 AND 35')| ## count ## 2 |
Like
Para encontrar uma determinada palavra eu posso utilizar a função Like. Querendo enontrar a palavra medio na coluna grau_instrucao.
sqldf("SELECT * from tb_func WHERE grau_instrucao LIKE '%medio%' "))| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 4 | solteiro | ensino medio | NA | 5.73 | 25 | outra |
| 9 | casado | ensino medio | 1 | 7.59 | 4.3 | capital |
| 10 | solteiro | ensino medio | NA | 7.44 | 60 | outra |
IN
Podemos também encontrar um conjunto de idade por exemplo, usando a função IN, querendo que apenas retorne pessoas com idade de 20, 30, e 40 anos.
sqldf("SELECT * from tb_func WHERE idade IN (20,30,40,50) ")| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 1 | casado | ensino fundamental | NA | 4.00 | 30 | interior |
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 3 | casado | ensino fundamental | 2 | 5.25 | 50 | capital |
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
| 7 | solteiro | ensino fundamental | NA | 6.86 | 30 | interior |
| 8 | solteiro | ensino fundamental | NA | 7.39 | 40 | capital |
NULL
Podemos identificar os registros na coluna numeor_filho que não possui registro, valores missing, usando IS NULL.
sqldf("SELECT * from 'tb_func' WHERE numero_filhos IS null")| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 1 | casado | ensino fundamental | NA | 4.00 | 30 | interior |
| 4 | solteiro | ensino medio | NA | 5.73 | 25 | outra |
| 5 | solteiro | ensino fundamental | NA | 6.26 | 70 | outra |
| 7 | solteiro | ensino fundamental | NA | 6.86 | 30 | interior |
| 8 | solteiro | ensino fundamental | NA | 7.39 | 40 | capital |
| 10 | solteiro | ensino medio | NA | 7.44 | 60 | outra |
Ou apenas as linhas que possuem registro, com IS NOT NULL.
sqldf("SELECT * from 'tb_func' WHERE numero_filhos IS NOT null")| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 3 | casado | ensino fundamental | 2 | 5.25 | 50 | capital |
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
| 9 | casado | ensino medio | 1 | 7.59 | 4.3 | capital |
UPDATE
Sabemos que possuem 06 registro com valores nulos em nosso conjunto de dados, então, trocarei os valores null, por uma quantidade de filhos errada, colocaremos o número 10.
tb_func <- sqldf(c("update tb_func set numero_filhos = 10 where numero_filhos IS null", "select * from main.tb_func"))| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 1 | casado | ensino fundamental | 10 | 4.00 | 30 | interior |
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 3 | casado | ensino fundamental | 2 | 5.25 | 50 | capital |
| 4 | solteiro | ensino medio | 10 | 5.73 | 25 | outra |
| 5 | solteiro | ensino fundamental | 10 | 6.26 | 70 | outra |
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
| 7 | solteiro | ensino fundamental | 10 | 6.86 | 30 | interior |
| 8 | solteiro | ensino fundamental | 10 | 7.39 | 40 | capital |
| 9 | casado | ensino medio | 1 | 7.59 | 4.3 | capital |
| 10 | solteiro | ensino medio | 10 | 7.44 | 60 | outra |
Delete
Anteriormente colocamos no lugar nos valores null na coluna numero_filhos, o valor 10, agora iremos deleter todos os registros que tenham o valor 10 da coluna numero_filhos
tb_func <- sqldf(c("DELETE from 'tb_func' WHERE numero_filhos IS 10", "select * from main.tb_func"))| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 3 | casado | ensino fundamental | 2 | 5.25 | 50 | capital |
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
| 9 | casado | ensino medio | 1 | 7.59 | 4.3 | capital |
Insert
Quando quizermos acrescentar registro em nossa tabela utilizaremso a função insert.
tb_func <- sqldf(c(" INSERT INTO tb_func ( ID, estado_civil, grau_instrucao, numero_filhos, salario_hora, idade, reg_procedencia) values (11, 'casado', 'ensino medio', 3, 4.5, 30, 'capital')", "select * from main.tb_func"))Podemos observar que tais informações da query acima, foram incluida na quinta linha, uma vez que o nosso conjunto de dados foram deletados todos os valores null, restando apenas 04 registos.
| ID | estado_civil | grau_intrucao | numero_filhos | salario_hora | idade | reg_procedencia |
|---|---|---|---|---|---|---|
| 2 | solteiro | ensino fundamental | 1 | 4.56 | 40 | capital |
| 3 | casado | ensino fundamental | 2 | 5.25 | 50 | capital |
| 6 | casado | ensino fundamental | 0 | 6.66 | 20 | interior |
| 9 | casado | ensino medio | 1 | 7.59 | 4.3 | capital |
| 10 | solteiro | ensino medio | NA | 7.44 | 60 | outra |
| 11 | casado | ensino medio | 3 | 4.5 | 30 | capital |
Valor Mínimo, máximo e média
Agora pegaremos a coluna salario_hora, e criaremos uma query que teão o valor mínimo que chamaremos de Mim, o valor máximo que chamaremos de Max, o valor médio que chamaremos de Média, a sumatória de todos os valores, que chamaremos de Soma e a quantidade de registro que chamaremos de Count.
sqldf("select MIN(salario_hora) Mim, MAX(salario_hora) Max, AVG(salario_hora) Media, SUM(salario_hora) Soma, count(*) Count from tb_func ")
| ## Min Max Media Soma Count ## 4.5 7.59 5.712 28.56 5 |
Group By
Esta função é de estrema importância, pois abaixo iremos encontrar o salário_hora média por região
sqldf("SELECT round(AVG(salario_hora),2) Média, reg_procedencia regiao FROM tb_func GROUP BY reg_procedencia")| ## Media Regiao ## 5.48 capital ## 6.66 interior |
Agora utilizaremos 02 colunas para a média de salário hora, reg_procedencia e grau_instrucao
sqldf("SELECT round(AVG(salario_hora),2) Media, reg_procedencia Regiao, grau_instrucao Ensino FROM tb_func GROUP BY reg_procedencia, grau_instrucao")| ## Media Regiao Ensino ## 4.91 capital ensino fundamental ## 4.05 capital ensino medio ## 6.66 interior ensino fundamental |
Exercício
Quem tiver suas dúvida, sugestões ou quiser enviar a sua resposta, fica a vontade para o seguinte: E-mail.
1. Crie uma instrução SQL que retorna a média de idade, número de filhos e grau de instrução dos funcionários cujo salário_hora estiver acima da média de todos os funcionários. Retorne os dados somente de funcionários da capital e estado civil casado, com ordem decrescente da média de idade.
2. Retorne todos os registro dos funcionários com 2 filhos.
3. Retorno a média de salário hora or estado O comando do novo conjunto de dados no RStudio segue abaixo:
tb_endereco <- data.frame("id_end" = c(1001,1002,1003,1004,1005), "rua" = c('Jaguar', 'Mercedes Benz','BMW','Ferrari','McLaren'), "numero" = c(40, 140, 20, 32, 45), "bairro"= c('Tijuca','Centro','Tijuca','Centro','Centro'), "cep" = c('24239-900','12098-900',"23232-900",'99872-900','43982-900'), "estado" = c('Rio de Janeiro','Minas Gerais','Rio de Janeiro','Minas Gerais','Minas Gerais'), "pais" =c('Brasil', 'Brasil', 'Brasil', 'Brasil', 'Brasil'), "id_func" = c(2, 6, 3, 11, 17))Outros Artigos
Matrix
Matrizes é caracterizado como uma coleção de vetores, sendo todos do mesmo tipo (numérico ou caracteres) armazenados entre linhas e colunas.
Data Frame
Um data frame é semelhante a uma matriz mas as suas colunas têm nomes e podem conter dados de tipo diferente. Um data frame pode er visto como uma tabela de uma base de dados, em que cada linha corresponde a um registo (linha) da tabela.
Análise Exploratória
Em estatística, a análise exploratória de dados é uma abordagem à análise de conjuntos de dados de modo a resumir suas características principais, frequentemente com métodos visuais.
Regressão Linear
Em estatística ou econometria, regressão linear é uma equação para se estimar a condicional de uma variável y, dado os valores de algumas outras variáveis x.
Clusterização
Em Breve
Projeto - Linguagem Python
Vamos realizar uma análise de dados com base em séries temporais e análises geoespaciais, além de outras técnicas exploratórias
mais gerais, para examinar se essas previsões se tornaram realidade em Los Angeles desde que a legalização entrou em vigor. Nosso foco
principal será nos dados disponíveis sobre detenções por crimes relacionados à maconha e na taxa geral de crimes nas proximidades de
dispensários. Vamos focar principalmente nem: Permição o cultivo de maconha perto de escolas e parques;
A legalização Prejudica ou não as comunidades pobres com problemas de dependência por meio do influxo de novos pontos de vendas de maconha;
E se com legalização existiu um aumento de criminalidade nas áreas próximas aos pontos de vendas.
Os dados usados neste projeto estão disponíveis publicamente e são oferecidos pelo portal de dados abertos do governo de Los Angeles.
Você é favor ou contra à legalização da Maconha? Acompanhe o projeto passo a passo, leia atentamente cada comentário e então emita sua
opinião com base em dados.
