DETONADO de SQL (Pt. II/II)

O que é SQL e por que ele é vendável? As empresas estão investindo grandes quantias para coletar, armazenar dados, torná-los relevantes e usá-los.

Conteúdio extraído do livro: Getting Started with SQL

No Detonado de SQL Pt. I abordamos:

  • Por que aprender SQL?
  • O que é DATABASE?
  • Filtro de registros WHERE
  • Instruções AND, OR, IN e HAVING
  • Agrupação com GROUP BY e ORDER BY

A partir daqui veremos neste Detonado Pt.2:

  • Instruções CASE
  • Agrupamento de instruções
  • Associação de tabelas INNER JOIN, LEFT JOIN
  • Associação de várias tabelas

Instruções CASE

Esse comando nos permite substituir o valor de uma coluna por outro valor, de acordo com uma ou mais condições.

Uma instrução CASE permite mapear uma ou mais condições para um valor correspondente a cada condição. Ela deve ser iniciada com a palavra CASE e finalizada com END.

Palavras chave

WHEN [condição] THEN [valor], em que a [condição] e o [valor] que fornecemos. Após especificar os pares condição-valor, podemos estabelecer um valor geral a ser usado como padrão se nenhuma das condições for atendida, que deve ser definido em ELSE.

Poderíamos classificar a velocidade do vento (wind_speed) em categorias relacionadas ao seu rigor (wind_severity), em que velocidades com valor maior que 40 seriam altas (‘HIGH), de 30 a 40 moderadas (‘MODERATE’) e velocidades menores seriam consideradas baixas (‘LOW’):

SELECT report_code, year, month, day, wind_speed,
CASE
WHEN wind_speed >= 40 THEN 'HIGH'
WHEN wind_speed >= 30 AND wind_speed < 40 THEN 'MODERATE'
ELSE 'LOW'
END as wind_severity
FROM station_data;
Categorizando o rigor do vento como alto, moderado e baixo.

Na verdade, podemos omitir a condição AND wind_speed < 40. Entenda o motivo: a máquina lê uma instrução CASE de cima para baixo, e a primeira condição verdadeira que ela encontra é a que é usada (ela não avalia as condições subsequentes). Logo, se tivermos um registro com velocidade do vento igual a 43, poderemos ter certeza de que ele será avaliado na categoria ‘HIGH’. Embora ele seja maior que 30, não será considerado ‘MODERATE’ porque não chegará a esse ponto

SELECT report_code, year, month, day, wind_speed,
CASE
WHEN wind_speed >= 40 THEN 'HIGH'
WHEN wind_speed >= 30 THEN 'MODERATE'
ELSE 'LOW'
END as wind_severity
FROM station_data

Agrupando instruções

Quando criar instruções CASE e agrupá-las, poderá provocar algumas transformações muito poderosas. Converter valores de acordo com uma ou mais condições antes de agregá-los nos abre ainda mais possibilidades de fatiar dados de maneiras interessantes.

SELECT year,
CASE
WHEN wind_speed >= 40 THEN 'HIGH'
WHEN wind_speed >= 30 THEN 'MODERATE'
ELSE 'LOW'
END as wind_severity,
COUNT(*) AS record_count
FROM station_data
GROUP BY 1, 2

A instrução CASE “Zero/Null”

CASE “zero/null” permite aplicar diferentes “filtros” a valores agregados distintos na mesma consulta SELECT. Isso não é possível na instrução WHERE porque ela aplica um filtro a todos os itens. No entanto, é possível usar CASE para criar uma condição de filtro diferente para cada valor agregado.

Se quisesse agregar a precipitação em duas somas, tornado_precipitation e non_tornado_precipitation, e agrupar por ano e mês. A lógica depende principalmente de dois campos: precipitation e tornado, mas como exatamente podemos codificar isso?

Não pode fazê-lo com uma instrução WHERE, a menos que execute duas consultas separadas:

Precipitação com tornado:

SELECT year, month,
SUM(precipitation) AS tornado_precipitation
FROM station_data
WHERE tornado = 1
GROUP BY year, month;

Precipitação sem tornado:

SELECT year, month,
SUM(precipitation) AS non_tornado_precipitation
FROM station_data
WHERE tornado = 0
GROUP BY year, month;

Entretano, é possível fazê-lo em uma única consulta com o uso de uma instrução CASE, movendo as condições de ocorrência de tornado da instrução WHERE para uma instrução CASE e retornar o valor 0 se a condição produzir falso como resultado.

SELECT year, month,SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) AS
tornado_precipitation,
SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) AS
non_tornado_precipitation
FROM station_data
GROUP BY year, month
Obtendo a precipitação com e sem tornados por ano e mês

A instrução CASE pode realizar um grande volume de trabalho, principalmente em tarefas de agregação complexas. Beneficiando-nos de uma condição que retorne o valor 0 quando não for atendida, ignoramos esse valor e o excluímos da soma (porque a soma de 0 não altera nada).

Também poderia fazer isso com a operação MIN ou MAX e usar nulo em vez de 0 para assegurar que valores de determinadas condições nunca fossem considerados. É possível calcular a precipitação máxima quando tornados estavam ou não presentes:

MAX(CASE WHEN tornado = 0 THEN precipitation ELSE NULL END)
AS max_non_tornado_precipitation,
MAX(CASE WHEN tornado = 1 THEN precipitation ELSE NULL END)
AS max_tornado_precipitation
FROM station_dataGROUP BY year
Precipitações máximas com e sem tornados por ano

A instrução CASE, o que inclui funções e as instruções AND, OR e NOT.

Calcularemos as temperaturas médias por mês quando chuva/granizo estavam ou não presentes após o ano 2000:

SELECT month,AVG(CASE WHEN rain OR hail THEN temperature ELSE null END)
AS avg_precipitation_temp,
AVG(CASE WHEN NOT (rain OR hail) THEN temperature ELSE null END)
AS avg_non_precipitation_temp
FROM station_data
WHERE year > 2000
GROUP BY month

O truque da instrução CASE zero/null é uma ótima aplicação da instrução CASE. Ele oferece muitas possibilidades para a execução de várias agregações com diferentes critérios e, portanto, é útil conhecê-lo.

1. JOIN

Associando tabelas

A associação é a funcionalidade definidora do SQL que o distingue de outras tecnologias de dados.

Por exemplo, considere a tabela CUSTOMER_ORDER, que tem um campo CUSTOMER_ID:

A tabela CUSTOMER_ORDER tem um campo CUSTOMER_ID.

O campo CUSTOMER_ID fornece uma CHAVE para buscas na tabela CUSTOMER. Diante disso, espera-se que a tabela CUSTOMER tenha um campo CUSTOMER_ID:

A tabela CUSTOMER tem um campo de chave CUSTOMER_ID que pode ser usado no retorno de informações clientes.

Podemos recuperar as informações de cliente de um pedido nessa tabela, de maneira semelhante ao que ocorre com PROCV no Excel.

CUSTOMER é pai de CUSTOMER_ORDER, porque CUSTOMER_ORDER depende dela para obter informações de clientes.

O outro aspecto que devemos considerar em um relacionamento é quantos registros da tabela-filha podem estar associados a um único registro da tabela-pai. Examine as tabelas CUSTOMER e CUSTOMER_ORDER e verá que esse é um relacionamento um-para-muitos, em que um único registro de cliente pode estar associado a vários pedidos.

Telacionamento um-para-muitos entre CUSTOMER e CUSTOMER_ORDER

O tipo um-para-muitos é o relacionamento mais comum porque acomoda a maioria das necessidades empresariais, como quando um único cliente possui vários pedidos. Os dados empresariais de um banco de dados bem projetado costumam se enquadrar em um padrão um-para-muitos.

2. INNER JOIN

Agora que conhecemos os relacionamentos entre as tabelas, poderíamos achar útil unir duas delas; dessa forma, veríamos informações de CUSTOMER e CUSTOMER_ORDER lado a lado.

O operador INNER JOIN nos permite mesclar duas tabelas. Porém, se você pretende mesclar tabelas, precisamos definir um atributo comum entre elas para que seus registros se alinhem. Precisamos definir um ou mais campos que elas tenham em comum e fazer a associação a partir deles.

SELECT order_id,
customer.customer_id,
ORDER_DATE,
SHIP_DATE,
NAME,
STREET_ADDRESS,
CITY,
STATE,
ZIP,
PRODUCT_ID,
ORDER_QTY
FROM customer_1 INNER JOIN customer_order_2
ON customer_1.customer_id = customer_order_2.customer_id

A primeira coisa a notar é que conseguimos consultar campos tanto da tabela CUSTOMER quanto de CUSTOMER_ORDER. Mesclamos temporariamente em uma única tabela.

INNER JOIN

Vejamos em detalhes como isso ocorreu. Primeiro, selecionamos os campos que queríamos nas tabelas CUSTOMER e CUSTOMER_ORDER:

SELECT customer_1.customer_id,NAME,
STREET_ADDRESS,
CITY,
STATE,
ZIP,
ORDER_DATE,
SHIP_DATE,
ORDER_ID,
PRODUCT_ID,
ORDER_QTY
FROM customer_1 INNER JOIN customer_order_2
ON customer_1.customer_id = customer_order_2.customer_id

Nesse caso, queremos exibir os endereços de clientes referentes a cada pedido. Observe também que como CUSTOMER_ID aparece nas duas tabelas, precisamos selecionar explicitamente uma das ocorrências (não importando qual). No exemplo, selecionamos CUSTOMER_ID de CUSTOMER usando uma sintaxe explícita, CUSTOMER.CUSTOMER_ID.

Para concluir, temos a parte importante que mescla temporariamente as duas tabelas em uma. Foi na instrução FROM que executamos INNER JOIN. Especificamos que estamos extraindo informações de CUSTOMER e mesclando-as a CUSTOMER_ORDER, e que o atributo comum se encontra nos campos CUSTOMER_ID (que precisam ser iguais para se alinhar):

SELECT customer_1.customer_id,NAME,
STREET_ADDRESS,
CITY,
STATE,
ZIP,
ORDER_DATE,
SHIP_DATE,
ORDER_ID,
PRODUCT_ID,
ORDER_QTY
FROM customer_1 INNER JOIN customer_order_2
ON customer_1.customer_id = customer_order_2.customer_id;

Graças ao operador INNER JOIN, essa consulta fornece uma visualização que inclui os detalhes do cliente em cada pedido.

CUSTOMER mesclada a CUSTOMER_ORDER

Armazenamos dados eficientemente por intermédio da normalização, mas podemos usar associações para mesclar tabelas a partir de campos comuns para criar visualizações mais descritivas dos dados.

Se examinarmos a tabela CUSTOMER, verá que há cinco clientes. Nossa consulta INNER JOIN só capturou três. “Rex Tooling Inc” e “Prairie Construction” não aparecem nos resultados da consulta. Não há pedidos para Rex Tooling Inc e Prairie Construction e, portanto, INNER JOIN as excluiu da consulta. Ela só exibirá registros que existam nas duas tabelas (Fi

Com o uso de INNER JOIN, qualquer registro que não tenha um valor comum nas duas tabelas será excluído. Se quisermos incluir todos os registros da tabela CUSTOMER, podemos fazê-lo com LEFT JOIN.

Visualização da mesclagem de CUSTOMER e CUSTOMER_ORDER (observe que os dois clientes foram omitidos porque não têm pedidos associados).

3. LEFT JOIN

Rex Tooling Inc e Prairie Construction, foram excluídos da operação INNER JOIN baseada em CUSTOMER_ID porque não tinham pedidos associados. Porém, digamos que quiséssemos incluí-los mesmo assim, associando as tabelas e ver, por exemplo, todos os clientes, mesmo se eles não tiverem pedidos.

A tabela especificada no lado “esquerdo” do operador LEFT JOIN (CUSTOMER_1) terá todos os seus registros incluídos, mesmo se não tiverem registros-filho na tabela da “direita” (CUSTOMER_ORDER_2).

LEFT JOIN incluirá todos os registros da tabela da esquerda, mesmo se não tiverem um registro associado na tabela da direita

Ao executar essa operação, teremos resultados semelhantes aos obtidos na consulta INNER JOIN anterior, mas com dois registros adicionais para os clientes que não têm pedidos.

No caso desses dois clientes, observe que todos os campos que vêm de CUSTOMER_ORDER_2 são nulos porque não há pedidos a serem incluídos na associação. Em vez de omiti-los, como fez INNER JOIN, LEFT JOIN apenas os tornou nulos:

CUSTOMER em uma associação à esquerda com CUSTOMER_ORDER (os campos nulos de CUSTOMER_ORDER indicam que não foram encontrados pedidos desses dois clientes).
CUSTOMER em uma associação à esquerda com CUSTOMER_ORDER (observe que “Rex Tooling” e “Prairie Construction” foram associadas a NULL, já que não há pedidos aos quais serem associadas).

Também é comum o uso de LEFT JOIN na busca de registros filhos “órfãos”, por exemplo, pedidos que não tenham clientes ou clientes que não tenham pedidos).

Pode usar uma instrução WHERE para procurar valores nulos que sejam resultado de LEFT JOIN. Modificando nosso exemplo anterior, podemos encontrar clientes que não tenham pedidos filtrando campos da tabela da direita que sejam nulos:

SELECT
customer_1.customer_id,
name AS customer_name
FROM customer_1 LEFT JOIN customer_order_2ON customer_1.customer_id = customer_order_2.customer_idWHERE order_id IS NULL

Outros tipos de operador JOIN

O operador RIGHT JOIN executa uma associação externa à direita que é quase idêntica à associação externa à esquerda. Ele inverte a direção da associação e inclui todos os registros da tabela da direita. No entanto, RIGHT JOIN é raramente usado e deve ser evitado.

A convenção faz associações externas à esquerda com LEFT JOIN e posicionar a tabela “que contém todos os registros” no lado esquerdo do operador de associação.

Também há um operador de associação externa completa chamado OUTER JOIN que inclui todos os registros das duas tabelas. Ele executa uma LEFT JOIN e uma RIGHT JOIN simultaneamente e pode ter registros nulos nas duas tabelas. Pode ser útil na busca de registros órfãos nas duas direções simultaneamente em uma única consulta, mas também é pouco usado.

Associando várias tabelas

Examinamos o relacionamento entre CUSTOMER e CUSTOMER_ORDER. No entanto, há outra tabela que podemos incluir que tornará nossos pedidos mais significativos: a tabela PRODUCT. A tabela CUSTOMER_ORDER tem uma coluna PRODUCT_ID, que corresponde a um produto da tabela PRODUCT.

É possível fornecer informações não só de CUSTOMER para a tabela CUSTOMER_ORDER, mas também de PRODUCT usando PRODUCT_ID.

Associando várias tabelas

Podemos usar esses dois relacionamentos para executar uma consulta que exiba pedidos com informações de clientes e de produtos simultaneamente.

SELECTORDER_ID,
CUSTOMER.CUSTOMER_ID,
NAME AS CUSTOMER_NAME,
STREET_ADDRESS,
CITY,
STATE,
ZIP,
ORDER_DATE,
PRODUCT_ID,
DESCRIPTION,
ORDER_QTY
FROM customer_1 INNER JOIN customer_order_2ON customer_order_2.customer_id = customer_1.customer_idINNER JOIN product ON customer_order_2.product_id = product.product_id
Associando campos de ORDER, CUSTOMER, e PRODUCT

Já que mesclamos as tabelas, podemos usar campos das três para criar expressões. Se quisermos calcular a receita obtida com cada pedido, podemos multiplicar ORDER_QTY e PRICE, mesmo que esses campos existam em tabelas separadas:

SELECTORDER_ID,
CUSTOMER.CUSTOMER_ID,
NAME AS CUSTOMER_NAME,
STREET_ADDRESS,
CITY,
STATE,
ZIP,
ORDER_DATE,
PRODUCT_ID,
DESCRIPTION,
ORDER_QTY,
ORDER_QTY * PRICE AS REVENUE
FROM customerINNER JOIN customer_order_2
ON customer_1.customer_id = customer_order_2.customer_id
INNER JOIN product
ON customer_order.product_id = product.product_id

Agora temos a receita fornecida por cada pedido, ainda que as colunas necessárias venham de duas tabelas separadas.

Agrupando JOINs

Daremos continuidade usando o mesmo exemplo. Temos os pedidos com suas receitas, graças à associação que construímos. E se quiséssemos calcular a receita total por cliente?

Como queremos fazer a agregação por cliente, o agrupamento precisa se basear em CUSTOMER_ID e CUSTOMER_NAME. Em seguida, precisamos somar a expressão ORDER_QTY * PRICE para obter a receita total:

SELECT
customer_1.customer_id,
name AS customer_name,
SUM (order_qty * price) AS total_revenue
FROM customer_order_2INNER JOIN customer_1
ON customer_1.customer_id = customer_order.customer_id
INNER JOIN product
ON customer_order_2.product_id = product.product_id
GROUP BY 1,2
Calculando Total_REVENUE pela associação e agregação de 3 tabeals

Se quisermos ver todos os clientes, inclusive os que não têm pedidos, é só usar LEFT JOIN em vez de INNER JOIN:

SELECTcustomer.customer_id,
NAME AS CUSTOMER_NAME,
sum(ORDER_QTY * PRICE) as TOTAL_REVENUE
FROM customer_1LEFT JOIN customer_order_2
ON customer_1.customer_id = customer_order_2.customer_id
LEFT JOIN product
ON customer_order_2.product_id = product.product_id
GROUP BY 1,2
Usando LEFT JOIN para incluir todos os clientes e sua receita total.

Agora a Rex Tooling Inc e a Prairie Construction estão presentes, mesmo não tendo pedidos. Também podemos fazer os valores adotarem como padrão 0 em vez de nulo se não houver vendas. Isso pode ser feito com a função coalesce() para transformar zeros em valores nulos:

SELECTcustomer_1.customer_id,NAME AS CUSTOMER_NAME,coalesce(sum(ORDER_QTY * PRICE), 0) AS TOTAL_REVENUEFROM CUSTOMER_1LEFT JOIN CUSTOMER_ORDER_2
ON CUSTOMER_1.CUSTOMER_ID = CUSTOMER_ORDER_2.CUSTOMER_ID
LEFT JOIN PRODUCT
ON CUSTOMER_ORDER_2.PRODUCT_ID = PRODUCT.PRODUCT_ID
GROUP BY 1,2

Resumo As associações são o tópico mais desafiador do SQL, mas também o mais compensador. Elas nos permitem pegar dados distribuídos em várias tabelas e reuni-los como algo mais significativo e descritivo. Podemos pegar duas ou mais tabelas e associá-las em uma tabela maior com mais contexto. No próximo capítulo aprenderemos mais sobre as associações e como elas são naturalmente definidas por relacionamentos entre as tabelas.

Obrigado.

Composing a repository of books (i bought), authors (i follow) & blogs (direct ones) for my own understanding.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store