Combinar subconsultas e GROUP BY no SQL permite realizar agregações e análises complexas de dados de maneira eficiente. Neste artigo, discutiremos desde os fundamentos das subconsultas e GROUP BY, até como integrá-los, com exemplos avançados e dicas de otimização de desempenho. Este conteúdo é valioso para quem deseja aprimorar suas habilidades em SQL.
Fundamentos das Subconsultas
Uma subconsulta é uma consulta SQL aninhada dentro de outra consulta SQL. Isso permite criar consultas complexas em etapas, mantendo e utilizando temporariamente os resultados. Subconsultas são utilizadas da seguinte forma:
Sintaxe das Subconsultas
A sintaxe básica de uma subconsulta é a seguinte:
SELECT column_name FROM table_name WHERE column_name = (SELECT column_name FROM table_name WHERE condition);Tipos de Subconsultas
Existem três tipos principais de subconsultas: subconsultas escalares, subconsultas de linha e subconsultas de tabela.
Subconsultas Escalares
Retornam um único valor. Exemplo:
SELECT name FROM employees WHERE id = (SELECT manager_id FROM departments WHERE name = 'Sales');Subconsultas de Linha
Retornam uma linha de dados. Exemplo:
SELECT * FROM employees WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);Subconsultas de Tabela
Retornam múltiplas linhas e colunas. Exemplo:
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');Especifique os seguintes itens:
Fundamentos do GROUP BY
O GROUP BY é utilizado no SQL para agrupar dados e realizar agregações em cada grupo. Isso permite agregar e analisar dados que compartilham o mesmo atributo.
Sintaxe do GROUP BY
A sintaxe básica do GROUP BY é a seguinte:
SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;Funções de Agregação Comuns
As funções de agregação mais comuns usadas com o GROUP BY incluem:
COUNT
Conta o número de registros. Exemplo:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;SUM
Calcula a soma de uma coluna. Exemplo:
SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;AVG
Calcula a média de uma coluna. Exemplo:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;MAX
Obtém o valor máximo de uma coluna. Exemplo:
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;MIN
Obtém o valor mínimo de uma coluna. Exemplo:
SELECT department_id, MIN(salary) FROM employees GROUP BY department_id;Uso da cláusula HAVING
A cláusula HAVING é usada para especificar condições após a agregação com GROUP BY. Enquanto a cláusula WHERE aplica condições a linhas, a cláusula HAVING aplica condições a grupos. Exemplo:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000;Exemplos de Integração de Subconsultas e GROUP BY
Combinar subconsultas e GROUP BY permite realizar agregações e filtragens complexas. Abaixo estão alguns exemplos específicos.
Agregação de Dados Filtrados por Subconsulta
No exemplo abaixo, dados de uma tabela de funcionários que atendem a certas condições são extraídos por uma subconsulta e, em seguida, agregados por GROUP BY.
SELECT department_id, AVG(salary) AS avg_salary
FROM (SELECT * FROM employees WHERE hire_date >= '2020-01-01') AS recent_hires
GROUP BY department_id;Essa consulta primeiro extrai funcionários contratados a partir de 1º de janeiro de 2020 e, em seguida, calcula a média salarial por departamento.
Usando Resultados de Agregação como Subconsulta
Em seguida, um exemplo em que o resultado agregado por GROUP BY é processado por uma consulta externa.
SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id) AS department_avg
WHERE avg_salary > 60000;Esta consulta calcula primeiro a média salarial por departamento e depois filtra os departamentos com média salarial superior a 60.000.
Agregação Aninhada Usando Subconsultas
Em um exemplo mais complexo, subconsultas aninhadas são usadas para realizar uma agregação detalhada.
SELECT department_id, MAX(avg_salary)
FROM (SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id) AS department_avg
GROUP BY department_id;Esta consulta calcula primeiro a média salarial por departamento e, em seguida, extrai o departamento com a maior média salarial.
Exemplos Avançados de Agregação Usando Subconsultas
Usar subconsultas permite realizar agregações e análises mais avançadas. Aqui estão alguns exemplos de aplicação.
Criando Rankings com Subconsultas
O exemplo abaixo cria um ranking de salários dentro de cada departamento com base nos salários dos funcionários.
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;Essa consulta ordena os funcionários por salário em ordem decrescente dentro de cada departamento e usa a função RANK() para calcular a classificação salarial.
Calculando Percentuais com Resultados Agregados
O próximo exemplo calcula a porcentagem de funcionários em cada departamento em relação ao total de funcionários.
SELECT department_id,
COUNT(*) AS dept_employee_count,
(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees)) AS employee_percentage
FROM employees
GROUP BY department_id;Essa consulta usa uma subconsulta para obter o total de funcionários e, em seguida, calcula a porcentagem de funcionários em cada departamento.
Consulta Combinada com Múltiplas Agregações
O exemplo abaixo obtém simultaneamente a média salarial, o maior salário e o menor salário em cada departamento.
SELECT department_id,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;Essa consulta combina GROUP BY com múltiplas funções de agregação para obter estatísticas detalhadas sobre salários por departamento.
Agregação Condicional
Um exemplo de agregação que se aplica a dados que atendem a uma condição específica.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2022-01-01'
GROUP BY department_id;Essa consulta calcula a média salarial de funcionários contratados a partir de 1º de janeiro de 2022 em cada departamento.
Pontos de Otimização de Desempenho
Para usar subconsultas e GROUP BY de maneira eficaz, a otimização de desempenho é crucial. Seguir os pontos abaixo pode melhorar a velocidade de execução das consultas.
Uso de Índices
Índices são estruturas de dados criadas para colunas específicas em um banco de dados, usadas para melhorar a velocidade de busca e agregação. Colunas frequentemente usadas em subconsultas e GROUP BY devem ter índices configurados.
CREATE INDEX idx_employees_hire_date ON employees(hire_date);
CREATE INDEX idx_employees_department_id ON employees(department_id);Verificação do Plano de Execução com EXPLAIN
Use EXPLAIN para verificar o plano de execução de uma consulta SQL. Isso ajuda a entender como a consulta é executada e a identificar gargalos.
EXPLAIN SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;Armazenamento de Resultados de Subconsultas em Tabelas Temporárias
Armazenar resultados de subconsultas em tabelas temporárias pode evitar a reexecução da consulta e melhorar o desempenho geral.
CREATE TEMPORARY TABLE temp_recent_hires AS
SELECT * FROM employees WHERE hire_date >= '2020-01-01';
SELECT department_id, AVG(salary) FROM temp_recent_hires GROUP BY department_id;Uso de Tipos de Dados Apropriados
A escolha do tipo de dado pode afetar significativamente o desempenho das consultas. Usar tipos de dados apropriados pode reduzir o uso de memória e melhorar a velocidade de execução das consultas.
Evitar Subconsultas Redundantes
Se uma consulta contiver várias subconsultas que retornam os mesmos resultados, consolidá-las pode melhorar a eficiência da consulta.
SELECT department_id, AVG(salary)
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department_id;Erros Comuns e Como Corrigi-los
A seguir, discutiremos os erros mais comuns ao usar subconsultas e GROUP BY, e como corrigi-los.
Erro: Subconsulta Retornando Múltiplas Linhas
Este erro ocorre quando uma subconsulta retorna várias linhas em uma posição onde é esperado um único valor. Como solução, adicione LIMIT 1 à subconsulta ou use uma função de agregação adequada.
-- Exemplo de subconsulta retornando múltiplas linhas
SELECT name
FROM employees
WHERE id = (SELECT id FROM employees WHERE department_id = 1);
-- Solução: Uso de LIMIT 1
SELECT name
FROM employees
WHERE id = (SELECT id FROM employees WHERE department_id = 1 LIMIT 1);Erro: Coluna não Inclusa no GROUP BY na Cláusula SELECT
Esse erro ocorre quando uma coluna não incluída na cláusula GROUP BY é selecionada na cláusula SELECT. Para corrigir, todas as colunas na cláusula SELECT devem estar presentes na cláusula GROUP BY.
-- Exemplo que gera erro
SELECT department_id, name, AVG(salary)
FROM employees
GROUP BY department_id;
-- Solução: Adicionar name ao GROUP BY
SELECT department_id, name, AVG(salary)
FROM employees
GROUP BY department_id, name;Erro: Desempenho Baixo da Subconsulta
Quando a execução de uma subconsulta é lenta, pode ser necessário adicionar índices ou otimizar a consulta. Também pode ser útil dividir a consulta usando tabelas temporárias.
-- Exemplo de subconsulta com desempenho baixo
SELECT department_id, (SELECT AVG(salary) FROM employees WHERE department_id = d.id)
FROM departments d;
-- Solução: Adicionar índice
CREATE INDEX idx_employees_department_id ON employees(department_id);
-- Ou usar tabela temporária
CREATE TEMPORARY TABLE temp_avg_salaries AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
SELECT d.id, t.avg_salary
FROM departments d
JOIN temp_avg_salaries t ON d.id = t.department_id;Erro: Falta de Memória
Consultas e agregações em larga escala podem consumir muita memória, resultando em erros de falta de memória. Como solução, divida a consulta em partes ou ajuste as configurações do banco de dados.
-- Exemplo de divisão de consulta para evitar falta de memória
CREATE TEMPORARY TABLE temp_large_query AS
SELECT * FROM large_table WHERE condition;
SELECT * FROM temp_large_query WHERE another_condition;Conclusão
Combinar subconsultas e GROUP BY de forma eficaz pode aumentar significativamente a expressividade e flexibilidade de consultas SQL. Ao dominar o uso básico e avançado dessas técnicas, além de otimizar o desempenho e lidar com erros comuns, você poderá realizar agregações e análises complexas de dados de maneira mais eficiente. Aproveite essas técnicas para aprimorar suas operações em bancos de dados.