Pular para o conteúdo principal
Página traduzida

Esta página foi traduzida do inglês com assistência de IA. Se houver diferenças entre as duas, a versão em inglês é a referência.

WITH (CTEs)

Define uma expressão de tabela comum (CTE) para uso em uma instrução SELECT, INSERT, UPDATE ou DELETE subsequente. As CTEs funcionam como conjuntos de resultados nomeados temporários que existem apenas durante a consulta.

Veja também: [SELECT] [INSERT] [UPDATE] [DELETE]


Sintaxe​

withClause::=

WITHRECURSIVEcteDefinition','cteDefinition

cteDefinition::=

cteName'('columnList')'AS'('selectStatement')'

   

WITH [RECURSIVE] cte_name [(column_name, ...)] AS (
select_statement
)
[, cte_name [(column_name, ...)] AS (
select_statement
)]
SELECT ... FROM cte_name ...;

Argumentos​

RECURSIVE Palavra-chave opcional que permite que a CTE faça referência a si mesma, possibilitando consultas recursivas, como percorrer dados hierárquicos.

cte_name O nome atribuído à CTE. Esse nome pode ser referenciado na consulta principal como se fosse uma tabela.

column_name Opcional. Nomes das colunas do conjunto de resultados da CTE. Se não forem especificados, os nomes das colunas são derivados da instrução SELECT.

select_statement A consulta que define o conjunto de resultados da CTE.

Valor(es) retornado(s)​

As CTEs não retornam valores diretamente. Elas definem conjuntos de resultados temporários que podem ser referenciados na consulta principal.


Exemplos​

CTE básica para organizar consultas complexas​

Uma CTE pode simplificar consultas complexas ao dividi-las em componentes lógicos e nomeados.

-- Calculate moving average of commit activity
WITH weekly_totals AS (
SELECT
week,
SUM(json_each.value) as commits_this_week
FROM github.repos.stats_commit_activity, JSON_EACH(days)
WHERE owner = 'stackql'
AND repo = 'stackql'
GROUP BY week
)
SELECT
week,
commits_this_week,
ROUND(AVG(commits_this_week) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
SUM(commits_this_week) OVER (ORDER BY week) as cumulative_commits
FROM weekly_totals
ORDER BY week;

Combinar dados de várias fontes com UNION ALL​

As CTEs são úteis para combinar dados de várias fontes antes de realizar uma análise.

-- Rank total contributions across multiple repos
WITH all_contributors AS (
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql'
UNION ALL
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-deploy'
UNION ALL
SELECT login, contributions, html_url
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
)
SELECT
DENSE_RANK() OVER (ORDER BY SUM(contributions) DESC) as rank,
login,
SUM(contributions) as total_contributions,
MAX(html_url) as html_url
FROM all_contributors
GROUP BY login
ORDER BY total_contributions DESC;

Usar CTEs com funções de janela para classificação​

-- Rank contributors within each repo using CTE and window functions
WITH repo_contributors AS (
SELECT
repo,
login,
contributions,
RANK() OVER (PARTITION BY repo ORDER BY contributions DESC) as rank_in_repo,
SUM(contributions) OVER (PARTITION BY repo) as repo_total_contributions
FROM (
SELECT 'stackql' as repo, login, contributions
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql'
UNION ALL
SELECT 'stackql-provider-registry' as repo, login, contributions
FROM github.repos.contributors
WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
) t
)
SELECT
repo,
login,
contributions,
rank_in_repo
FROM repo_contributors
ORDER BY repo, rank_in_repo;

Várias CTEs em uma única consulta​

Você pode definir várias CTEs separadas por vírgulas.

-- Analyze issues and releases together
WITH open_issues AS (
SELECT number, title, created_at
FROM github.issues.issues
WHERE owner = 'stackql'
AND repo = 'stackql'
AND state = 'open'
),
recent_releases AS (
SELECT tag_name, published_at
FROM github.repos.releases
WHERE owner = 'stackql'
AND repo = 'stackql'
ORDER BY published_at DESC
LIMIT 5
)
SELECT
'Open Issues' as category,
COUNT(*) as count
FROM open_issues
UNION ALL
SELECT
'Recent Releases' as category,
COUNT(*) as count
FROM recent_releases;

CTEs recursivas​

As CTEs recursivas permitem que uma CTE faça referência a si mesma, possibilitando consultas que percorrem estruturas de dados hierárquicas ou em forma de grafo.

WITH RECURSIVE cte_name (columns) AS (
-- Base case (anchor member)
SELECT ...
UNION ALL
-- Recursive case (references cte_name)
SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;
dica

As CTEs recursivas são poderosas para dados hierárquicos, como organogramas, sistemas de arquivos ou árvores de dependências. Sempre inclua uma condição de término no caso recursivo para evitar loops infinitos.

Práticas recomendadas​

  1. Use CTEs para facilitar a leitura: divida consultas complexas em componentes lógicos e nomeados.
  2. Combine com funções de janela: as CTEs funcionam bem com funções de janela para análises avançadas.
  3. Encadeie várias CTEs: faça referência a CTEs anteriores nas posteriores para construir uma lógica complexa passo a passo.
  4. Considere o desempenho: embora as CTEs melhorem a legibilidade, CTEs muito grandes podem afetar o desempenho. Teste com os volumes de dados reais.

Para obter mais informações, consulte https://sqlite.org/lang_with.html.