Consultas Avançadas no MySQL: JOINs, Subconsultas e Funções de Janela

No desenvolvimento diário, consultas simples em uma única tabela geralmente não atendem às necessidades de negócios. Através das técnicas de consulta avançada do MySQL, é possível obter dados flexivelmente de várias tabelas, realizar filtros complexos e análises de dados. Este artigo focará em três tipos de consultas avançadas: JOINs (Consultas de Junção), subconsultas e funções de janela, fornecendo exemplos práticos para ajudar você a entender e aplicar essas tecnologias melhor.

1. JOINs (Consultas de Junção)

Os JOINs permitem combinar duas ou mais tabelas em uma única consulta através de colunas relacionadas, obtendo assim dados de múltiplas tabelas em uma só operação. Os tipos comuns de JOINs no MySQL incluem:

1.1 Junção Interna (INNER JOIN)

• Princípio: Retorna apenas os registros que têm correspondência entre as duas tabelas na condição de junção.
• Exemplo: ``` SELECT o.id_pedido, o.data_pedido, c.nome_cliente FROM pedidos AS o INNER JOIN clientes AS c ON o.id_cliente = c.id_cliente;

Esta consulta retorna todos os pedidos junto com o nome do cliente associado, mas apenas para aqueles onde há uma correspondência entre o pedido e o cliente.

#### **1.2 Junção à Esquerda (LEFT JOIN)**

- **Princípio**: Retorna todas as linhas da tabela à esquerda e as correspondências da tabela à direita. Se não houver correspondência, os campos da tabela à direita serão nulos.
- **Exemplo**:```
SELECT c.nome_cliente, o.id_pedido
FROM clientes AS c
LEFT JOIN pedidos AS o ON c.id_cliente = o.id_cliente;

Essa consulta lista todos os clientes, mesmo se eles não fizeram nenhum pedido, com campos de pedido sendo nulos para esses clientes.

1.3 Junção à Direita (RIGHT JOIN)

• Princípio: Semelhante à junção à esquerda, mas retorna todas as linhas da tabela à direita e as correspondências da tabela à esquerda. Também pode ser menos utilizada em comparação com a junção à esquerda ajustada.
• Exemplo:``` SELECT o.id_pedido, c.nome_cliente FROM pedidos AS o RIGHT JOIN clientes AS c ON o.id_cliente = c.id_cliente;

Este tipo de junção é raramente utilizado, e muitas vezes pode ser substituído pela reordenação de uma junção à esquerda.

#### **1.4 Junção Auto (Self JOIN)**

- **Princípio**: Realiza uma junção entre registros da mesma tabela, usada normalmente para encontrar hierarquias ou relações entre registros.
- **Exemplo**:```
SELECT e1.nome_funcionario AS Gerente, e2.nome_funcionario AS Subordinado
FROM funcionarios AS e1
INNER JOIN funcionarios AS e2 ON e1.id_funcionario = e2.id_gerente;

Essa consulta exibe a relação gerenciamento-subordinados entre funcionários.

2. Subconsultas

As subconsultas são consultas incorporadas dentro de outras consultas SQL, geralmente usadas para fornecer valores ou resultados como condições ou fontes de dados. As subconsultas podem ser classificadas por sua posição dentro da consulta principal:

2.1 Subconsulta Escalar

• Características: Retorna um único valor, que pode ser usado diretamente na cláusula WHERE ou SELECT.
• Exemplo:``` SELECT id_pedido, data_pedido FROM pedidos WHERE id_cliente = (SELECT id_cliente FROM clientes WHERE nome_cliente = 'João');

Neste exemplo, o nome do cliente "João" é extraído e usado para filtrar os pedidos.

#### **2.2 Subconsulta de Lista**

- **Características**: Retorna uma lista de valores, que pode ser usada em condições como IN ou NOT IN.
- **Exemplo**:```
SELECT id_pedido, data_pedido
FROM pedidos
WHERE id_cliente IN (SELECT id_cliente FROM clientes WHERE cidade = 'Rio de Janeiro');

Este exemplo filtra os pedidos dos clientes que residem em Rio de Janeiro.

2.3 Subconsulta de Tabela

• Características: Retorna um conjunto de resultados, que pode ser usado na cláusula FROM como uma tabela temporária.
• Exemplo:``` SELECT id_cliente, total_pedidos FROM ( SELECT id_cliente, COUNT(*) AS total_pedidos FROM pedidos GROUP BY id_cliente ) AS t WHERE total_pedidos > 3;

Aqui, a subconsulta primeiro conta o número de pedidos por cliente, e depois filtra os clientes com mais de 3 pedidos.

#### **2.4 Subconsulta Correlacionada**

- **Características**: A subconsulta depende dos dados da consulta externa, sendo executada uma vez para cada linha da consulta externa.
- **Exemplo**:```
SELECT id_funcionario, nome_funcionario,
       (SELECT COUNT(*) FROM vendas v WHERE v.vendedor_id = f.id_funcionario) AS num_vendas
FROM funcionarios AS f;

Esta consulta retorna o número de vendas realizadas por cada funcionário.

3. Funções de Janela

A partir da versão 8.0 do MySQL, foram introduzidas as funções de janela (Window Functions), permitindo realizar operações de agrupamento dinâmico, classificação e agregação sem a necessidade de usar subconsultas.

3.1 Funções de Janela Comuns

• ROW_NUMBER(): Atribui um número sequencial único a cada linha do resultado.

SELECT id_pedido, data_pedido,
       ROW_NUMBER() OVER (ORDER BY data_pedido) AS num_linha
FROM pedidos;

Esta consulta atribui um número sequencial a cada pedido, ordenado por data.

• RANK() e DENSE_RANK(): Usadas para classificar os resultados, com RANK pulando números de ranqueamento quando há valores iguais e DENSE_RANK não.

SELECT id_cliente, total_gasto,
       RANK() OVER (ORDER BY total_gasto DESC) AS posicao
FROM (
    SELECT id_cliente, SUM(valor) AS total_gasto
    FROM vendas
    GROUP BY id_cliente
) AS gastos;

• Funções de Agregação como SUM(), AVG(), MAX(), MIN(): Podem ser usadas como funções de janela para calcular valores cumulativos ou médios em cada grupo.

SELECT id_pedido, data_pedido, valor,
       SUM(valor) OVER (ORDER BY data_pedido ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_cumulado
FROM vendas;

Esta consulta calcula o total acumulado de vendas até a data atual.

3.2 Cenários de Uso

• Classificação e Classificação: Para classificar vendas, pontuações ou outros indicadores.
• Somatório Cumulativo: Para gerar valores cumulativos dinâmicos, como somatório de vendas ao longo do tempo.
• Estatísticas de Partição: Para realizar estatísticas detalhadas em partições de dados sem usar GROUP BY.

4. Caso Prático: Aplicação Conjunta

Suponha que você precise gerar um relatório de vendas, contendo o total de vendas de cada vendedor em seu respectivo departamento e seu ranking de vendas dentro desse departamento. Isso pode ser feito combinando subconsultas e funções de janela:

WITH VendasPorDepartamento AS (
  SELECT vendedor_id, departamento, SUM(valor) AS total_vendas
  FROM vendas
  GROUP BY vendedor_id, departamento
)
SELECT vendedor_id, departamento, total_vendas,
       RANK() OVER (PARTITION BY departamento ORDER BY total_vendas DESC) AS ranking
FROM VendasPorDepartamento;

Neste caso, o CTE (Expressão Comum) VendasPorDepartamento primeiro soma as vendas por vendedor e departamento. Em seguida, a função de janela RANK() classifica os vendedores dentro de cada departamento com base em suas vendas totais.

5. Conclusão

• JOINs tornam as consultas envolvendo múltiplas tabelas simples e eficientes, suportando diversos cenários de negócio.
• Subconsultas oferecem flexibilidade para filtragem e processamento de dados individuais ou conjuntos de dados.
• Funções de Janela proporcionam soluções eficientes para operações de agrupamento dinâmico e classificação, eliminando a necessidade de subconsultas.

Ao dominar estas três técnicas de consulta avançada, você poderá aumentar significativamente a complexidade e a flexibilidade das suas consultas no MySQL, faiclitando o suporte a cenários de negócios e análise de dados complexos. Encorajamos a prática constante e otimização dessas habilidades, aproveitando plenamente a potência de processamento de dados do MySQL!