trigésimo segundo tijolo - REVISÃO SQL PARTE 02

OPERADORES DE AGREGAÇÃO
A função COUNT é usada para contar o número de linhas em uma tabela que atendem a uma determinada condição. Isso pode ser útil para determinar o tamanho da tabela ou para contar o número de linhas que atendem a uma condição específica.
A função SUM é usada para somar os valores em uma coluna da tabela. Isso pode ser útil para calcular o total de vendas ou a soma dos salários em uma tabela.
Ou seja eu posso ter 1 milhão de linhas e 13 milhões de objetos. O número de linhas eu vou contar usando o COUNT e o SUM eu vou somar o valores presentes nas linhas, uma linha pode ter um valor nulo ou número alto.
Banana é maior que rádio. 
FROM 
WHERE 'banana' > 'radio'

*************

No uso dos operadores booleanos AND e OR, o AND é aditivo e o OR é exclusivo. Estes dois operadores podem acrescentar ou excluir linhas no resultato final por conta dos seus condicionamentos. 
No AND o resulta de uma soliticação depende de outra solicitação, já no caso do OR as sentenças envolvidas é independente.

*************

HAVING eu consigo apresentar resultados dos filtros aplicados em funções agregadoras, diferentemente do WHERE e GROUP BY.
Neste caso eu repito a função presente no SELECT que eu quero agregar por exemplo COUNT (isso e aquilo) eu reescrevo no HAVING COUNT (isso e aquilo) > 250 (por exemplo) 

O operador IS NULL apresenta todas as linhas vazias e o IS NOT NULL apenas as linhas preenchidas com algum dados, excluindo assim as linhas vazias. Este operador é usado para identificar resultados nulos e bastante eficiente para 'higienizar' ou fazer um tratamento nos dados antes de uma análise mais apurada ser iniciada.

Já a função LIMIT é ideal para se limitar o número de resultados, tal qual na função head() do python, também é indicado durante o tratamento de dados para não causar um superaquecimento, você limita o número de resultados e assim o seu pc não esquenta ou pior não necessita ser reiniciado.

SELECT

Usando um operador maior que para comparar 'palavras' não irei obter uma resposta conclusiva pois não é possivel comparar palavras.

BETWEEN permite filtrar intervalos ou seja é uma simplificação do uso do AND repetidas vezes.

Caso você saiba exatamente os números dentro do intervalo pode ainda fazer uso do operador IN para especificar uma inclusão ou do NOT IN para especificar uma exclusão.

Já o LIKE é usado como um buscador de padrão nos resultados de textos. Caso queira buscar uma linha em que haja a categoria que se inicia pela letra b, devo escrever WHERE nome_da_tabela LIKE 'b%'

como se trata de texto fazemos uso das aspas 'b' e o simbolo do %. Se foram palavras terminas em a, ficara assim LIKE '%a'. Caso esteja procurando um email em que haja  'f%a%o' , que resultaria em fernando5885@email.com  ou  na minha base dados eu quero apenas os emails do '%gmail.com'

Já o ORDER BY serve para organizar os dados de forma crescente ASC ou descrescente DESC

                                                                 **************

CARDINALIDADE E TIPOS DE UNIÃO DE TABELAS

INNER JOIN ...ON - Retorna apenas as linhas correspondentes, ou seja, se não houver correspondencia a linha é eliminada, neste caso não aparece no resultado final. 

(1:1) Monoganismo um homem para um mulher.

LEFT JOIN ...ON - Retorna apenas as linhas da esquerda e caso não haja correspondencia as linhas são apresentadas com valores "null" ou "N/A"

(1:N) Uma mãe é para muitos filhos, mas muitos filhos não são para uma mãe.

RIGHT JOIN ...ON - Retorna apenas as linhas da direita.

(N:1) Muitos livros para uma editora.

FULL OUTER JOIN ...ON - Retorna todas as linhas. 

(N:N) Um autor pode escrever muitos livros e um livro pode ter muitos autores.

******************

Subquery como uma nova coluna da consulta ( Subquery no SELECT ) 

Subquery como filtro de uma consulta ( Subquery no WHERE ) 

Subquery como fonte de dados da consulta principal ( Subquery no FROM )

trigésimo primeiro tijolo - SQL REVISÃO - SELECT, FROM, WHERE e GROUP BY

 

Em SQL, usamos o GROUP BY para indicar a coluna ( dimensão ) do agrupamento, ou seja para

enquanto o FROM é a tabela escolhida, o SELECT é a coluna ou as colunas escolhidas e o WHERE é o filtro por linhas. 

Cabe salientart que a ordem como se organiza as colunas na clásula SELECT é a ordem que irá aparecer na tela. Será a mesma ordem apresentada. Por questões de boas práticas se põe na mesma ordem no SELECT e no GROUP BY.

O GROUP BY é um agrupamento por CATEGORIAS (por exemplo você tem definido uma quantidade X de cliente em 3 categorias [gold, premium, basic], o GROUP BY pode organizar por categoria a função agregadora que você escolher tais como média, soma, contagem...).

                                                                      * * *

Ainda sobro GROUP BY se houve sub categorias você pode adiciona-las, isso causará um efeito GRANULAR e a medida que vai subcategorizando você vai granulando seus dados.

FAIXA ETÁRIA
01 - 15 // 16 - 30 // 31 - 45  // 46 - 60 // 61 - 75 // 76 -100 (Temos 5 faixa etárias)

GENEROS 

HOMENS // MULHERES // NÃO BINÁRIO // NÃO DECLARADOS (Temos 4 gêneros)

TIPOS DE GASTOS

ALIMENTAÇÃO  // VESTUÁRIO // SAÚDE // EDUCAÇÃO // LAZER (Temos 5 tipos de gastos)

A medida que quero especificar a minha amostra, ou seja dependendo do tamanho do recorte eu vou precisar agrupar mais ou menos inserindo mais ou menos colunas através do GROUP BY. 

Por exemplo: As compras de vestuário de uma mulher de 31 - 45, podem ser subcategorizadas por acessórios, por preço, por forma de pagamento.

Colunas apenas com quantidades podem ser agrupadas, mas diferente das colunas de categorias ela não tem o mesmo potencila de informações a oferecer. 

Observação importante, se usar determinada coluna no GROUP BY e ela contiver dados com datas, possivelmente será preciso usar o DATE, mas para não haver choques/conflitos será preciso usar o DATE no SELECT também. 

Não é possivel agrupar (usar o GROUP BY) com funções agregadoras (SUM // COUNT // AVG // MIN // MAX )

Resumindo:

SELECT (seleciona coluna(s))
FROM (seleciona tabela(s))
WHERE (seleciona linha(s))
GROUP BY (seleciona colunas e organiza por categoria(s)).

Vejamos um exemplo:

FASE 01

SELECT

p.product_category_name

FROM products p

FASE 02

SELECT

p.product_category_name,

COUNT( p.product_id )

FROM products p

FASE 03

SELECT

p.product_category_name,

COUNT( p.product_id ),

MIN( p.product_width_cm )

FROM products p

FASE 04

SELECT

p.product_category_name,

COUNT( p.product_id ),

MIN( p.product_width_cm ),

SUM( p.product_weight_g ),

AVG( p.product_weight_g )

FROM products p

FASE 05 (Precisei renomear para a foto ficar com uma boa qualidade em uma tamanho cada vez maior, ocupando menos espaço nas celulas dos nomes das colunas)

SELECT

p.product_category_name,

    COUNT( p.product_id ) as qty_,

    MIN( p.product_width_cm ) as min_,

    SUM( p.product_weight_g ) as sum_,

    AVG( p.product_weight_g ) as aveg_,

    MAX( p.product_height_cm ) as max_

FROM products p

FASE 06

SELECT

    p.product_category_name,

    COUNT( p.product_id ) as qty_,

    MIN( p.product_width_cm ) as min_,

    SUM( p.product_weight_g ) as sum_,

    AVG( p.product_weight_g ) as aveg_,

    MAX( p.product_height_cm ) as max_,

    COUNT( DISTINCT p.product_length_cm ) as length_

FROM products p

FASE 07 - Resultado final da minha QUERY

SELECT 
        p.product_category_name, 
       COUNT( p.product_id ), 
        SUM( p.product_weight_g ), 
        AVG( p.product_weight_g ), 
        MAX( p.product_height_cm ), 
        MIN( p.product_width_cm ), 
        COUNT( DISTINCT p.product_length_cm ) 
FROM products p 
GROUP BY p.product_category_name

trigésimo tijolo - Anatonia SQL

 

Um BANCO DE DADOS tem inúmeras tabelas, para escolhermos uma vamos fazer uso do comando FROM.

Imagine que toda esta TABELA se chama FROM.

A coluna VALIDADE sem chama SELECT

Se um comando contiver mais de uma SELECT serão apresentadas apenas as colunas selecionadas.

E a LINHA se chama WHERE.

No exemplo acima temos um SELECT, um FROM e um WHERE

Vigésimo nono tijolo - SQL BASICO_02

 

1. Qual o número de clientes únicos do estado de Minas Gerais?

SELECT #traducao livre do comando...

COUNT(DISTINCT customer_id) #conte de forma distinta os clientes...

FROM customer c #da coluna clientes_id.

WHERE c.customer_state = 'MG' #de Minas Gerais.

2. Qual a quantidade de cidades únicas dos vendedores do estado de Santa Catarina?

SELECT

COUNT(DISTINCT s.seller_city)

FROM sellers s

WHERE s.seller_state = 'SC'

# Traduzindo:

# selecione

a coluna vendedores por cidade.

apresente os vendendores únicos por cidade

conte todos os vendedores únicos pod cidade

# from

retire esta informação da tabela vendedores

# where

mostre quantos deles são do estado de Santa catarina

3. Qual a quantidade de cidades únicas de todos os vendedores da base?

SELECT

COUNT(DISTINCT s.seller_city)

FROM sellers s

# A pergunta foi mal formulada, qual a quantidade total de vendedores únicos

por cidade, ou seja há vendedores que podem esta vendendo em duas cidades,

mas só vai contabilizar uma única vez.

order

4. Qual o número total de pedidos únicos acima de R$ 3.500?

SELECT

COUNT(DISTINCT order_id)

FROM order_items oi

WHERE oi.price > 3500.

# Lembrete o 3.500,00 na conotação monetária adotada o 3500. o ponto vem no lugar

da virgula.

5. Qual o valor médio do preço de todos os pedidos?

SELECT

AVG(oi.price)

FROM order_items oi

6. Qual o maior valor de preço entre todos os pedidos?

SELECT

MAX(price)

FROM order_items oi

7. Qual o menor valor de preço entre todos os pedidos?

SELECT

MIN(price)

FROM order_items oi

8. Qual a quantidade de produtos distintos vendidos abaixo do preço de R$ 100.00?

SELECT

COUNT(DISTINCT product_id )

FROM

order_items oi

WHERE oi.price < 100.

9. Qual a quantidade de vendedores distintos que receberam

algum pedido antes do dia 23 de setembro de 2016?

SELECT

COUNT(DISTINCT seller_id )

FROM

order_items oi

WHERE shipping_limit_date < '2016-09-23 00:00:00'

10. Quais os tipos de pagamentos existentes?

SELECT

DISTINCT op.payment_type

FROM order_payments op

# A pergunta é quais e não quantos.

11. Qual o maior número de parcelas realizado?

SELECT

MAX(payment_installments) as parcelado

FROM order_payments op

# O "parcelado" foi para renomear a coluna payment_installments

12. Qual o menor número de parcelas realizado?

SELECT

MIN(payment_installments)

FROM order_payments op

13. Qual a média do valor pago no cartão de crédito?

SELECT

AVG(op.payment_value)

FROM order_payments op

WHERE payment_type = 'credit_card'

14. Quantos tipos de status para um pedido existem?

SELECT

COUNT(DISTINCT order_status)

FROM orders o

15. Quais os tipos de status para um pedido?

SELECT

DISTINCT order_status

FROM orders o

16. Quantos clientes distintos fizeram um pedido?

SELECT

COUNT(DISTINCT customer_id)

FROM orders o

18. Qual a quantidade máxima de fotos de um produto?

SELECT

MAX( DISTINCT product_photos_qty)

FROM products p

19. Qual o maior valor do peso entre todos os produtos?

SELECT

MAX( DISTINCT product_weight_g)

FROM products p

20. Qual a altura média dos produtos?

SELECT

avg (DISTINCT product_height_cm)

FROM products p

Vigésimo oitavo tijolo - SQL BASICO_01

 

Gere uma tabela com o id do cliente, a cidade e o estado onde ele vive.

SELECT

c.customer_id ,

c.customer_city ,

c.customer_state

FROM customer c

LIMIT 10

2. Gere uma tabela com o id do cliente e a cidade, somente dos clientes que vivem em Santa Catarina

SELECT

c.customer_id ,

c.customer_city

FROM customer c

WHERE c.customer_state = 'SC'

LIMIT 10;

Explicando: Com o comando select eu selecione as colunas do ID e da cidade do cliente.

mas de qual estado, ou melhor de onde? Neste exemplo usei o WHERE e limitei o resulado a 10.

3. Gere uma tabela com o id do cliente e o estado, somente dos clientes que vivem em Florianópolis.

SELECT

c.customer_id ,

c.customer_state

FROM customer c

WHERE c.customer_city = 'florianopolis'

LIMIT 10;

Explicando: Neste exemplo troquei o Estado pela cidade. O comando é o mesmo.

4. Gere uma tabela com o estado, latitude e longitude do estado de Sã Paulo

SELECT

g.geolocation_lat ,

g.geolocation_lng,

g.geolocation_state

FROM geolocation g

WHERE g.geolocation_state = 'SP' # Do estado de SP

LIMIT 10;

5. Gere uma tabela com o id do produto, a data de envio e o preço, somente para produtos acima de 6300 

SELECT

oi.product_id,

oi.shipping_limit_date,

oi.price

FROM order_items oi

WHERE oi.price > 6300

LIMIT 10;

6. Gere uma tabela com o id do pedido, o tipo de pagamento e o número de parcelas, somente para produtos com parcelas menores que 1.

SELECT

op.order_id ,

op.payment_type ,

op.payment_installments #pagamentos_parcelado

FROM order_payments op

WHERE op.payment_installments < 1

LIMIT 10;

7. Gere uma tabela com o id do pedido, id do cliente, o status do pedido e a data de aprovação , somente para compras aprovadas até dia 10 de Maio de 2016

SELECT

o.order_id ,

o.customer_id ,

o.order_status ,

o.order_approved_at

FROM orders o

WHERE o.order_approved_at < '2016-10-05'

 

Vigésimo sétimo tijolo - Contagem regressiva com e sem texto

 

Antes de iniciarmos a contagem regressiva iremos exercitar o  for

Iniciamos uma forma de contagem regressiva sem texto e logo na figura posterior encontramos uma instrução que insere em inglês os números.

Fazendo uso da biblioteca "inflect" e da instrução ".engine()" e do comando p.number_to_words, o capitalize é para que as letras iniciais sejam escritas com letras maiúsculas.

Vigésimo sexto tijolo - Preenchendo dados faltantes

Note que o tempo de alguns atletas não foi registrado por algum erro no processo de medição. 

Observando os dados e tendo um conhecimento prévio do desempenho de cada atleta, você, como cientista de dados, resolve que é razoável para este caso específico atribuir o tempo médio de todos os atletas aos dados faltantes.

Primeiramente importamos a biblioteca pandas, a seguir o matplotlib.
Definimos que haverá uma figura e seu tamanho.

import pandas as pd

import matplotlib.pyplot as plt

plt.rc('figure', figsize = (15, 7))

dados = pd.read_csv('amostrado.csv', sep = ';')

Veja que no nosso dataframe columns= Corredor (Nome da pessoa), Melhor tempo (desempenho)

Como é possivel de se notar alguns atletas não possuem dados sobre seus desempenhos. Por isso podemos utilizar uma instrução chamada de fillna. No pandas ela é usada para preencher valores faltantes nas tabelas NaN.

Neste caso ela repitiu o valor do atleta anterior. O atleta Pedro antes sem valor definido recebeu o valor de Marcos. Sandro recebeu o valor de Denis.

Vigésimo quinto tijolo - Mais sobre gráficos

 

Estas linhas representam:

%matplotlib inline

import pandas as pd

import matplotlib.pyplot as plt

plt.rc('figure', figsize = (15, 8))

Inicialmente a primeira é um comando para o jupyter apresentar o gráfico na própria linha, caso use o colab não é necessário pô-lo. As linhas a seguir são importações de bibliotecas. O plt.rc é uma configuração específica do matplotlib para um ajuste do tamanho padrão das figuras criadas/geradas por ele. Como iremos criar 4 gráficos em uma figura é necessário garantir que haja espaço para todas.

Nesta linha abrimos um arquivo que carregado no colab via upload, o uso do sep=';' é para a reconfiguração das informações.

dados = pd.read_csv('/content/sem_outliers.csv', sep=';')

dados

O dataframe gerado é este:

A seguir temos temos outras instruções como definir uma área.

area = plt.figure()

Na área criada iremos criar uma figura com 4 gráficos, por inserimos 4 variáveis são elas:

g1 = area.add_subplot(2, 2, 1)

g2 = area.add_subplot(2, 2, 2)

g3 = area.add_subplot(2, 2, 3)

g4 = area.add_subplot(2, 2, 4)

Cada gráfico terá 2 linhas, 2 colunas, e o último número diz respeito a posição do gráfico dentro da figura. 

Escolhemos criar 4 gráficos distintos, são eles um scatter (um gráfico de dispersão), um histograma, uma amostra que apresentará dados momentâneos, ou seja, a cada atualização o mesmo será apresentará dados diferentes e o quarto gráfico será de barrras.

Em seguida inserimos um título a cada uma. 

g1.scatter(dados.Valor, dados.Area)

g1.set_title('Valor X Area')

g2.hist(dados.Valor)

g2.set_title('Histograma')

dados_g3 = dados.Valor.sample(100)

dados_g3.index = range(dados_g3.shape[0])

g3.plot(dados_g3)

g3.set_title('Amostra (Valor)')

grupo = dados.groupby('Tipo')['Valor']

label = grupo.mean().index

valores = grupo.mean().values

g4.bar(label, valores )

g4.set_title('HistValor Médio por Tipo')

No caso do scatter os dados levados em conta foram Valor x Area

No segundo gráfico o histrograma levou em consideração apenas os dados correspondentes a Valor.

Antes de avançamos, vamos entender melhor um pouco do 3º gráfico. O 3º preciso receber um indice para que as informações contidas não corressem o risco de serem malinterpretadas.

dados_g3 = dados.Valor.sample(100)

dados_g3.index = range(dados_g3.shape[0])

g3.plot(dados_g3)

g3.set_title('Amostra (Valor)')

Como podemos ver, sem o ajuste do indice seria assim a apresentação dos dados.

A variável g4 criou um gráfico cruzando as informações tipo (apartamento, casa, casa de condominio, casa de vila e quitinete) com os valores.

grupo = dados.groupby('Tipo')['Valor']

label = grupo.mean().index

valores = grupo.mean().values

g4.bar(label, valores )

g4.set_title('HistValor Médio por Tipo')

E por fim este o resultado final desta linha de comando esta logo abaixo.

g1.scatter(dados.Valor, dados.Area)

g1.set_title('Valor X Area')

g2.hist(dados.Valor)

g2.set_title('Histograma')

dados_g3 = dados.Valor.sample(100)

dados_g3.index = range(dados_g3.shape[0])

g3.plot(dados_g3)

g3.set_title('Amostra (Valor)')

grupo = dados.groupby('Tipo')['Valor']

label = grupo.mean().index

valores = grupo.mean().values

g4.bar(label, valores )

g4.set_title('HistValor Médio por Tipo')

Os arquivos deste post encontram-se neste link https://github.com/faovieira/mais_sobre_gr-ficos.