{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "SQL-modulo_7_aula.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KJqp9AANOCtf"
      },
      "source": [
        "<img src=\"https://raw.githubusercontent.com/andre-marcos-perez/ebac-course-utils/main/media/logo/newebac_logo_black_half.png\" alt=\"ebac-logo\">\n",
        "\n",
        "---\n",
        "\n",
        "# **Módulo** | SQL Avançado\n",
        "Caderno de **Aula**<br> \n",
        "Professor [Mariane Neiva](www.linkedin.com/in/mariane-neiva)\n",
        "\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d9jDtUbDOE1-"
      },
      "source": [
        "# **Tópicos**\n",
        "\n",
        "<ol type=\"1\">\n",
        "  <li>Subqueries;</li>\n",
        "  <li>Agregações por particionamento</li>\n",
        "  <li>Visões;</li>\n",
        "</ol>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SmoHgt-lwkpD"
      },
      "source": [
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GABI6OW8OfQ2"
      },
      "source": [
        "# **Aulas**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NF7Pdc6hJ8oF"
      },
      "source": [
        "Nessa aula, usaremos a seguinte tabela:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QkH5jQruJG-L"
      },
      "source": [
        "```sql\n",
        "CREATE TABLE transacoes (\n",
        "  id_cliente INT, \n",
        "  id_transacao INT,\n",
        "  data_compra DATE,\n",
        "  valor FLOAT, \n",
        "  id_loja varchar(25)\n",
        ");\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MLdOjQcDKCXc"
      },
      "source": [
        "Também temos os seguintes valores inseridos na tabela"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I34tqzZrKOma"
      },
      "source": [
        "```sql\n",
        "INSERT INTO transacoes VALUES (1,768805383,'2021-06-10',50.74,'magalu');\n",
        "INSERT INTO transacoes VALUES (2,768805399,'2021-06-13',30.90,'giraffas');\n",
        "INSERT INTO transacoes VALUES (3,818770008,'2021-06-05',110.00,'postoshell');\n",
        "INSERT INTO transacoes VALUES (1,76856563,'2021-07-10',2000.90,'magalu');\n",
        "INSERT INTO transacoes VALUES (1,767573759,'2021-06-20',15.70,'subway');\n",
        "INSERT INTO transacoes VALUES (3,818575758,'2021-06-25',2.99,'seveneleven');\n",
        "INSERT INTO transacoes VALUES (4,764545534,'2021-07-11',50.74,'extra');\n",
        "INSERT INTO transacoes VALUES (5,76766789,'2021-08-02',10.00,'subway');\n",
        "INSERT INTO transacoes VALUES (3,8154567758,'2021-08-15',1100.00,'shopee');\n",
        "```"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rRBLidUaK7NJ"
      },
      "source": [
        "Como resultado da função SELECT, temos a seguinte tabela:\n",
        "\n",
        "| id_cliente | id_transacao | data_compra |valor | id_loja |\n",
        "|------------|----------|----|-----| ------- |\n",
        "| 1| 768805383    | 2021-06-10    | 50.74 |magalu \n",
        "| 2 | 768805399    | 2021-06-13    |  30.90 | giraffas \n",
        "| 3 | 818770008    |2021-06-05   |  110.00 |  postoshell  \n",
        "|1 |76856563 | 2021-07-10 |2000.90 |magalu\n",
        "|1|767573759|2021-06-20|15.70|subway\n",
        "| 3|818575758|2021-06-25|2.99|seveneleven\n",
        " |4|764545534|2021-07-10|50.74|extra\n",
        "|5|76766789|2021-08-92|10.00|subway\n",
        "|3|8154567758|2021-08-15|1100.00|shopee "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rM6OChBV1RQ2"
      },
      "source": [
        "Além disso, também utilizaremos a tabela:\n",
        "\n",
        "```sql\n",
        "CREATE TABLE cliente (\n",
        "  id_cliente INT,  \n",
        "  nome varchar(25),\n",
        "  data_compra DATE,\n",
        "  valor_compra float, \n",
        "  loja_cadastro varchar(25)\n",
        ");\n",
        "```\n",
        "\n",
        "Teremos os seguintes dados na tabela:\n",
        "\n",
        "```sql\n",
        "INSERT INTO cliente VALUES (5,'jose', '2020-07-01',500.43,'magalu');\n",
        "INSERT INTO cliente VALUES (1,'maria','2019-03-02',150.70,'subway');\n",
        "INSERT INTO cliente VALUES (2,'valentina','2020-01-09',210.99,'postoshell');\n",
        "INSERT INTO cliente VALUES (4,'joana','2019-05-11',1300.50,'magalu');\n",
        "INSERT INTO cliente VALUES (6,'fernando','2020-03-02',86.55,'seveneleven'); \n",
        "```\n",
        "\n",
        "Como resultado temos:\n",
        "\n",
        "|id_cliente|nome|data_compra|valor_compra|loja_cadastro|\n",
        "|-|-|-|-|-|\n",
        "|5|jose|2020-07-01|500.43|cea|\n",
        "|1|maria|2019-03-02|150.70|riachuelo|\n",
        "|2|valentina|2020-01-09|210.99|zara|\n",
        "|4|joana|2019-05-11|1300.50|pontofrio|\n",
        "|6|fernando|2020-03-02|86.55|pontofrio|"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ok889VghEyKq"
      },
      "source": [
        "#1. Subqueries\n",
        "\n",
        "Podemos resumir o uso de subqueries quando utilizamos um comando dentro do outro. \n",
        "\n",
        "Para utilizar, basta inserir um comando \n",
        "\n",
        "*  dentro de um SELECT, INSERT, UPDATE ou DELETE\n",
        "* junto com um operador = , >, <=, >= e LIKE. \n",
        "*  aliado a um WHERE, HAVING e FROM\n",
        "\n",
        "A query *externa* é chamada de **main query** e a interna é chamada de **subquery**.\n",
        "\n",
        "* a **subquery** deve ser utilizada com  parenteses \n",
        "* a **subquery** em geral é executada primeiro!\n",
        "* é como se a **subquery** fosse um comando condicional associado \n",
        "* **subquery** não pode ser associado com ORDER BY\n",
        "\n",
        "Exemplo teórico:\n",
        "\n",
        "```sql\n",
        "SELECT <nome_coluna>\n",
        "FROM <nome_tabela>\n",
        "WHERE <nome_coluna> <expressao> <operador>  \n",
        "    ( SELECT <coluna>  from <tabela>   WHERE ... );\n",
        "```\n",
        "\n",
        "Exemplo prático:\n",
        "\n",
        "\n",
        "```sql\n",
        "SELECT id_loja, id_cliente, id_transacao from transacoes \n",
        "WHERE id_loja IN\n",
        "(SELECT cliente.loja_cadastro from cliente where cliente.valor_compra > 160 )\n",
        "```\n",
        "\n",
        "> **O que ele executa?**\n",
        "\n",
        "Primeiro a **subquery** é executada.\n",
        "\n",
        "O resultado de:\n",
        "\n",
        "```sql\n",
        "SELECT cliente.loja_cadastro from cliente where cliente.valor_compra > 160\n",
        "```\n",
        "\n",
        "|loja_cadastro|\n",
        "|-|\n",
        "|magalu|\n",
        "|postoshell|\n",
        "|magalu|\n",
        "\n",
        "Ou seja, vamos considerar apenas essas lojas na seleção da **main query**.\n",
        "\n",
        "É como se traduzissemos para:\n",
        "```sql\n",
        "SELECT id_loja, id_cliente, id_transacao from transacoes \n",
        "WHERE id_loja IN\n",
        "('magalu','postoshell')\n",
        "```\n",
        "\n",
        "Resultado: \n",
        "\n",
        "|id_loja|id_cliente|id_transacao\n",
        "|-|-|-|\n",
        "|magalu|1|768805383|\n",
        "|postoshell|3|818770008|\n",
        "|magalu|1|76856563|\n",
        "\n",
        "Use a criatividade para gerar novas queries com subqueries!\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "O1GZbhfC7vE3"
      },
      "source": [
        "# 2. Agregações por particionamento\n",
        "\n",
        "Esse é um aspecto do AWS Athena utilizado para organizar e gerar as queries de maneira mais eficiente no framework.\n",
        "\n",
        "É uma organização hierarquica onde cada *pasta* contem *subpastas* com o rótulo e valores.\n",
        "\n",
        "> Por que utilizar? \n",
        "Para economizar dados carregados no AWS Athena, aumentando a performance reduzindo custos.\n",
        "\n",
        "> Como fazer?\n",
        "* No S3, crie uma pasta no AWS com o nome do seu dataset. \n",
        "* Vamos supor que queremos separar as lojas na nossa partição. \n",
        "* Para isso criamos subpastas:\n",
        "** transacoes_partition/id_loja=magalu\n",
        "** transacoes_partition/id_loja=giraffas\n",
        "** transacoes_partition/id_loja=postoshell\n",
        "** transacoes_partition/id_loja=subway\n",
        "** transacoes_partition/id_loja=seveneleven\n",
        "** transacoes_partition/id_loja=extra\n",
        "** transacoes_partition/id_loja=shopee\n",
        "\n",
        "Dentro de cada uma das subpastas, colocamos apenas aquelas informações referentes a id_loja dedicada.\n",
        "\n",
        "A geração da partição é indicada na hora da **CRIAÇÃO** da tabela com o comando **PARTITIONED by** id_loja (no exemplo).\n",
        "\n",
        "Depois da criação, é necessário carregas as partições como o comando: \n",
        "\n",
        "```sql\n",
        "CREATE EXTERNAL TABLE transacoes_part(\n",
        "  id_cliente BIGINT, \n",
        "  id_transacoes BIGINT, \n",
        "  valor DOUBLE) \n",
        "  PARTITIONED BY (id_loja string)\n",
        "ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'\n",
        "WITH SERDEPROPERTIES (\n",
        "  'serialization.format' = ',',\n",
        "  'field.delim' = ','\n",
        ") \n",
        "LOCATION 's3://transacoes-partition/'\n",
        "```\n",
        "\n",
        "\n",
        "```sql \n",
        "MSCK REPAIR TABLE transacoes_part\n",
        "```\n",
        "\n",
        "Você pode verificar pela contagem de linhas na tabela completa:\n",
        "\n",
        "```sql \n",
        "select count(*) from transacoes_part\n",
        "```\n",
        "\n",
        "A partir disso, nós podemos seguir com os comandos de SELECT que aprendemos nos modulos do curso.\n",
        "\n",
        "> Mais detalhes na aula prática!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cGW-jtcAAvVO"
      },
      "source": [
        "# 3. Visões\n",
        "\n",
        "Uma visão é uma tabela virtual, não fisica.\n",
        "\n",
        "Isso significa que toda vez a view é referenciada, ela é também criada. \n",
        "\n",
        "> Porque utilizar? \n",
        "Ao invés de fazer um SELECT várias vezes, você pode criar uma visão cujos dados são o resultado desse SELECT, ou quando:\n",
        "* você quer um subconjunto dos seus dados com frequência\n",
        "* você combina múltiplas tabelas\n",
        "* você quer simplificar as chamadas de queries\n",
        "\n",
        "\n",
        "Funções para a visão:\n",
        "\n",
        "\n",
        "|nome| descrição|\n",
        "|-|-|\n",
        "|CREATE VIEW| cria a visão a partir de um select|\n",
        "|DESCRIBE| mostra a lista de colunas da visão e atributos |\n",
        "|DROP VIEW| deleta a visão|\n",
        "|SHOW CREATE VIEW| mostra a query que criou a visao|\n",
        "|SHOW VIEWS|mostra as visões disponíveis em uma base de dados|\n",
        "|SHOW COLUMNS| lista as colunas em uma visão|\n",
        "\n",
        "Mais detalhes [aqui](https://docs.aws.amazon.com/athena/latest/ug/views.html)\n",
        "\n",
        "> Pratique!\n",
        "\n"
      ]
    }
  ]
}