Common Table Expressions (CTEs) são uma poderosa construção dentro de SQL. Na conversa diária, você pode ouvir Eti referidos como WITH cláusulas. Você pode pensar em CTEs como semelhante a uma visão que é materializada apenas enquanto essa consulta está em execução e não existe fora dessa consulta. Os CTEs podem ser Blocos de construção muito úteis para permitir que as suas grandes consultas SQL sejam mais legíveis. Mas, eles também podem ser usados recursivamente permitindo que você crie algumas consultas muito complexas sem ter que cair para uma linguagem processual como plpgsql ou plv8.
os Eti recursivos permitem-se ser chamados até que alguma condição seja cumprida. Vamos saltar para a direita e explorar uma CTE recursiva—um básico, e usando PostgreSQL é claro—e, em seguida, vamos dissecar o CTE recursiva um pouco mais para ver o que podemos usá-lo para:
WITH RECURSIVE tens (n) AS ( SELECT 10 UNION ALL SELECT n+10 FROM tens WHERE n+10<= 100 ) SELECT n FROM tens; Quando acima é executado vamos obter o seguinte resultado:
n ----- 10 20 30 40 50 60 70 80 90 100 (10 rows) com o acima, também poderíamos facilmente fazer isso com um generate_series. Mas fique conosco e você verá as coisas mais complexas que podemos fazer que não são possíveis com generate_series. Primeiro vamos ver melhor como funciona.
a primeira parte que você vai notar é WITH RECURSIVE. Isto diz a Postgres que o CTE pode chamar-se recursivamente. A próxima parte que você vai notar é que ele leva alguns parâmetros nele. Neste caso (n), também pode tomar mais do que um se precisar.
movendo-se mais para o CTE, temos a primeira consulta que é executada, SELECT 10, que gera o primeiro valor. A segunda parte é onde toda a diversão começa. O UNION ALL especifica que vamos devolver todos os registros que são produzidos a partir do loop. Então SELECT n+10 FROM tens WHERE n+10<= 100 vai continuar chamando o tens CTE que é criado até que a condição é cumprida.
então esses são os fundamentos, mas a questão interessante é: quando você usaria um CTE recursivo? Quando você tem uma árvore ou estrutura hierárquica para seus dados, CTEs recursivos podem tornar a vida muito mais fácil do que carregar todos os seus dados e executar um loop em seu código. Para aplicações que lidam com o comércio eletrônico e categorias de compras, CTEs recursivos são uma grande ajuda.
vamos tentar fazer com que os benefícios dos Eti recursivos em Postgres sejam um pouco mais concretos, com um exemplo. Primeiro vamos criar uma mesa de funcionários, depois vamos carregar alguns funcionários de exemplo. (Sim, você verá alguns nomes da nossa equipe aqui na Citus Data nesta lista. Foram os primeiros nomes que me vieram à cabeça.)
CREATE TABLE employees ( id serial, name varchar(255), manager_id int ); INSERT INTO employees VALUES (1, 'Umur', null); INSERT INTO employees VALUES (2, 'Craig', 1); INSERT INTO employees VALUES (3, 'Daniel', 2); INSERT INTO employees VALUES (4, 'Claire', 1); INSERT INTO employees VALUES (5, 'Lindsay', 2); INSERT INTO employees VALUES (6, 'Will', 2); INSERT INTO employees VALUES (7, 'Burak', 2); INSERT INTO employees VALUES (8, 'Eren', 2); INSERT INTO employees VALUES (9, 'Katie', 3); INSERT INTO employees VALUES (10, 'Teresa', 4); agora vou escrever uma consulta que me dá todos os relatórios que aparecem em uma certa org dentro da empresa. Neste caso, vou buscar todos os meus relatórios, juntamente com a identificação do gerente de cada pessoa.:
WITH RECURSIVE managertree AS ( SELECT id, name, manager_id FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN managertree mtree ON mtree.id = e.manager_id ) SELECT * FROM managertree; id | name | manager_id ----+---------+------------ 2 | Craig | 1 3 | Daniel | 2 5 | Lindsay | 2 6 | Will | 2 7 | Burak | 2 8 | Eren | 2 9 | Katie | 3 (7 rows) da próxima vez que você precisar fazer alguma computação recursiva através de seus dados, considere fazê-lo diretamente em SQL ao invés de carregar todos esses dados em sua aplicação. Para uma leitura adicional, considere dar uma olhada em alguns desses recursos úteis:
- PostgreSQL docs em CTEs
- Resolver o problema do caixeiro viajante com uma CTE
- Fazer uma árvore com todos os filhos
Como nota CTEs neste momento são uma otimização da cerca no PostgreSQL, mas há esperanças de que a mudança no futuro. As expressões de tabelas comuns são uma ferramenta incrivelmente útil para relatar. Às vezes, a legibilidade das CTEs supera o impacto no desempenho, mas considerar os trade-offs, ao usá-los
- diversão com o sql
- Postgres
- dicas