Consultas Avançadas
Aula 2 de 7
SELECT Avançado
DISTINCT ON
-- Último pedido de cada cliente (sem subquery)
SELECT DISTINCT ON (cliente_id)
cliente_id,
pedido_id,
total,
criado_em
FROM pedidos
ORDER BY cliente_id, criado_em DESC;
-- Produto mais caro por categoria
SELECT DISTINCT ON (categoria_id)
categoria_id,
nome,
preco
FROM produtos
ORDER BY categoria_id, preco DESC;
GROUPING SETS, CUBE, ROLLUP
-- GROUPING SETS: agrupa por combinações específicas
SELECT
EXTRACT(YEAR FROM criado_em) AS ano,
EXTRACT(MONTH FROM criado_em) AS mes,
COUNT(*) AS total_pedidos,
SUM(total) AS receita
FROM pedidos
GROUP BY GROUPING SETS (
(ano, mes),
(ano),
(mes),
()
)
ORDER BY ano, mes;
-- ROLLUP: hierarquia decrescente
SELECT
categoria,
subcategoria,
SUM(vendas) AS total
FROM vendas
GROUP BY ROLLUP (categoria, subcategoria)
ORDER BY categoria, subcategoria;
-- CUBE: todas as combinações
SELECT
marca,
cor,
tamanho,
SUM(estoque) AS total
FROM produtos
GROUP BY CUBE (marca, cor, tamanho);
CTEs (WITH)
-- CTE simples
WITH recentes AS (
SELECT id, cliente_id, total, criado_em
FROM pedidos
WHERE criado_em >= NOW() - INTERVAL '30 days'
)
SELECT c.nome, r.total, r.criado_em
FROM clientes c
JOIN recentes r ON r.cliente_id = c.id;
-- Múltiplas CTEs
WITH
vendas_2024 AS (
SELECT cliente_id, SUM(total) AS total
FROM pedidos
WHERE EXTRACT(YEAR FROM criado_em) = 2024
GROUP BY cliente_id
),
top_clientes AS (
SELECT cliente_id, total
FROM vendas_2024
ORDER BY total DESC
LIMIT 10
)
SELECT c.nome, tc.total
FROM clientes c
JOIN top_clientes tc ON tc.cliente_id = c.id;
CTEs Recursivas — Hierarquia
-- Estrutura de categorias (parent_id)
CREATE TABLE categorias_h (
id SERIAL PRIMARY KEY,
nome TEXT NOT NULL,
parent_id INTEGER REFERENCES categorias_h(id)
);
INSERT INTO categorias_h VALUES
(1, 'Eletrônicos', NULL),
(2, 'Computadores', 1),
(3, 'Notebooks', 2),
(4, 'Desktops', 2),
(5, 'Periféricos', 1),
(6, 'Teclados', 5);
-- CTE recursiva para árvore
WITH RECURSIVE cat_tree AS (
-- Âncora: raízes
SELECT id, nome, parent_id, 1 AS nivel, nome::TEXT AS caminho
FROM categorias_h
WHERE parent_id IS NULL
UNION ALL
-- Passo recursivo
SELECT c.id, c.nome, c.parent_id,
ct.nivel + 1,
ct.caminho || ' > ' || c.nome
FROM categorias_h c
JOIN cat_tree ct ON ct.id = c.parent_id
)
SELECT id, nivel, caminho
FROM cat_tree
ORDER BY caminho;
CTE Recursiva — Data Series
-- Gerar série de datas (útil para relatórios)
WITH RECURSIVE datas AS (
SELECT CURRENT_DATE AS data
UNION ALL
SELECT data + 1
FROM datas
WHERE data < CURRENT_DATE + INTERVAL '30 days'
)
SELECT data, EXTRACT(DOW FROM data) AS dia_semana
FROM datas;
-- Exemplo prático: preencher dias sem vendas
WITH RECURSIVE datas AS (
SELECT '2024-01-01'::DATE AS data
UNION ALL
SELECT data + 1
FROM datas
WHERE data < '2024-01-31'
)
SELECT d.data, COALESCE(SUM(v.valor), 0) AS vendas
FROM datas d
LEFT JOIN vendas v ON v.data = d.data
GROUP BY d.data
ORDER BY d.data;
Window Functions
-- ROW_NUMBER: numeração sequencial
SELECT
nome,
preco,
ROW_NUMBER() OVER (ORDER BY preco DESC) AS rank_global,
ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY preco DESC) AS rank_categoria
FROM produtos;
-- RANK e DENSE_RANK
SELECT
nome,
preco,
RANK() OVER (ORDER BY preco DESC) AS rank, -- 1, 2, 2, 4
DENSE_RANK() OVER (ORDER BY preco DESC) AS dense -- 1, 2, 2, 3
FROM produtos;
-- NTILE: dividir em N grupos
SELECT
nome,
total_vendas,
NTILE(4) OVER (ORDER BY total_vendas DESC) AS quartil
FROM vendas_produto;
-- LAG e LEAD: acessar linhas adjacentes
SELECT
data,
vendas,
LAG(vendas, 1) OVER (ORDER BY data) AS dia_anterior,
LEAD(vendas, 1) OVER (ORDER BY data) AS proximo_dia,
vendas - LAG(vendas, 1) OVER (ORDER BY data) AS diferenca
FROM vendas_diarias;
-- FIRST_VALUE e LAST_VALUE
SELECT
nome,
preco,
FIRST_VALUE(nome) OVER (PARTITION BY categoria_id ORDER BY preco DESC) AS mais_caro,
LAST_VALUE(nome) OVER (
PARTITION BY categoria_id
ORDER BY preco DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS mais_barato
FROM produtos;
Frame Clause
-- ROWS: número fixo de linhas
SELECT
data,
receita,
AVG(receita) OVER (
ORDER BY data
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS media_movel_7d
FROM receita_diaria;
-- RANGE: valores dentro de um intervalo
SELECT
data,
receita,
SUM(receita) OVER (
ORDER BY data
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
) AS soma_7d
FROM receita_diaria;
-- GROUPS: grupos de linhas (para dados agrupados)
SELECT
departamento,
mes,
SUM(salario) AS total,
AVG(SUM(salario)) OVER (
ORDER BY mes
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS media_departamentos_vizinhos
FROM folha
GROUP BY departamento, mes;
LATERAL Subqueries
-- LATERAL permite subquery referenciar colunas anteriores no FROM
-- Top 3 produtos por categoria
SELECT c.nome, p.nome, p.preco
FROM categorias c
CROSS JOIN LATERAL (
SELECT nome, preco
FROM produtos
WHERE categoria_id = c.id
ORDER BY preco DESC
LIMIT 3
) p;
-- Último pedido de cada cliente
SELECT c.nome, p.total, p.criado_em
FROM clientes c
LEFT JOIN LATERAL (
SELECT total, criado_em
FROM pedidos
WHERE cliente_id = c.id
ORDER BY criado_em DESC
LIMIT 1
) p ON true;
FILTER Clause
-- FILTER: agregados condicionais sem CASE
SELECT
departamento,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE salario > 10000) AS acima_10k,
COUNT(*) FILTER (WHERE salario BETWEEN 5000 AND 10000) AS medio,
COUNT(*) FILTER (WHERE salario < 5000) AS abaixo_5k,
AVG(salario) FILTER (WHERE ativo = true) AS media_ativos
FROM funcionarios
GROUP BY departamento;
VALUES
-- VALUES como tabela temporária
SELECT * FROM (VALUES
(1, 'João', 1000),
(2, 'Maria', 2000),
(3, 'Pedro', 1500)
) AS t(id, nome, salario);
-- Inserir múltiplas linhas
INSERT INTO clientes (nome, email) VALUES
('Ana', '[email protected]'),
('Bob', '[email protected]'),
('Carol', '[email protected]')
RETURNING id, nome;
Lab: Relatório Analítico de Vendas
-- 02-relatorio-vendas.sql
WITH
-- 1. Vendas mensais com totais parciais
vendas_mensais AS (
SELECT
EXTRACT(YEAR FROM p.criado_em) AS ano,
EXTRACT(MONTH FROM p.criado_em) AS mes,
COUNT(DISTINCT p.id) AS total_pedidos,
SUM(p.total) AS receita_total,
COUNT(DISTINCT p.cliente_id) AS clientes_ativos
FROM pedidos p
WHERE p.status != 'cancelado'
GROUP BY GROUPING SETS ((ano, mes), (ano), ())
),
-- 2. Top 5 produtos por mês
top_produtos AS (
SELECT
EXTRACT(YEAR FROM p.criado_em) AS ano,
EXTRACT(MONTH FROM p.criado_em) AS mes,
pr.nome AS produto,
SUM(ip.quantidade) AS unidades_vendidas,
SUM(ip.subtotal) AS receita_produto,
ROW_NUMBER() OVER (
PARTITION BY EXTRACT(YEAR FROM p.criado_em), EXTRACT(MONTH FROM p.criado_em)
ORDER BY SUM(ip.subtotal) DESC
) AS rank
FROM pedidos p
JOIN itens_pedido ip ON ip.pedido_id = p.id
JOIN produtos pr ON pr.id = ip.produto_id
WHERE p.status != 'cancelado'
GROUP BY ano, mes, pr.nome
)
-- 3. Relatório final
SELECT
vm.ano,
vm.mes,
vm.total_pedidos,
vm.receita_total,
vm.clientes_ativos,
vm.receita_total / NULLIF(vm.clientes_ativos, 0) AS ticket_medio,
tp.produto,
tp.unidades_vendidas,
tp.receita_produto,
tp.rank
FROM vendas_mensais vm
LEFT JOIN top_produtos tp
ON tp.ano = vm.ano
AND tp.mes = vm.mes
AND tp.rank <= 5
WHERE vm.ano IS NOT NULL AND vm.mes IS NOT NULL
ORDER BY vm.ano DESC, vm.mes DESC, tp.rank;
DISTINCT ON elimina subqueries. GROUPING SETS/ROLLUP/CUBE geram subtotais. CTEs recursivas navegam hierarquias. Window functions (ROW_NUMBER, LAG, LEAD) analisam sem perder granularidade. LATERAL permite subqueries dependentes no FROM.