Pode criar um select no sql com varias estruturas de lógica complexas?
- User
Pode criar um select no sql com varias estruturas de lógica complexas?
- 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
SELECTque 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:
Clientes: Armazena dados dos clientes.id(PK),nome,email,cidade,estado,data_cadastro
Produtos: Catálogo de produtos.id(PK),nome_produto,categoria,preco
Pedidos: Registra os pedidos feitos pelos clientes.id(PK),cliente_id(FK paraClientes),data_pedido,status
ItensPedido: Detalha quais produtos estão em cada pedido.id(PK),pedido_id(FK paraPedidos),produto_id(FK paraProdutos),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):
- ID e nome do cliente.
- Segmento do Cliente: Classificar o cliente como 'Ouro', 'Prata' ou 'Bronze' com base no total gasto.
- Valor Total Gasto: A soma de todos os seus pedidos.
- Data do Último Pedido.
- Ranking de Gastos: A posição do cliente em um ranking de quem mais gastou no estado de 'SP'.
- Contagem de Categorias Distintas: Quantas categorias de produtos diferentes o cliente já comprou.
- 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
-
Expressões de Tabela Comuns (CTEs -
WITHclause)- 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 (...)eWITH AgregadoPorCliente AS (...). A primeira CTE prepara os dados por pedido, e a segunda agrega esses dados por cliente.
-
Estrutura
CASEAninhada- O que é? Permite criar uma lógica condicional (if-then-else) diretamente dentro do
SELECTpara 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.
- O que é? Permite criar uma lógica condicional (if-then-else) diretamente dentro do
-
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.
- 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
-
Subconsulta Correlacionada no
SELECT- O que é? É uma subconsulta (um
SELECTdentro 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.
- O que é? É uma subconsulta (um
-
EXISTScom Subconsulta noWHERE- 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.
- O que é?
-
Múltiplos
JOINs e Agregação (GROUP BY)- Embora mais comuns, a combinação de múltiplos
JOINs (PedidoscomItensPedido) e a subsequente agregação (GROUP BY) para calcular totais é a base para a maioria das análises complexas.
- Embora mais comuns, a combinação de múltiplos
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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.