CTEs e consultas recursivas

Nomear resultados intermédios

WITH dá nome a um resultado dentro de uma instrução; o nome morre no fim dela. Não promete materialização nem desempenho: serve para exprimir cada nível de agregação.

Para totais por cliente: primeiro totais por encomenda, depois por cliente. Juntar antes de agregar multiplica linhas e duplica valores.

• Contagens de níveis diferentes (freguesias por município, municípios por distrito) pedem CTEs separadas: cada uma com a sua chave.
• Máximos por grupo correlacionam o MAX pelo grupo e conservam empates; LIMIT 1 global escolhe uma linha para todos.
• Ao reunir origens, a identidade é (tipo, id): o mesmo número em origens diferentes não é o mesmo concorrente.

Escrever a recursão

Uma CTE recursiva junta parte inicial (primeiras linhas) e passo (novas linhas a partir das anteriores), com o mesmo número de colunas 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;

Produz 1 a 5. O filtro testa o valor anterior: < 5 para em 5; <= 5 produziria 6. A terminação tem de vir da consulta, não de um limite presumido do motor.

Numa hierarquia Categoria(id, idMae), começa na raiz e junta filhos por c.idMae = d.id, com profundidade 0 na raiz. A estrangeira garante a mãe, mas não proíbe ciclos: isso é regra adicional.

Terminar com ciclos

Com Aresta(1,2),(2,3),(3,1),(2,4), os alcançáveis de 1 são 1, 2, 3, 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;

CREATE TABLE Aresta(origem INTEGER NOT NULL,
destino INTEGER NOT NULL, PRIMARY KEY(origem, destino));
INSERT INTO Aresta VALUES (1,2), (2,3), (3,1), (2,4);

UNION elimina tuplos já obtidos e trava o ciclo; UNION ALL seguiria sem parar. A deduplicação compara o tuplo inteiro: com profundidade, (1,0) e (1,3) são distintos e o ciclo reabre. Para caminhos, impede repetição de nós no caminho e limita a exploração.

O caso D’Hondt

Para $M$ mandatos: gera divisores 1 a $M$ (só isto é recursivo), cruza listas com divisores, calcula votos/divisor e fica com os $M$ maiores quocientes. Com A=100, B=60, C=40 e $M=4$: 100/A, 60/B, 50/A, 40/C. A leva 2, B e C 1 cada. Usa divisão real e desempate explícito; o desempate por nome do exercício é convenção, não lei eleitoral.

Em SQLite, o passo recursivo não aceita agregações nem janelas: agrega no SELECT exterior.

Hierarquia e passos da recursão.