Consultas jerárquicas y recursivas en SQL - Hierarchical and recursive queries in SQL

Una consulta jerárquica es un tipo de consulta SQL que maneja datos de modelos jerárquicos . Son casos especiales de consultas de punto fijo recursivas más generales, que calculan cierres transitivos .

En SQL estándar : 1999 , las consultas jerárquicas se implementan mediante expresiones de tabla común recursivas (CTE). A diferencia de la cláusula de conexión anterior de Oracle , los CTE recursivos se diseñaron con semántica de punto fijo desde el principio. Los CTE recursivos del estándar estaban relativamente cerca de la implementación existente en IBM DB2 versión 2. Los CTE recursivos también son compatibles con Microsoft SQL Server (desde SQL Server 2008 R2), Firebird 2.1 , PostgreSQL 8.4+ , SQLite 3.8.3+ , IBM Informix versión 11.50+, CUBRID , MariaDB 10.2+ y MySQL 8.0.1+ . Tableau tiene documentación que describe cómo se pueden usar los CTE. TIBCO Spotfire no admite CTE, mientras que la implementación de Oracle 11g Release 2 carece de semántica de punto fijo.

Sin expresiones de tabla comunes o cláusulas de conexión, es posible lograr consultas jerárquicas con funciones recursivas definidas por el usuario.

Expresión de tabla común

Una expresión de tabla común, o CTE, (en SQL ) es un conjunto de resultados llamado temporal, derivada de una consulta sencilla y definida dentro del ámbito de ejecución de un SELECT, INSERT, UPDATE, o DELETEcomunicado.

Los CTE se pueden considerar como alternativas a las tablas derivadas ( subconsultas ), vistas y funciones definidas por el usuario en línea.

Las expresiones de tabla comunes son compatibles con Teradata (a partir de la versión 14), DB2 , Informix (a partir de la versión 14.1), Firebird (a partir de la versión 2.1), Microsoft SQL Server (a partir de la versión 2005), Oracle (con recursividad desde la versión 11g 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 (a partir de la versión 9), Vertica , H2 (experimental) y muchos otros . Oracle llama a los CTE "factorización de subconsultas".

La sintaxis de un CTE (que puede ser recursiva o no) es la siguiente: 

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

donde with_queryla sintaxis es: 

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

Las CTE recursivas se pueden utilizar para recorrer relaciones (como gráficos o árboles) aunque la sintaxis es mucho más complicada porque no se crean pseudocolumnas automáticas (como a LEVEL continuación ); si se desean, deben crearse en el código. Consulte la documentación de MSDN o la documentación de IBM para ver ejemplos de tutoriales.

Por RECURSIVElo general, la palabra clave no se necesita después de WITH en sistemas que no sean PostgreSQL.

En SQL: 1999, una consulta recursiva (CTE) puede aparecer en cualquier lugar donde se permita una consulta. Es posible, por ejemplo, nombrar el resultado usando CREATE[ RECURSIVE] VIEW. Usando un CTE dentro de un INSERT INTO, uno puede poblar una tabla con datos generados a partir de una consulta recursiva; La generación de datos aleatorios es posible utilizando esta técnica sin utilizar declaraciones de procedimiento.

Algunas bases de datos, como PostgreSQL, admiten un formato CREATE RECURSIVE VIEW más corto que se traduce internamente a la codificación WITH RECURSIVE.

Un ejemplo de una consulta recursiva que calcula el factorial de números del 0 al 9 es el siguiente: 

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

Una sintaxis alternativa es la CONNECT BYconstrucción no estándar ; fue introducido por Oracle en la década de 1980. Antes de Oracle 10g, la construcción solo era útil para atravesar gráficos acíclicos porque devolvía un error al detectar cualquier ciclo; en la versión 10g, Oracle introdujo la función NOCYCLE (y palabra clave), haciendo que el recorrido también funcione en presencia de ciclos.

CONNECT BYes compatible con Snowflake , EnterpriseDB , base de datos Oracle , CUBRID , IBM Informix y DB2, aunque solo si está habilitado como modo de compatibilidad. La sintaxis es la siguiente: 

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 ejemplo,
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr "manager"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

El resultado de la consulta anterior se vería así: 

 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)

Pseudocolumnas

  • NIVEL
  • CONNECT_BY_ISLEAF
  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ROOT

Operadores unarios

El siguiente ejemplo devuelve el apellido de cada empleado en el departamento 10, cada gerente por encima de ese empleado en la jerarquía, el número de niveles entre el gerente y el empleado y la ruta entre los dos: 

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";

Funciones

  • SYS_CONNECT_BY_PATH

Ver también

Referencias

Otras lecturas

  • CJ Date (2011). SQL y teoría relacional: cómo escribir código SQL preciso (2ª ed.). O'Reilly Media. págs. 159-163. ISBN 978-1-4493-1640-2.

Libros de texto académicos . Tenga en cuenta que estos cubren solo el estándar SQL: 1999 (y Datalog), pero no la extensión de Oracle.

enlaces externos