CTEs e consultas recursivas

Para calcular o total de Ana, primeiro somamos as linhas de cada encomenda e depois somamos as suas encomendas.

Uma CTE, common table expression, dá nome a um resultado dentro de uma instrução SQL. O nome permite usar esse passo no seguinte sem criar uma vista permanente. Uma CTE recursiva acrescenta outro mecanismo: produzir linhas a partir das já obtidas.

WITH: dar nome a um passo

WITH Totais AS (
SELECT idEncomenda, SUM(qtd * precoUnitario) AS total
FROM Item GROUP BY idEncomenda
), PorCliente AS (
SELECT e.idCliente, SUM(t.total) AS total
FROM Encomenda AS e JOIN Totais AS t ON t.idEncomenda = e.id
GROUP BY e.idCliente
)
SELECT c.id, c.nome, COALESCE(p.total, 0) / 100.0 AS euros
FROM Cliente AS c LEFT JOIN PorCliente AS p ON p.idCliente = c.id
ORDER BY c.id;

PRAGMA foreign_keys = ON;
CREATE TABLE Cliente (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE Produto (
  id INTEGER PRIMARY KEY,
  nome TEXT NOT NULL,
  precoCentimos INTEGER NOT NULL CHECK (precoCentimos >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);
CREATE TABLE Encomenda (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL,
  idCliente INTEGER NOT NULL REFERENCES Cliente(id)
);
CREATE TABLE Item (
  idEncomenda INTEGER NOT NULL REFERENCES Encomenda(id) ON DELETE CASCADE,
  idProduto INTEGER NOT NULL REFERENCES Produto(id),
  qtd INTEGER NOT NULL CHECK (qtd > 0),
  precoUnitario INTEGER NOT NULL CHECK (precoUnitario >= 0),
  PRIMARY KEY (idEncomenda, idProduto)
);
INSERT INTO Cliente VALUES
  (1, 'Ana', 'ana@example.test'),
  (2, 'Rui', NULL),
  (3, 'Mia', NULL);
INSERT INTO Produto VALUES
  (10, 'Teclado', 4500, 20),
  (11, 'Rato', 2500, 50),
  (12, 'Monitor', 18000, 5);
INSERT INTO Encomenda VALUES
  (100, '2026-01-05', 1),
  (101, '2026-01-06', 1),
  (102, '2026-01-06', 2);
INSERT INTO Item VALUES
  (100, 10, 2, 4500),
  (100, 11, 1, 2500),
  (101, 12, 1, 18000),
  (102, 11, 3, 2500);

Totais tem uma linha por encomenda. PorCliente reúne essas linhas por comprador, por isso a resposta tem Ana com 295 euros, Rui com 75 e Mia com 0. O LEFT JOIN conserva Mia e o COALESCE apresenta o total ausente como zero.

Uma CTE pode referenciar outra definida na mesma instrução. O seu nome deixa de existir no fim dessa instrução.

Não garante materialização nem uma vantagem de desempenho. O otimizador pode integrá-la no plano. A razão inicial para a usar aqui é exprimir claramente o nível de cada agregação.

Comparar contagens de níveis diferentes

Num distrito há municípios; em cada município há freguesias. Queremos municípios com mais de uma freguesia e com tantas freguesias como municípios tem o seu distrito. São duas contagens com grãos diferentes. Se contarmos municípios depois de os juntar às freguesias, um município com duas freguesias passa a ocupar duas linhas.

Usa duas CTEs independentes, uma por nível:

WITH PorMunicipio AS (
  SELECT m.id, m.idDistrito, COUNT(f.id) AS nFreguesias
  FROM Municipio AS m
  LEFT JOIN Freguesia AS f ON f.idMunicipio = m.id
  GROUP BY m.id, m.idDistrito
), PorDistrito AS (
  SELECT idDistrito, COUNT(*) AS nMunicipios
  FROM Municipio GROUP BY idDistrito
)
SELECT m.id, m.nFreguesias
FROM PorMunicipio AS m
JOIN PorDistrito AS d ON d.idDistrito = m.idDistrito
WHERE m.nFreguesias > 1 AND m.nFreguesias = d.nMunicipios
ORDER BY m.id;

Com os dados de votação da lição de subconsultas, PorMunicipio contém (10,1,2), (11,1,1), (20,2,1) e (30,3,1). PorDistrito contém (1,2), (2,1) e (3,1). A resposta é (10,2). O município 11 tem só uma freguesia; os outros dois satisfazem a igualdade, mas não ultrapassam o limite de uma.

O LEFT JOIN e COUNT(f.id) dão zero a um município sem freguesias. O número de municípios do distrito calcula-se diretamente em Municipio, por isso inclui também esses municípios. Cada CTE tem uma linha por chave do seu nível e a junção final não repete as contagens.

Máximos dentro de cada grupo

Para obter as listas vencedoras de cada município, o máximo tem de ser correlacionado pelo município. Comparar com o máximo de todos os municípios conserva apenas o maior total global.

WITH Resultado(municipio, lista, votos) AS (
  VALUES (10,'A',9), (10,'B',8), (11,'A',1), (11,'B',0),
         (20,'A',4), (20,'B',4)
)
SELECT r.municipio, r.lista, r.votos
FROM Resultado AS r
WHERE r.votos = (
  SELECT MAX(outro.votos) FROM Resultado AS outro
  WHERE outro.municipio = r.municipio
)
ORDER BY r.municipio, r.lista;

Devolve A/9 no município 10, A/1 no 11 e A/4 e B/4 no 20. O empate produz duas vencedoras. LIMIT 1 no resultado exterior escolheria só uma linha para todos os municípios, não uma por grupo.

Ao reunir listas partidárias, coligações e listas independentes com UNION ALL, conserva o tipo e a chave de origem. Uma lista partidária de id 7 e uma independente de id 7 são concorrentes diferentes; o par (tipo,id) distingue-as. A designação serve para apresentar, não para fundir concorrentes. Decide no enunciado se os empates se conservam ou se há um desempate definido.

Parte inicial e passo recursivo

Uma CTE recursiva tem uma parte inicial, que cria as primeiras linhas, e um passo recursivo, que usa o resultado anterior para criar mais. As duas partes têm de devolver o mesmo número de colunas com significados compatíveis.

WITH RECURSIVE Numero(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM Numero WHERE n < 5
)
SELECT n FROM Numero ORDER BY n;

A parte inicial produz 1. O passo lê 1 e produz 2, depois 3, 4 e 5. Ao ler 5, n < 5 é falso e não produz uma linha nova. A recursão termina. A condição usa o valor anterior: trocar < 5 por <= 5 produziria também 6.

Os estados da recursão:

Não contes com um suposto limite fixo de profundidade para tornar uma consulta correta. Escreve uma condição de terminação e, quando necessário, um limite de trabalho compatível com a pergunta. Os limites e restrições dependem do motor.

Percorrer uma hierarquia

Cada categoria tem no máximo uma categoria mãe. A raiz tem NULL. Queremos a raiz e todos os seus descendentes, com profundidade 0 para a raiz.

PRAGMA foreign_keys = ON;
CREATE TABLE Categoria(
id INTEGER PRIMARY KEY,
nome TEXT NOT NULL,
idMae INTEGER REFERENCES Categoria(id)
);
INSERT INTO Categoria VALUES
(1, 'Informática', NULL), (2, 'Periféricos', 1),
(3, 'Teclados', 2), (4, 'Monitores', 1), (5, 'Casa', NULL);
WITH RECURSIVE Descendente(id, nome, profundidade) AS (
SELECT id, nome, 0 FROM Categoria WHERE id = 1
UNION ALL
SELECT c.id, c.nome, d.profundidade + 1
FROM Categoria AS c JOIN Descendente AS d ON c.idMae = d.id
)
SELECT id, nome, profundidade FROM Descendente
ORDER BY profundidade, id;

A condição c.idMae = d.id procura filhos dos nós já alcançados. Os níveis da resposta tornam visíveis esses passos:

Informática (1), profundidade 0
├── Periféricos (2), profundidade 1
│   └── Teclados (3), profundidade 2
└── Monitores (4), profundidade 1

Casa (5): outra raiz, não alcançada

A saída é ordenada por profundidade e id, não pela disposição desta árvore. Inverter a condição para c.id = d.idMae seguiria ascendentes; nesse caso teríamos de conservar idMae na CTE para o usar no passo seguinte.

O exemplo assume uma hierarquia sem ciclos. A estrangeira verifica que a mãe existe, mas permite criar um ciclo entre categorias. A validação da estrutura é uma regra adicional.

Grafos e ciclos

Num grafo, o mesmo nó pode ser alcançado por vários caminhos. Se queremos apenas o conjunto de nós alcançáveis, podemos usar uma coluna id e UNION, que elimina resultados já obtidos:

CREATE TABLE Aresta(origem INTEGER, destino INTEGER,
PRIMARY KEY(origem, destino));
INSERT INTO Aresta VALUES (1,2), (2,3), (3,1), (2,4);
WITH RECURSIVE Alcancavel(id) AS (
SELECT 1
UNION
SELECT a.destino FROM Aresta AS a
JOIN Alcancavel AS r ON a.origem = r.id
)
SELECT id FROM Alcancavel ORDER BY id;

O resultado é 1, 2, 3 e 4. Voltar ao nó 1 não introduz um novo tuplo. UNION ALL conservaria as repetições e seguiria o ciclo indefinidamente se não houvesse outra condição.

Divisores e distribuição de mandatos

No método de D’Hondt, cada lista compete com os quocientes votos/1, votos/2 e assim por diante, até ao número de mandatos M. Os M lugares vão para os M maiores quocientes. Com 4 mandatos e votos A=100, B=60, C=40, os quocientes são:

Os quatro maiores são 100 de A, 60 de B, 50 de A e 40 de C. O resultado é A com 2, B com 1 e C com 1. Só a sequência de divisores 1 a M precisa de recursão; o resto são operações comuns:

WITH RECURSIVE Divisor(k) AS (
SELECT 1
UNION ALL
SELECT k + 1 FROM Divisor WHERE k < 4
),
Voto(lista, votos) AS (
VALUES ('A', 100), ('B', 60), ('C', 40)
),
Quociente AS (
SELECT v.lista, v.votos / (d.k * 1.0) AS q
FROM Voto AS v CROSS JOIN Divisor AS d
),
Top AS (
SELECT lista FROM Quociente
ORDER BY q DESC, lista LIMIT 4
)
SELECT lista, COUNT(*) AS mandatos
FROM Top GROUP BY lista ORDER BY lista;

A resposta é A com 2, B com 1 e C com 1. Usamos neste exercício a regra de desempate pelo nome da lista, apenas para definir o resultado SQL. Não é uma descrição da regra eleitoral legal. O ORDER BY q DESC, lista aplica essa convenção: em caso de quocientes iguais, vence a lista com nome menor. Sem esse segundo critério, a escolha entre empatados ficaria indefinida. A agregação final conta lugares, não votos: cada ocorrência de uma lista nos 4 primeiros quocientes vale um mandato.

Limites de SQLite

Em SQLite, a parte recursiva tem de usar a tabela recursiva segundo as restrições do motor, sem referências adicionais em subconsultas, e não pode usar agregações nem funções de janela nessa parte. No exemplo de D’Hondt, a agregação está no SELECT exterior, depois de produzidas as linhas. Uma CTE recursiva não é necessariamente a melhor opção para toda a pergunta: a recursão pertence à geração dos divisores; não precisamos de tornar toda a agregação recursiva.