CTEs (WITH) e recursão

Uma CTE (Common Table Expression) é uma subconsulta com nome, declarada antes do SELECT principal. Ela não muda o resultado; muda quem consegue ler a consulta. Cada passo vira um degrau com nome.

WITH entregues AS (
  SELECT * FROM pedidos WHERE status = 'entregue'
),
por_cliente AS (
  SELECT cliente_id, SUM(valor) AS total FROM entregues GROUP BY cliente_id
)
SELECT c.nome, p.total
FROM por_cliente p
JOIN clientes c ON c.id = p.cliente_id
ORDER BY p.total DESC, c.nome;

nome  | total
----- | -----
Ana   | 1130
Bruno | 1050
Diego | 1050
Carla | 180
Elisa | 30

WITH RECURSIVE: um calendário

A parte recursiva roda até não produzir linha nova. É o jeito clássico de gerar a tabela de datas que "preenche os buracos" de um relatório: aqui, dias sem venda aparecem com zero.

WITH RECURSIVE dias(dia) AS (
  SELECT '2024-06-01'
  UNION ALL
  SELECT date(dia, '+1 day') FROM dias WHERE dia < '2024-06-07'
)
SELECT d.dia, COALESCE(SUM(p.valor), 0) AS vendido
FROM dias d
LEFT JOIN pedidos p ON p.data = d.dia AND p.status = 'entregue'
GROUP BY d.dia
ORDER BY d.dia;

dia        | vendido
---------- | -------
2024-06-01 | 1130
2024-06-02 | 180
2024-06-03 | 1050
2024-06-04 | 180
2024-06-05 | 980
2024-06-06 | 0
2024-06-07 | 0

Sem a condição de parada (WHERE dia < …) a recursão não termina. No site, a consulta é interrompida em alguns segundos; num banco de produção, ela segura recursos até alguém matá-la.

Hierarquias

WITH RECURSIVE equipe(id, nome, chefe, nivel) AS (
  SELECT 1, 'Diretoria', NULL, 0
  UNION ALL
  SELECT t.id, t.nome, t.chefe, e.nivel + 1
  FROM (SELECT 2 AS id, 'Dados' AS nome, 1 AS chefe UNION ALL SELECT 3, 'Engenharia', 2 UNION ALL SELECT 4, 'BI', 2) t
  JOIN equipe e ON t.chefe = e.id
)
SELECT substr('        ', 1, nivel * 2) || nome AS arvore, nivel FROM equipe ORDER BY id;

arvore         | nivel
-------------- | -----
Diretoria      | 0
  Dados        | 1
    Engenharia | 2
    BI         | 2

• No Spark SQL, CTE não é materializada por padrão: se for referenciada duas vezes, pode ser calculada duas vezes. Para reaproveitar um resultado caro, grave uma tabela ou use cache (veja Cache e persist).
• WITH RECURSIVE chegou ao Spark SQL e ao Databricks só em versões recentes: confira a documentação da sua versão antes de contar com ele. Sem ele, hierarquia se resolve com um laço de JOINs em PySpark.