Jornalismo Hacker. Analisando bancos de dados com SQL. Leonardo Leite

(1)

Jornalismo Hacker

Leonardo Leite

(2)

Slides utilizados no curso “Jornalismo Hacker” oferecido no SESC Pompeia (São Paulo) em julho de 2015.

(3)

(4)

O que são bancos de dados?

“São coleções organizadas de dados que se relacionam de forma a criar algum sentido

(Informação) e dar mais efciência durante uma pesquisa ou estudo.”

(5)

5

Alto nível x baixo nível

(6)

SGBD

(7)

7

(8)

Tabelas

Nome Raça Dono Idade

Fifi Poodle 3 3 anos

Rex Dobermann 1 2 anos

Napoleão Pinscher 1 1 ano

Floquinho Lhasa-Apso 2 5 anos

Id Nome Sexo UF Idade

1 José M SP 27 anos

Cachorros

(9)

9

CRUD

● Create ● Retrieve ● Update ● Delete

(10)

(11)

11

SQL

(12)

(13)

13

Dojô

● Apenas um computador ● Pilotagem é revezada

(14)

(15)

15

(16)

(17)

17

Abrir o sqlite

$sqlite3 ReceitasPartidos2010.sqlite

Comando pra abrir o Sqlite.

Argumento: diz qual banco abrir.

(18)

Abrir o sqlite

(outro jeito*)

$sqlite3

(19)

19

Comandos do sqlite

● .tables ● .schema ● .help

(20)

https://github.com/leonardofl/jornalismo_hacker

(21)

21

SELECT <campos>

FROM <tabela>

(22)

SELECT ValorReceita, NomeDoador, TipoReceita FROM receitas_sp

(23)

23

select * from homicidios where uf = "SP"

and populacao > 1000000;

Uma condição

(24)

Condição

populacao > 1000000

campo

operador

(25)

25

Operadores em uma

condição

● = ● != ● > ● >= ● < ● <= ● in

(26)

select * from homicidios where municipio in

("São Paulo", "Diadema", "São Caetano do Sul");

(27)

27

Operadores entre condições

● Or ● And ● Not

(28)

SELECT [DISTINCT] <campos>

FROM <tabela>

(29)

29

SELECT DISTINCT TipoReceita FROM receitas_sp;

(30)

SELECT DISTINCT SiglaPartido, TipoReceita FROM receitas_sp;

Nenhuma linha na saída repetida. Mas partidos repetem.

Distinct se aplica ao par (SiglaPartido, TipoReceita), e não somente a SiglaPartido.

(31)

31

FROM <tabela>

[WHERE <condição>]

(32)

SELECT SiglaPartido, ValorReceita FROM receitas_sp

(33)

33

FROM <tabela>

[ORDER BY <campos> [DESC]] [LIMIT <N>]

(34)

SELECT SiglaPartido, ValorReceita FROM receitas_sp

ORDER BY ValorReceita DESC LIMIT 10;

(35)

35

SELECT [DISTINCT] <campos*>

FROM <tabela>

(36)

Funções agregadoras

● Count ● Min ● Max ● Sum ● Avg

(37)

37

SELECT max(ValorReceita) from receitas_sp;

(38)

SELECT <campos>

FROM <tabela>

(39)

39

select txt_municipios from pac where txt_municipios like "ita%";

(40)

select txt_municipios from pac where txt_municipios not like "%,%";

Tabela possui algumas linhas nas quais o campo txt_municipios possuí vários municípios, separados por vírgula. Com esse

select a gente pega só as linhas que possuem apenas um município.

(41)

41

FROM <tabela>

[WHERE <condição>] [GROUP BY <campos>]

(42)

select uf, 1.0*sum(homicidios)/sum(populacao) as taxauf from homicidios group by uf order by

(43)

43

FROM <tabela>

[WHERE <condição>] [GROUP BY <campos>

[HAVING <condição>]]

(44)

SELECT SiglaPartido, SUM(ValorReceita) as Total FROM receitas GROUP BY Siglapartido HAVING Total>=1000000 ORDER BY Total desc;

(45)

45 SELECT <campos> FROM <tabela*> [WHERE <condição>] * ou SELECT...

Sub-select

(46)

SELECT SiglaPartido, Total FROM (SELECT SiglaPartido, SUM(ValorReceita) as Total FROM receitas GROUP BY SiglaPartido) WHERE

(47)

47

SELECT <campos>

FROM <tabela>

[WHERE <campo> <operador> <valor*>]

* ou SELECT...

(48)

select enemy_kia, summary from diary where enemy_kia = (select max(enemy_kia) from

diary where enemy_kia != '');

Obs: com esse comando não temos apenas uma linha com o máximo, mas sim todos os registros com o valor máximo.

(49)

49

Views

● Tabelas “virtuais”.

● Não ocupam espaço em disco. ● Podem ser temporárias.

(50)

Criando uma view

create view shoppings as select * from

contratos where vol2014 != "" AND vol2013 != "" AND cliente like "%shopping%";

(51)

51

SELECT <campos*>

FROM <tabela>

* ou expressões. Ex: campo1 + campo2.

(52)

Contas entre colunas

select enemy_kia + civilian_kia +

friendly_kia as mortes from diary where mortes > 100;

(53)

53

Contas entre colunas

● Atenção!

● sum() soma valores de uma mesma coluna em

linha diferentes.

● "coluna1 + coluna2" soma valores em colunas

diferentes, mas numa mesma linha (a soma é feita em cada linha).

(54)

Count distinct

● Uma forma de contar quantos partidos temos:

select count(*) from (select distinct siglaPartido from receitas_sp);

● Outra forma melhor:

select count(distinct siglapartido) from receitas_sp;

(55)

55

Tipos de dados

● Text (Obs.: texto vazio é “”) ● Integer (inteiros)

(56)

Data

● Usamos o tipo TEXT. ● Formato: 1789-07-14

● Ordem alfabética coincide com ordem

(57)

57

Tipos errados

● Nem tudo que parece um número é um número. ● CPF só tem dígitos.

● Mas a gente não faz contas com ele (essa é uma

pista).

● Mas o mais importante: um eventual zero a

esquerda não pode ser desprezado!

● Conclusão: CPF é um código numérico, mas não

(58)

Tipos errados

● Se usar um número como TEXT: min, max, sum

(59)

59

Texto plano

● Não possui formatação, só texto. ● Editável com o bloco de notas.

(60)

CSV

● Planilha em texto plano. ● Ex: id;nome;populacao;Id_pais 1;Xangai;17836133;2 2;Lagos;16060307;14 3;Karachi;13969284;15 4;Istambul;13907015;19 5;Mumbai;12478447;8 6;Moscou;12111194;17 7;São Paulo;11821873;1 8;Pequim;11716620;2

(61)

61

Codificação

● Tabela carácter número→ ● “Tudo é número”, Pitágoras

● Tabela básica: ASCII (sem acentos) ● Existem várias outras

● A mais usada: UTF-8

(62)

Convertendo codificação

$iconv -f ISO-8859-15 -t UTF-8 OriginalReceitasPartidosSP.txt >

(63)

63

Importação de dados

● Limpar / transformar dados. ● Gerar tabela “quadrada”.

● Exportar para CSV (UTF-8). ● No SQLite:

– .separator – .import

(64)

Exemplo de limpeza

● Mudando o formato de datas usando expressões regulares no LibreOfce.

(65)

65

Atenção

O jeito de escrever as expressões regulares varia ligeiramente de programa para programa.

(66)

(67)

67

Para aprender

(68)

Select com

expressões regulares

● sudo apt-get install sqlite3-pcre (no linux,

só uma vez)

● .load /usr/lib/sqlite3/pcre.so (no sqlite,

toda vez que o abrir)

● Aí pra fazer o select:

select * from veiculos where placa REGEXP '[a-zA-Z]{3}\d{4}'

(69)

69

Redirecionando saída

● Técnica 1

● .output FILENAME

● Técnica específca para o SQLite.

(70)

Redirecionando saída

● Técnica 2

● $sqlite3 base.sqlite “select * from tabela” >

saida.txt

● Não funciona no Windows.

● Mas funciona com qualquer programa de linha

(71)

71

Joins

● Relacionar valores em tabelas diferentes ● Tabela países: – Id – Nome – População ● Tabela cidades: – Id – Nome – População – IdPais

(72)

select c.nome, p.nome from cidades c, paises p

where c.idPais = p.id;

(73)

73

Joins

● Tabela pais_lingua:

– idLingua: aponta para id da tabela linguas – idPais: aponta para id de tabela países

● Cada registro de pais_lingua indica relação

país – língua.

● Ex: o registro (idPais=1, idLingua=2) indica que

(74)

select p.nome, l.nome from paises as p, pais_lingua as pl, linguas as l

where pl.idpais = p.id and pl.idlingua = l.id;

(75)

75

select l.nome, count(*) as qtd from

pais_lingua pl, linguas l where pl.idlingua = l.id group by l.id order by qtd desc;

Quantos países falam

cada língua

(76)

Dicas gerais de programação

● Baby steps: se algo é muito complicado,

quebre em vários passos. Adicione complexidade aos poucos.

● Faz sentido? Utilize outros comandos ou dê

uma olhada nos dados para ver se a saída de seu comando faz sentido.

(77)

77

Dicas gerais de programação

● Bons nomes! Se esforce na hora de batizar

colunas, tabelas, views etc. Faz toda diferença na hora usá-los.

● Dúvidas? Faça experiências! Tente de um jeito

e de outro e compare para chegar a

conclusões. Ao mesmo tempo, pesquise na Internet: provavelmente a resposta está lá!