Skip to content
The Chat Archive
gemini-2.5-pro· Google LMArena Human Preference 140K

Pode criar um select no sql com varias estruturas de lógica complexas?

  1. User

    Pode criar um select no sql com varias estruturas de lógica complexas?

  2. Assistant

    Com certeza! Criar uma consulta SQL com lógica complexa é um ótimo exercício para demonstrar o poder da linguagem.

    Vou criar um cenário de banco de dados de e-commerce e, em seguida, uma única consulta SELECT que utiliza várias estruturas lógicas para gerar um relatório analítico de clientes.

    O Cenário: Banco de Dados de Vendas

    Imagine que temos as seguintes tabelas:

    1. Clientes: Armazena dados dos clientes.
      • id (PK), nome, email, cidade, estado, data_cadastro
    2. Produtos: Catálogo de produtos.
      • id (PK), nome_produto, categoria, preco
    3. Pedidos: Registra os pedidos feitos pelos clientes.
      • id (PK), cliente_id (FK para Clientes), data_pedido, status
    4. ItensPedido: Detalha quais produtos estão em cada pedido.
      • id (PK), pedido_id (FK para Pedidos), produto_id (FK para Produtos), quantidade, preco_unitario

    O Objetivo da Consulta

    Queremos gerar um relatório para o time de marketing com as seguintes informações sobre os clientes do estado de 'SP' que estão ativos (fizeram pelo menos um pedido nos últimos 12 meses):

    1. ID e nome do cliente.
    2. Segmento do Cliente: Classificar o cliente como 'Ouro', 'Prata' ou 'Bronze' com base no total gasto.
    3. Valor Total Gasto: A soma de todos os seus pedidos.
    4. Data do Último Pedido.
    5. Ranking de Gastos: A posição do cliente em um ranking de quem mais gastou no estado de 'SP'.
    6. Contagem de Categorias Distintas: Quantas categorias de produtos diferentes o cliente já comprou.
    7. Dias Desde o Cadastro: Quantos dias se passaram desde que o cliente se cadastrou.

    A Consulta SQL Complexa

    Aqui está a consulta que combina várias técnicas para atingir nosso objetivo. Abaixo dela, farei um detalhamento de cada parte complexa.

    -- Usamos Expressões de Tabela Comuns (CTEs) para organizar a lógica em etapas.
    WITH
      -- CTE 1: Calcula o valor total de cada pedido e extrai informações úteis.
      InfoPedidos AS (
        SELECT
          p.id AS pedido_id,
          p.cliente_id,
          p.data_pedido,
          SUM(ip.quantidade * ip.preco_unitario) AS valor_total_pedido
        FROM Pedidos AS p
        JOIN ItensPedido AS ip
          ON p.id = ip.pedido_id
        WHERE
          p.status = 'Entregue' -- Considerar apenas pedidos concluídos
        GROUP BY
          p.id,
          p.cliente_id,
          p.data_pedido
      ),
      -- CTE 2: Agrega os dados por cliente para obter métricas totais.
      AgregadoPorCliente AS (
        SELECT
          cliente_id,
          SUM(valor_total_pedido) AS valor_total_gasto,
          COUNT(pedido_id) AS numero_de_pedidos,
          MAX(data_pedido) AS data_ultimo_pedido
        FROM InfoPedidos
        GROUP BY
          cliente_id
      )
    -- Consulta Final: Junta tudo e aplica a lógica de apresentação.
    SELECT
      c.id AS cliente_id,
      c.nome AS nome_cliente,
      apc.valor_total_gasto,
      apc.data_ultimo_pedido,
    
      -- LÓGICA 1: Estrutura CASE aninhada para segmentação de clientes
      CASE
        WHEN apc.valor_total_gasto > 10000
        THEN 'Ouro'
        WHEN apc.valor_total_gasto > 3000 AND apc.numero_de_pedidos >= 5
        THEN 'Prata'
        ELSE 'Bronze'
      END AS segmento_cliente,
    
      -- LÓGICA 2: Função de Janela (Window Function) para criar um ranking
      DENSE_RANK() OVER (ORDER BY apc.valor_total_gasto DESC) AS ranking_gastos_sp,
    
      -- LÓGICA 3: Subconsulta Correlacionada (Scalar Subquery) no SELECT
      (
        SELECT
          COUNT(DISTINCT pr.categoria)
        FROM ItensPedido AS ip_sub
        JOIN Pedidos AS p_sub
          ON ip_sub.pedido_id = p_sub.id
        JOIN Produtos AS pr
          ON ip_sub.produto_id = pr.id
        WHERE
          p_sub.cliente_id = c.id
      ) AS categorias_distintas_compradas,
    
      -- LÓGICA 4: Manipulação de Datas
      DATEDIFF(day, c.data_cadastro, GETDATE()) AS dias_desde_cadastro -- Sintaxe SQL Server. Em PostgreSQL seria: (CURRENT_DATE - c.data_cadastro)
    
    FROM Clientes AS c
    JOIN AgregadoPorCliente AS apc
      ON c.id = apc.cliente_id
    
    -- LÓGICA 5: Condições complexas no WHERE, incluindo subconsulta com EXISTS
    WHERE
      c.estado = 'SP'
      AND apc.valor_total_gasto > 0
      AND EXISTS ( -- Garante que o cliente tenha feito um pedido nos últimos 12 meses (cliente ativo)
        SELECT
          1
        FROM Pedidos p_exist
        WHERE
          p_exist.cliente_id = c.id
          AND p_exist.data_pedido >= DATEADD(month, -12, GETDATE()) -- SQL Server. PostgreSQL: NOW() - INTERVAL '12 month'
      )
    ORDER BY
      ranking_gastos_sp ASC;
    
    

    Detalhamento das Estruturas Lógicas Complexas

    1. Expressões de Tabela Comuns (CTEs - WITH clause)

      • O que é? São "tabelas virtuais" temporárias que existem apenas no escopo da consulta. Elas ajudam a quebrar um problema complexo em partes menores e mais legíveis.
      • Onde está? WITH InfoPedidos AS (...) e WITH AgregadoPorCliente AS (...). A primeira CTE prepara os dados por pedido, e a segunda agrega esses dados por cliente.
    2. Estrutura CASE Aninhada

      • O que é? Permite criar uma lógica condicional (if-then-else) diretamente dentro do SELECT para criar colunas derivadas.
      • Onde está? Na criação da coluna segmento_cliente. Note que a condição para 'Prata' não é apenas o valor, mas também o número de pedidos, tornando-a mais complexa.
    3. Funções de Janela (Window Functions)

      • O que é? Funções que realizam um cálculo sobre um conjunto de linhas de tabela que estão de alguma forma relacionadas à linha atual. Diferente do GROUP BY, elas não colapsam as linhas.
      • Onde está? DENSE_RANK() OVER (ORDER BY apc.valor_total_gasto DESC) AS ranking_gastos_sp. Isso calcula a posição de cada cliente com base no total gasto, sem pular números no ranking caso haja empates.
    4. Subconsulta Correlacionada no SELECT

      • O que é? É uma subconsulta (um SELECT dentro de outro) que depende de um valor da consulta externa para ser executada. Ela é executada uma vez para cada linha do resultado principal.
      • Onde está? Na criação da coluna categorias_distintas_compradas. Para cada cliente (c.id) da consulta principal, a subconsulta é executada para contar as categorias distintas que aquele cliente específico comprou.
    5. EXISTS com Subconsulta no WHERE

      • O que é? EXISTS é um operador que verifica se uma subconsulta retorna alguma linha. É uma forma muito eficiente de verificar a existência de registros relacionados.
      • Onde está? No WHERE, para filtrar apenas os clientes que são "ativos". A consulta só retorna um cliente se a subconsulta encontrar (EXISTS) pelo menos um pedido para aquele cliente nos últimos 12 meses.
    6. Múltiplos JOINs e Agregação (GROUP BY)

      • Embora mais comuns, a combinação de múltiplos JOINs (Pedidos com ItensPedido) e a subsequente agregação (GROUP BY) para calcular totais é a base para a maioria das análises complexas.

    Este exemplo mostra como combinar diferentes ferramentas do SQL para transformar dados brutos em um relatório rico em insights, tudo em uma única e poderosa declaração SELECT.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.