PostgreSQL: Documentação: 13: 7.8. Com consultas (expressões de tabelas comuns)
7.8.1. Seleccionar com
o valor básico de SELECT
em WITH
é dividir consultas complicadas em partes mais simples. Um exemplo é:
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;
que exibe totais de vendas por produto em apenas as regiões de vendas de topo. A cláusula WITH
define duas declarações auxiliares chamadas regional_sales
e top_regions
, onde a saída de regional_sales
é usada em top_regions
e a saída de top_regions
é usada na consulta primária SELECT
. Este exemplo poderia ter sido escrito sem WITH
, mas teríamos precisado de dois níveis de sub-SELECT
S. é um pouco mais fácil de seguir por este caminho.
o opcional RECURSIVE
modificador muda WITH
de uma mera conveniência sintática para uma característica que realiza coisas que de outra forma não seriam possíveis em SQL padrão. Usando RECURSIVE
, uma consulta WITH
pode se referir à sua própria saída. Um exemplo muito simples é esta consulta para somar os inteiros de 1 a 100:
WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;
a forma geral de uma consulta recursiva WITH
é sempre um termo não recursivo, então UNION
(ou UNION ALL
), então um termo recursivo, onde apenas o termo recursivo pode conter uma referência à própria saída da consulta. Tal consulta é executada da seguinte forma::
avaliação recursiva da pesquisa
-
avaliar o termo não-recursivo. Para
UNION
(mas nãoUNION ALL
), eliminar linhas duplicadas. Incluir todas as linhas restantes no resultado da consulta recursiva, e também colocá-las em uma tabela de trabalho temporário. -
enquanto a mesa de trabalho não estiver vazia, repita estes passos:
-
avalie o termo recursivo, substituindo o conteúdo atual da tabela de trabalho pela auto-referência recursiva. Para
UNION
(mas nãoUNION ALL
), descartar linhas e linhas duplicadas que duplicem qualquer linha do resultado anterior. Incluir todas as linhas restantes no resultado da consulta recursiva, e também colocá-las em uma tabela intermediária temporária. -
substitua o conteúdo da tabela de trabalho pelo conteúdo da tabela intermediária e, em seguida, esvazie a tabela intermediária.
-
Nota
Estritamente falando, este processo de iteração não recursão, mas RECURSIVE
é a terminologia escolhida pelo SQL comitê de padrões.
no exemplo acima, a tabela de trabalho tem apenas uma linha em cada passo, e assume os valores de 1 a 100 em etapas sucessivas. Na 100ª etapa, não há saída por causa da cláusula WHERE
, e assim a consulta termina.
consultas recursivas são tipicamente usadas para lidar com dados hierárquicos ou estruturados em árvores. Um exemplo útil é esta consulta para encontrar todos os diretos e indiretos sub-partes de um produto, tendo apenas uma tabela que mostra imediato inclusões:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part
Quando se trabalha com consultas recursivas é importante certifique-se de que a parte recursiva da consulta retornará eventualmente não tuplas, ou, então, a consulta irá repetir indefinidamente. Às vezes, usando UNION
em vez de UNION ALL
pode conseguir isso descartando as linhas que duplicam as linhas de saída anteriores. No entanto, muitas vezes um ciclo não envolve linhas de saída que são completamente duplicadas: pode ser necessário verificar apenas um ou alguns campos para ver se o mesmo ponto foi alcançado antes. O método padrão para lidar com tais situações é calcular uma matriz dos valores já visitados. Por exemplo, considere a seguinte consulta que procura numa tabela graph
usando um campo link
:
WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;
esta consulta fará loop se as relações link
contiverem ciclos. Porque precisamos de uma saída de “profundidade”, apenas mudando UNION ALL
para UNION
não eliminaria o looping. Em vez disso, precisamos reconhecer se chegamos à mesma linha novamente, enquanto seguimos um caminho particular de links. Nós adicionamos duas colunas path
e cycle
à consulta propensa ao loop:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;
além de prevenir ciclos, o valor do array é muitas vezes útil em seu próprio direito como representando o “caminho” tomado para chegar a qualquer linha particular.
no caso geral onde mais de um campo precisa ser verificado para reconhecer um ciclo, use um array de linhas. Por exemplo, se fosse preciso comparar os campos f1
e f2
:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;
Dica
Omitir o ROW()
sintaxe no caso comum onde apenas um campo precisa ser verificado para reconhecer um ciclo. Isso permite que um array simples ao invés de um array de tipo compósito seja usado, ganhando eficiência.
Tip
the recursive query evaluation algorithm produces its output in width-first search order. Você pode exibir os resultados em ordem de busca em profundidade, fazendo a consulta externa ORDER BY
uma coluna de “caminho” construída desta forma.
um truque útil para testar consultas quando você não tem certeza se eles podem loop é colocar um LIMIT
na consulta Pai. Por exemplo, esta consulta seria loop para sempre sem o LIMIT
:
WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;
isto funciona porque a implementação do PostgreSQL avalia apenas tantas linhas de uma consulta WITH
como são realmente obtidas pela consulta Pai. Usar este truque na produção não é recomendado, porque outros sistemas podem funcionar de forma diferente. Além disso, normalmente não funcionará se você fizer a consulta externa ordenar os resultados da consulta recursiva ou juntá-los a alguma outra tabela, porque em tais casos a consulta externa normalmente tentará obter todos os resultados da consulta WITH
de qualquer maneira.
uma propriedade útil de WITH
consultas é que eles são normalmente avaliados apenas uma vez por execução da consulta-mãe, mesmo se eles são referidos mais de uma vez pela consulta-mãe ou sibling WITH
consultas. Assim, cálculos caros que são necessários em vários lugares podem ser colocados dentro de uma consulta WITH
para evitar trabalho redundante. Outra possível aplicação é evitar avaliações múltiplas indesejadas de funções com efeitos secundários. No entanto, o outro lado desta moeda é que o otimizador não é capaz de empurrar restrições da consulta pai para baixo em uma consulta multiply referenciada WITH
, uma vez que isso pode afetar todos os usos da saída da consulta WITH
quando deve afetar apenas um. A consulta multiply-referenciada WITH
será avaliada como escrita, sem supressão de linhas que a consulta pai pode descartar depois. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)
However, if a WITH
query is non-recursive and side-effect-free (that is, it is a SELECT
containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. Por padrão, isso acontece se a consulta Pai referencia a consulta WITH
apenas uma vez, mas não se referencia a consulta WITH
mais de uma vez. Você pode anular essa decisão, especificando MATERIALIZED
para forçar o cálculo separado da consulta WITH
, ou especificando NOT MATERIALIZED
para forçá-la a ser fundido na consulta Pai. A última opção corre o risco de duplicar a computação da consulta WITH
, mas ela ainda pode dar uma poupança líquida se cada uso da consulta WITH
necessita apenas de uma pequena parte da saída completa da consulta WITH
.
Um exemplo simples destas regras é
WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;
Este WITH
consulta será dobrado, produzindo o mesmo plano de execução como
SELECT * FROM big_table WHERE key = 123;
Em particular, se existe um índice em key
, ele provavelmente será usado para buscar apenas as linhas de ter key = 123
. Por outro lado, em
WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;
a consulta WITH
será materializada, produzindo uma cópia temporária de big_table
que é então unida a si mesma — sem benefício de qualquer índice. Esta consulta será executada de forma muito mais eficiente se escrita como
WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;
de modo que as restrições da consulta-mãe podem ser aplicadas diretamente a varreduras de big_table
.
um exemplo onde NOT MATERIALIZED
pode ser indesejável é
WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
aqui, a materialização da consulta WITH
garante que very_expensive_function
é avaliado apenas uma vez por linha de tabela, e não duas vezes.
os exemplos acima mostram apenas WITH
sendo usado com SELECT
, mas pode ser ligado da mesma forma a INSERT
, UPDATE
, ou DELETE
. Em cada caso, fornece efetivamente tabelas temporárias que podem ser referidas no comando principal.