Nessa aula a gente vai fazer mágica de verdade com queries! Vamos criar alguns exemplos usando vários CTE pra mostrar como eles podem ser integrados entre si pra montar consultas complexas e de vários passos. Esses exemplos são úteis na vida real, principalmente em tarefas analíticas mais pesadas.
Exemplo 1: Análise do desempenho dos estudantes
Imagina que a gente tem um banco de dados da faculdade com três tabelas:
Tabela students:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name TEXT NOT NULL
);
Tabela grades:
CREATE TABLE grades (
grade_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id),
course_id INT NOT NULL,
grade NUMERIC(3, 1) NOT NULL
);
Tabela courses:
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name TEXT NOT NULL
);
Tarefa: pegar a lista de estudantes que têm média acima de 85, mostrando a média deles e os nomes dos cursos que eles fazem.
Query:
WITH high_achievers AS (
-- Encontrando estudantes com média alta
SELECT
student_id,
AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
HAVING AVG(grade) > 85
),
student_courses AS (
-- Pegando os cursos em que cada estudante está matriculado
SELECT
s.student_id,
c.course_name
FROM grades g
JOIN courses c ON g.course_id = c.course_id
JOIN students s ON g.student_id = s.student_id
)
-- Juntando os resultados
SELECT
s.student_name,
ha.avg_grade,
sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;
Explicação:
- No primeiro CTE (
high_achievers) a gente calcula a média de cada estudante e pega só quem tirou acima de 85. - No segundo CTE (
student_courses) a gente liga os estudantes com os cursos deles. - No query principal a gente junta os dados dos dois CTE pra mostrar a lista dos estudantes, a média deles e os cursos que eles fazem.
Exemplo 2: Relatório de vendas para e-commerce
Imagina que você trabalha num e-commerce e tem as seguintes tabelas:
Tabela orders (pedidos):
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
Tabela customers (clientes):
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name TEXT NOT NULL
);
Tabela order_items (itens dos pedidos):
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_id INT NOT NULL,
quantity INT NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
Tarefa: montar um relatório mostrando pra cada cliente:
- Quantidade total de pedidos.
- Valor total de todos os pedidos do último mês.
- Lista de todos os produtos únicos que ele comprou.
Query:
WITH recent_orders AS (
-- Pegando os pedidos do último mês
SELECT
order_id,
customer_id,
total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
-- Contando o total de pedidos e o valor pra cada cliente
SELECT
ro.customer_id,
COUNT(ro.order_id) AS total_orders,
SUM(ro.total_amount) AS total_spent
FROM recent_orders ro
GROUP BY ro.customer_id
),
customer_products AS (
-- Pegando os produtos únicos que cada cliente comprou
SELECT DISTINCT
ro.customer_id,
oi.product_id
FROM recent_orders ro
JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Juntando os resultados
SELECT
c.customer_name,
cs.total_orders,
cs.total_spent,
ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;
Explicação:
- No primeiro CTE (
recent_orders) a gente pega os pedidos do último mês. - No segundo CTE (
customer_summary) a gente calcula o total de pedidos e o valor total pra cada cliente. - No terceiro CTE (
customer_products) a gente pega os produtos únicos que cada cliente comprou. - No query final a gente junta tudo e usa
ARRAY_AGG()pra montar a lista dos produtos únicos.
Exemplo 3: Análise de hierarquia de funcionários
A gente tem uma tabela de funcionários:
Tabela employees:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name TEXT NOT NULL,
manager_id INT NULL
);
Tarefa: montar a hierarquia dos funcionários, começando pelo CEO. Mostrar o nível de cada funcionário na hierarquia.
Query:
WITH RECURSIVE employee_hierarchy AS (
-- Começando pelos funcionários sem gerente (CEO)
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Adicionando todos os subordinados do nível atual
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Mostrando a hierarquia
SELECT
employee_id,
employee_name,
manager_id,
level
FROM employee_hierarchy
ORDER BY level, employee_id;
- No query recursivo a gente começa pelo CEO (funcionários sem gerente).
- Em cada passo, adiciona os subordinados do nível atual, aumentando o
levelem um. - No query principal a gente pega toda a hierarquia, ordenando pelo nível e pelo id do funcionário.
Dicas úteis e erros comuns
- CTE demais: não usa CTE onde dá pra resolver com subquery. Às vezes CTE pode deixar a query mais lenta por causa da materialização dos dados.
- Nomes dos CTE: dá nomes claros e curtos pros seus CTE pra deixar a query mais legível.
- Ordem de execução: lembra que os CTE são executados na ordem em que aparecem.
- Agrupamento de dados: usa
GROUP BYsó quando realmente precisa, pra evitar operações desnecessárias.
Todos esses exemplos mostram como os CTE podem ser usados pra dividir tarefas complexas em etapas, melhorando a leitura e manutenção das queries. Agora você tá com as ferramentas pra resolver tarefas analíticas pesadas usando PostgreSQL!
GO TO FULL VERSION