{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "/home/danielavancini/Projects/eng-analytics/chapters/05/02\n" ] } ], "source": [ "# HIDDEN\n", "# Altera caminho para manter padrão com Jupyter Book\n", "%cd chapters/05/02/" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 5.2 Juntando dados com JOIN e UNION\n", "\n", "Consultar informações de diferentes tabelas é uma das tarefas mais comuns na análise de dados. Por exemplo, podemos querer o total de vendas (tabela pedidos) por produtos com preço acima de 30 (tabela produto). Ou simplesmente saber o nome dos clientes que fizeram um pedido, etc. Como é comum em SQL, não há uma única forma de juntar dados (chamado de JOIN) entre tabelas mas algumas maneiras que podemos utilizar a depender do que queremos responder. A forma mais simples é fazendo algumas pequenas alterações na consulta que usamos anteriormente, adicionando um prefixo para explicar ao banco de dados de qual tabela queremos retornar cada campo e uma lógica para dizer como juntá-las:\n", "\n", "```sql\n", "SELECT \n", "FROM tabela_1, tabela2\n", "WHERE tabela_1.nome_do_campo1 = tabela_2.nome_do_campo1\n", "```\n", "Vamos utilizar essa nova estrutura para consultar o nome dos clientes que fizeram pedidos na nossa loja. Nessa hora é importante utilizar o diagrama ER de nosso banco de dados para verificar que a coluna CustomerId da tabela Order é uma chave estrangeira para a coluna Id da tabela Customer e portanto é uma boa candidata à ser utilizada como chave em nosso JOIN:" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "# HIDDEN\n", "# Vamos utilizar a extensão ipython-sql junto com o pacote SQLAlchemy para rodar consultas SQL diretamente\n", "from sqlalchemy import create_engine\n", "path_data = '../../assets/data/'\n", "%load_ext sql\n", "%config SqlMagic.displaycon=False\n", "%config SqlMagic.feedback=False\n", "\n", "# Agora dizemos para a extensão do ipython-sql onde estão os dados\n", "%sql sqlite:///../../../assets/data/northwind.sqlite" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IdCompanyNameOrderDate
10248Vins et alcools Chevalier2012-07-04
10249Toms Spezialitäten2012-07-05
10250Hanari Carnes2012-07-08
10251Victuailles en stock2012-07-08
10252Suprêmes délices2012-07-09
10253Hanari Carnes2012-07-10
10254Chop-suey Chinese2012-07-11
10255Richter Supermarkt2012-07-12
10256Wellington Importadora2012-07-15
10257HILARION-Abastos2012-07-16
" ], "text/plain": [ "[(10248, 'Vins et alcools Chevalier', '2012-07-04'),\n", " (10249, 'Toms Spezialitäten', '2012-07-05'),\n", " (10250, 'Hanari Carnes', '2012-07-08'),\n", " (10251, 'Victuailles en stock', '2012-07-08'),\n", " (10252, 'Suprêmes délices', '2012-07-09'),\n", " (10253, 'Hanari Carnes', '2012-07-10'),\n", " (10254, 'Chop-suey Chinese', '2012-07-11'),\n", " (10255, 'Richter Supermarkt', '2012-07-12'),\n", " (10256, 'Wellington Importadora', '2012-07-15'),\n", " (10257, 'HILARION-Abastos', '2012-07-16')]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT 'Order'.Id,'Customer'.CompanyName,'Order'.OrderDate\n", "FROM 'Order', 'Customer' \n", "WHERE 'Order'.CustomerId='Customer'.Id\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "Em termos um pouco mais técnicos, a consulta que fizemos acima é chamada de INNER JOIN. Essa operação funciona como uma espécie de filtro, trazendo todos os registros das tabelas juntadas que estejam presentes em ambas as tabelas. No entanto, se quisermos outros comportamentos em nossa consulta precisamos utilizar expressamente o comando JOIN e entender um pouco melhor como ele funciona. Para isso recorremos à teoria dos conjuntos e ao diagrama de Venn:\n", "* **(INNER) JOIN:** Retorna os campos onde há correspondência em ambas as tabelas\n", "* **LEFT (OUTER) JOIN**: Retorna todos os campos da tabela da esquerda, e os campos com correspondência na tabela da direita\n", "* **RIGHT (OUTER) JOIN**: Retorna todos os campos da tabela da direita, e os campos com correspondência na tabela da esquerda\n", "* **FULL (OUTER) JOIN**: Retorna todos os campos da tabela da esquerda e da direita\n", "\n", "![](../../../assets/img/db_venn.png)\n", "\n", "Além do INNER JOIN que já mostramos anteriormente, a operação de LEFT JOIN é a mais utilizada em consultas analíticas porque ela adiciona campos de uma outra tabela sem afetar a tabela inicial, semelhante à uma função PROCV() em planilhas eletrônicas. Para realizar um JOIN qualquer precisamos descrever qual tipo de JOIN queremos fazer e uma ou mais campos que devem ser correspondidos entre as tabelas:\n", "\n", "```sql\n", "SELECT \n", "FROM tabela_1\n", "(INNER | LEFT| RIGHT |FULL) JOIN tabela_2 ON tabela_1.campo1 = tabela_2.campo2\n", "(AND tabela_1.campo2 = tabela_2.campo2, ...)\n", "```\n", "\n", "Ao realizar um JOIN nós estamos fazendo uma operação vertical sobre as tabelas, isto é, estamos unindo diferentes colunas entre si a partir de uma relação de correspondência. Em alguns casos, nosso objetivo pode ser de unir duas consultas com as mesmas colunas, aumentando o número de linhas. Esse tipo de operação é feito através do comando UNION:\n", "\n", "```sql\n", "SELECT campo1, campo2\n", "FROM tabela_1\n", "UNION\n", "SELECT campo1, campo2\n", "FROM tabela_2\n", "```\n", "\n", "Se quiséssemos, por exemplo, criar uma consulta com todos os clientes e fornecedores do Brasil em uma mesma tabela, poderíamos usar uma UNION:" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///../../../assets/data/northwind.sqlite\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
IdCompanyName
10Refrescos Americanas LTDA
COMMIComércio Mineiro
FAMIAFamilia Arquibaldo
GOURLGourmet Lanchonetes
HANARHanari Carnes
QUEDEQue Delícia
QUEENQueen Cozinha
RICARRicardo Adocicados
TRADHTradição Hipermercados
WELLIWellington Importadora
" ], "text/plain": [ "[(10, 'Refrescos Americanas LTDA'),\n", " ('COMMI', 'Comércio Mineiro'),\n", " ('FAMIA', 'Familia Arquibaldo'),\n", " ('GOURL', 'Gourmet Lanchonetes'),\n", " ('HANAR', 'Hanari Carnes'),\n", " ('QUEDE', 'Que Delícia'),\n", " ('QUEEN', 'Queen Cozinha'),\n", " ('RICAR', 'Ricardo Adocicados'),\n", " ('TRADH', 'Tradição Hipermercados'),\n", " ('WELLI', 'Wellington Importadora')]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT Id, CompanyName\n", "FROM 'Customer' \n", "WHERE Country = 'Brazil'\n", "UNION\n", "SELECT Id, CompanyName\n", "FROM 'Supplier' \n", "WHERE Country = 'Brazil'" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.9.5 ('venv': venv)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.5" }, "vscode": { "interpreter": { "hash": "1f018ec1ba574647514081dcb490c63f68e342ee5f5450c4501934abf117bfd5" } } }, "nbformat": 4, "nbformat_minor": 2 }