Como Agregar Dados Complexos Usando Tabelas JOIN e GROUP BY no SQL

Nas operações de banco de dados usando SQL, é comum recuperar e agregar dados relacionados de várias tabelas. Especialmente para análises complexas em grandes conjuntos de dados, aproveitar as tabelas join e GROUP BY é essencial. Este artigo fornece uma explicação detalhada dos métodos de agregação de dados usando esses recursos do SQL, desde conceitos básicos até a criação de consultas práticas e técnicas de otimização.

Índice

Noções Básicas sobre Tabelas Join

As tabelas join são recursos do SQL usados para combinar várias tabelas para recuperar dados relacionados. Abaixo estão os principais tipos de joins e como usá-los.

INNER JOIN

O INNER JOIN retorna apenas os dados comuns a ambas as tabelas. É usado principalmente para obter registros correspondentes.

SELECT A.column1, B.column2
FROM TableA A
INNER JOIN TableB B ON A.id = B.id;

LEFT JOIN

O LEFT JOIN retorna todos os dados da tabela da esquerda e os dados correspondentes da tabela da direita. Se não houver dados correspondentes na direita, NULL é retornado.

SELECT A.column1, B.column2
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id;

RIGHT JOIN

O RIGHT JOIN retorna todos os dados da tabela da direita e os dados correspondentes da tabela da esquerda. Se não houver dados correspondentes na esquerda, NULL é retornado.

SELECT A.column1, B.column2
FROM TableA A
RIGHT JOIN TableB B ON A.id = B.id;

FULL JOIN

O FULL JOIN retorna todos os dados de ambas as tabelas e preenche com NULLs as correspondências ausentes. É usado quando você deseja incluir todos os dados de ambas as tabelas.

SELECT A.column1, B.column2<br>FROM TableA A<br>FULL JOIN TableB B ON A.id = B.id;

Noções Básicas sobre GROUP BY

O GROUP BY é um recurso do SQL usado para agrupar dados com base em colunas especificadas e retornar resultados para cada grupo usando funções de agregação. É usado principalmente quando você deseja agregar dados por categorias específicas.

Sintaxe Básica do GROUP BY

A sintaxe básica do GROUP BY é a seguinte.

SELECT column, AGGREGATE_FUNCTION(column)
FROM Table
GROUP BY column;

Aqui, AGGREGATE_FUNCTION pode ser SUM, AVG, COUNT, MAX, MIN, etc.

Exemplos

Por exemplo, uma consulta para encontrar o salário médio de cada departamento seria a seguinte.

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Esta consulta calcula o salário médio de cada departamento a partir da tabela de empregados.

Agrupando por Várias Colunas

Os dados também podem ser agrupados por várias colunas.

SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;

Esta consulta conta o número de funcionários por departamento e título de trabalho.

Exemplos de Agregação de Dados Complexos

Combinando tabelas join e GROUP BY, os dados de várias tabelas podem ser agregados para realizar análises mais complexas. Aqui estão exemplos concretos de agregação de dados relacionados a funcionários e seus departamentos.

Agregando Dados de Múltiplas Tabelas

Por exemplo, para agregar o número de funcionários e o salário médio de cada departamento, você pode usar a seguinte consulta.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Esta consulta junta as tabelas de departamentos e funcionários usando INNER JOIN e agrega o número de funcionários e o salário médio de cada departamento.

Usando Múltiplas Funções de Agregação

Além disso, várias informações estatísticas podem ser obtidas combinando várias funções de agregação.

SELECT d.department_name, 
       COUNT(e.employee_id) AS num_employees, 
       AVG(e.salary) AS avg_salary, 
       MAX(e.salary) AS max_salary, 
       MIN(e.salary) AS min_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Esta consulta agrega o número de funcionários, salário médio, salário máximo e salário mínimo de cada departamento de uma vez.

Agregação Condicional

Também é possível filtrar dados com condições antes de realizar a agregação.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
WHERE e.salary > 50000
GROUP BY d.department_name;

Esta consulta agrega o número de funcionários e o salário médio de cada departamento, considerando apenas funcionários com salário superior a 50.000.

Como você pode ver, combinando tabelas join e GROUP BY permite recuperar dados de várias tabelas e analisar dados de várias perspectivas.

Criando Consultas Práticas

Aqui, introduzimos como criar consultas complexas com base em cenários de negócios reais. Por exemplo, considere a agregação das vendas mensais de cada loja a partir de um banco de dados de vendas.

Cenário: Agregando Vendas Mensais para Cada Loja

Neste cenário, as tabelas incluem os seguintes dados:

stores table: contém informações sobre cada loja
orders table: contém informações dos pedidos

Primeiro, crie uma consulta para agregar as vendas mensais de cada loja.

SELECT s.store_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_name, month
ORDER BY s.store_name, month;

Os pontos-chave desta consulta são os seguintes:

Use INNER JOIN para juntar as tabelas stores e orders
Use a função DATE_FORMAT para formatar order_date por mês
Use a função SUM para calcular as vendas totais de cada mês
Agrupe os dados por nome da loja e mês usando GROUP BY
Ordene os resultados por nome da loja e mês usando ORDER BY

Cenário: Agregando Vendas Mensais por Categoria de Produto

Em seguida, considere um cenário de agregação de vendas mensais por categoria de produto. As tabelas incluem:

products table: contém informações sobre cada produto
categories table: contém informações de categoria para cada produto
order_items table: contém detalhes dos pedidos

A consulta é a seguinte:

SELECT c.category_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(oi.quantity * p.price) AS total_sales
FROM categories c
INNER JOIN products p ON c.category_id = p.category_id
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
GROUP BY c.category_name, month
ORDER BY c.category_name, month;

Os pontos-chave desta consulta são os seguintes:

Use múltiplos INNER JOIN para juntar as tabelas categories, products, orders e order items
Calcule as vendas de cada produto usando quantity * price e agregue com a função SUM
Agrupe os dados por nome da categoria e mês usando GROUP BY
Ordene os resultados por nome da categoria e mês usando ORDER BY

Como mostrado nesses exemplos, criar consultas com base em cenários de negócios reais requer entender as relações entre as tabelas e combinar joins e funções de agregação apropriadas.

Técnicas de Otimização

Para melhorar o desempenho de consultas complexas, é importante usar técnicas de otimização adequadas. Aqui estão alguns métodos para melhorar a velocidade de execução das consultas.

Usando Índices

O uso adequado de índices pode melhorar significativamente a velocidade de pesquisa do banco de dados. Crie índices nas colunas usadas em joins e condições de pesquisa.

CREATE INDEX idx_department_id ON employees(department_id);
CREATE INDEX idx_order_date ON orders(order_date);

Neste exemplo, são criados índices nas colunas department_id e order_date. Isso acelera os joins e as condições de pesquisa usando esses índices.

Utilizando Subconsultas

O uso de subconsultas para pré-processar dados pode aumentar a eficiência da consulta principal. Isso é especialmente eficaz ao lidar com grandes quantidades de dados.

SELECT department_name, num_employees, avg_salary
FROM (
  SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
  FROM departments d
  INNER JOIN employees e ON d.department_id = e.department_id
  GROUP BY d.department_name
) sub;

Neste exemplo, a subconsulta calcula o número de funcionários e o salário médio de cada departamento, e a consulta principal utiliza esses resultados.

Otimização da Ordem dos Joins

Otimizar a ordem dos joins das tabelas pode melhorar o desempenho da consulta. Começar a junção pela tabela menor primeiro é eficaz.

EXPLAIN SELECT s.store_name, DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_name, month
ORDER BY s.store_name, month;

Use EXPLAIN para verificar o plano de consulta e garantir que o mecanismo do banco de dados usa a ordem de joins ideal.

Usando Funções de Agregação Apropriadas

O uso adequado de funções de agregação pode melhorar o desempenho da consulta. Por exemplo, use apenas as funções de agregação mínimas necessárias para evitar agregações redundantes.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Neste exemplo, apenas as funções de agregação COUNT e AVG são usadas para obter as informações necessárias.

Usando Views

O uso de views pode simplificar consultas complexas e criar consultas reutilizáveis. Definir uma view melhora a legibilidade da consulta.

CREATE VIEW department_summary AS
SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Neste exemplo, uma view é criada para calcular o número de funcionários e o salário médio de cada departamento. Usando a view, você pode facilmente se referir aos resultados agregados posteriormente.

Conclusão

A agregação de dados usando tabelas join e GROUP BY é muito útil para análises de dados complexas. Compreendendo os tipos adequados de joins e o uso do GROUP BY, e utilizando técnicas de otimização de consultas, a agregação de dados eficiente e eficaz torna-se possível. Use essas técnicas para maximizar o desempenho do banco de dados.