sql - transitiva - Consulta recursiva utilizada para el cierre transitivo

cierre transitivo base de datos

Question

He creado un ejemplo simple para ilustrar el cierre transitivo utilizando consultas recursivas en PostgreSQL.

Sin embargo, algo está apagado con mi consulta recursiva. Todavía no estoy familiarizado con la sintaxis, por lo que esta solicitud puede ser totalmente inocente de mí, y para eso me disculpo de antemano. Si ejecuta la consulta, verá que el nodo 1 se repite en los resultados de la ruta. ¿Alguien puede ayudarme a descubrir cómo modificar el SQL?

/*           1
           /   /
          2     3
         / /   /
        4  5  6
       /
      7
     / /
    8   9
*/

create table account(
acct_id INT,
parent_id INT REFERENCES account(acct_id),
acct_name VARCHAR(100),
PRIMARY KEY(acct_id)
);

insert into account (acct_id, parent_id, acct_name) values (1,1,''account 1'');
insert into account (acct_id, parent_id, acct_name) values (2,1,''account 2'');
insert into account (acct_id, parent_id, acct_name) values (3,1,''account 3'');
insert into account (acct_id, parent_id, acct_name) values (4,2,''account 4'');
insert into account (acct_id, parent_id, acct_name) values (5,2,''account 5'');
insert into account (acct_id, parent_id, acct_name) values (6,3,''account 6'');
insert into account (acct_id, parent_id, acct_name) values (7,4,''account 7'');
insert into account (acct_id, parent_id, acct_name) values (8,7,''account 8'');
insert into account (acct_id, parent_id, acct_name) values (9,7,''account 9'');

WITH RECURSIVE search_graph(acct_id, parent_id, depth, path, cycle) AS (
        SELECT g.acct_id, g.parent_id, 1,
          ARRAY[g.acct_id],
          false
        FROM account g
      UNION ALL
        SELECT g.acct_id, g.parent_id, sg.depth + 1,
          path || g.acct_id,
          g.acct_id = ANY(path)
        FROM account g, search_graph sg
        WHERE g.acct_id = sg.parent_id AND NOT cycle
)
SELECT path[1] as Child,parent_id as Parent,path || parent_id as path FROM search_graph
ORDER BY path[1],depth;

Answer 1

Usted tiene la cuenta 1 configurada como su propio padre. Si establece que el padre de esa cuenta es null , puede evitar tener esa cuenta como el nodo de inicio y el de finalización (la forma en que se configura su lógica incluirá un ciclo pero luego no se agregará a ese ciclo, lo que parece razonable). También se ve un poco mejor cambiar la columna final de "ruta" a algo parecido a un case when parent_id is not null then path || parent_id else path end case when parent_id is not null then path || parent_id else path end para evitar tener el nulo al final.

Answer 2

Puede simplificar en varios lugares (suponiendo que acct_id y acct_id NOT NULL acct_id NOT NULL ):

WITH RECURSIVE search_graph AS (
   SELECT parent_id, ARRAY[acct_id] AS path
   FROM   account

   UNION  ALL
   SELECT g.parent_id, sg.path || g.acct_id
   FROM   search_graph sg
   JOIN   account g ON g.acct_id = sg.parent_id 
   WHERE  g.acct_id <> ALL(sg.path)
   )
SELECT path[1] AS child
     , path[array_upper(path,1)] AS parent
     , path
FROM   search_graph
ORDER  BY path;

• Las columnas acct_id , depth , cycle son solo ruido en su consulta.
• La condición WHERE debe salir de la recursión un paso antes, antes de que la entrada duplicada del nodo superior esté en el resultado. Eso fue un "off-by-one" en su original.

El resto es formatear.

Si sabe que el único círculo posible en su gráfica es una autorreferencia, podemos tenerlo más barato:

WITH RECURSIVE search_graph AS (
   SELECT parent_id, ARRAY[acct_id] AS path, acct_id <> parent_id AS keep_going
   FROM   account

   UNION  ALL
   SELECT g.parent_id, sg.path || g.acct_id, g.acct_id <> g.parent_id
   FROM   search_graph sg
   JOIN   account g ON g.acct_id = sg.parent_id 
   WHERE  sg.keep_going
)
SELECT path[1] AS child
     , path[array_upper(path,1)] AS parent
     , path
FROM   search_graph
ORDER  BY path;

SQL Fiddle.

Tenga en cuenta que habría problemas (al menos hasta la página v9.4) para los tipos de datos con un modificador (como varchar(5) ) porque la concatenación de matrices pierde el modificador pero el rCTE insiste en que los tipos coincidan exactamente:

• Resultados sorprendentes para tipos de datos con modificador de tipo