Consultas hierárquicas e recursivas em SQL - Hierarchical and recursive queries in SQL

Uma consulta hierárquica é um tipo de consulta SQL que trata os dados do modelo hierárquico . Eles são casos especiais de consultas recursivas de pontos fixos mais gerais, que calculam fechamentos transitivos .

No SQL: 1999 padrão, as consultas hierárquicas são implementadas por meio de expressões de tabela comuns recursivas (CTEs). Ao contrário da cláusula connect-by anterior da Oracle, os CTEs recursivos foram projetados com semântica de pontos fixos desde o início. CTEs recursivos do padrão eram relativamente próximos da implementação existente no IBM DB2 versão 2. CTEs recursivos também são suportados pelo Microsoft SQL Server (desde SQL Server 2008 R2), Firebird 2.1 , PostgreSQL 8.4+ , SQLite 3.8.3+ , IBM Informix versão 11.50+, CUBRID , MariaDB 10.2+ e MySQL 8.0.1+ . O Tableau tem uma documentação que descreve como os CTEs podem ser usados. O TIBCO Spotfire não oferece suporte a CTEs, enquanto a implementação do Oracle 11g Release 2 carece de semântica de ponto de fixação.

Sem expressões de tabela comuns ou cláusulas conectadas por, é possível realizar consultas hierárquicas com funções recursivas definidas pelo usuário.

Expressão de tabela comum

A expressão de tabela comum, ou CTE, (em SQL ) é um conjunto de resultados nomeado temporário, derivado de uma consulta simples e definidas no âmbito de execução de um SELECT, INSERT, UPDATE, ou DELETEdeclaração.

CTEs podem ser considerados como alternativas para tabelas derivadas ( subconsulta ), visualizações e funções definidas pelo usuário em linha.

Expressões de tabela comuns são suportadas por Teradata (começando com a versão 14), DB2 , Informix (começando com a versão 14.1), Firebird (começando com a versão 2.1), Microsoft SQL Server (começando com a versão 2005), Oracle (com recursão desde 11g versão 2 ), PostgreSQL (desde 8.4), MariaDB (desde 10.2), MySQL (desde 8.0), SQLite (desde 3.8.3), HyperSQL , Informix (desde 14.10), Google BigQuery , Sybase (desde a versão 9), Vertica , H2 (experimental) e muitos outros . A Oracle chama os CTEs de "fatoração de subconsulta".

A sintaxe de um CTE (que pode ou não ser recursiva) é a seguinte: 

WITH [RECURSIVE] with_query [, ...]
SELECT ...

onde with_querya sintaxe é: 

query_name [ (column_name [,...]) ] AS (SELECT ...)

CTEs recursivos podem ser usados ​​para atravessar relações (como gráficos ou árvores), embora a sintaxe seja muito mais envolvente porque não há pseudocolunas automáticas criadas (como LEVEL abaixo ); se forem desejados, eles devem ser criados no código. Consulte a documentação do MSDN ou a documentação da IBM para obter exemplos de tutoriais.

A RECURSIVEpalavra-chave geralmente não é necessária após WITH em sistemas diferentes do PostgreSQL.

No SQL: 1999, uma consulta recursiva (CTE) pode aparecer em qualquer lugar em que uma consulta seja permitida. É possível, por exemplo, nomear o resultado usando CREATE[ RECURSIVE] VIEW. Usando um CTE dentro de um INSERT INTO, pode-se preencher uma tabela com dados gerados a partir de uma consulta recursiva; a geração de dados aleatórios é possível usando esta técnica sem usar quaisquer instruções procedimentais.

Alguns bancos de dados, como PostgreSQL, suportam um formato CREATE RECURSIVE VIEW mais curto que é traduzido internamente para a codificação WITH RECURSIVE.

Um exemplo de consulta recursiva computando o fatorial de números de 0 a 9 é o seguinte: 

WITH RECURSIVE temp (n, fact) AS 
(SELECT 0, 1 -- Initial Subquery
  UNION ALL 
 SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery 
        WHERE n < 9)
SELECT * FROM temp;

CONECTAR POR

Uma sintaxe alternativa é a CONNECT BYconstrução não padrão ; foi introduzido pela Oracle na década de 1980. Antes do Oracle 10g, a construção só era útil para percorrer gráficos acíclicos porque retornava um erro ao detectar qualquer ciclo; na versão 10g a Oracle introduziu o recurso NOCYCLE (e palavra-chave), fazendo com que a travessia funcionasse também na presença de ciclos.

CONNECT BYé compatível com Snowflake , EnterpriseDB , banco de dados Oracle , CUBRID , IBM Informix e DB2, embora apenas se estiver ativado como um modo de compatibilidade. A sintaxe é a seguinte: 

SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... ]
[ GROUP BY ... ]
[ HAVING ... ]
...
Por exemplo,
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

O resultado da consulta acima seria semelhante a: 

 level |  employee   | empno | manager
-------+-------------+-------+---------
     1 | KING        |  7839 |
     2 |   JONES     |  7566 |    7839
     3 |     SCOTT   |  7788 |    7566
     4 |       ADAMS |  7876 |    7788
     3 |     FORD    |  7902 |    7566
     4 |       SMITH |  7369 |    7902
     2 |   BLAKE     |  7698 |    7839
     3 |     ALLEN   |  7499 |    7698
     3 |     WARD    |  7521 |    7698
     3 |     MARTIN  |  7654 |    7698
     3 |     TURNER  |  7844 |    7698
     3 |     JAMES   |  7900 |    7698
     2 |   CLARK     |  7782 |    7839
     3 |     MILLER  |  7934 |    7782
(14 rows)

Pseudo-colunas

  • NÍVEL
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

Operadores unários

O exemplo a seguir retorna o sobrenome de cada funcionário no departamento 10, cada gerente acima desse funcionário na hierarquia, o número de níveis entre gerente e funcionário e o caminho entre os dois: 

SELECT ename "Employee", CONNECT_BY_ROOT ename "Manager",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM emp
WHERE LEVEL > 1 and deptno = 10
CONNECT BY PRIOR empno = mgr
ORDER BY "Employee", "Manager", "Pathlen", "Path";

Funções

  • SYS_CONNECT_BY_PATH

Veja também

Referências

Leitura adicional

  • Data CJ (2011). SQL e teoria relacional: como escrever código SQL preciso (2ª ed.). O'Reilly Media. pp. 159–163. ISBN 978-1-4493-1640-2.

Livros didáticos acadêmicos . Observe que eles cobrem apenas o padrão SQL: 1999 (e Datalog), mas não a extensão Oracle.

links externos