Início / dba / Perguntas / 63153
Accepted
JohnCand
Asked: 2014-04-15 04:37:17 +0800 CST

Como classifico os resultados de uma consulta recursiva em uma forma de árvore expandida?

  • 772

Vamos supor que você tenha uma nodestabela como esta:

CREATE TABLE nodes (
    node serial PRIMARY KEY,
    parent integer NULL REFERENCES nodes(node),
    ts timestamp NOT NULL DEFAULT now()
);

Ele representa uma estrutura de árvore semelhante a um nó padrão com nós raiz no topo e vários nós filhos pendurados nos nós raiz ou outros nós filhos.

Vamos inserir alguns valores de exemplo:

INSERT INTO nodes (parent) VALUES
  (NULL), (NULL), (NULL), (NULL)
, (1), (1), (1), (1), (6), (1), (6)
, (9), (6), (6), (3), (3), (3), (15);

Agora eu quero recuperar os primeiros 10 nós raiz e todos os seus filhos até uma profundidade de 4:

WITH RECURSIVE node_rec AS (
    (SELECT 1 AS depth, * FROM nodes WHERE parent IS NULL LIMIT 10)

    UNION ALL

    SELECT depth + 1, n.*
    FROM nodes AS n JOIN node_rec ON (n.parent = node_rec.node)
    WHERE depth < 4
)
SELECT * FROM node_rec;

Isso funciona muito bem e me dá o seguinte resultado:

 depth | node | parent 
-------+------+--------
     1 |  1   |
     1 |  2   |
     1 |  3   |
     1 |  4   |
     2 |  5   |  1
     2 |  6   |  1
     2 |  7   |  1
     2 |  8   |  1
     2 | 10   |  1
     2 | 15   |  3
     2 | 16   |  3
     2 | 17   |  3
     3 |  9   |  6
     3 | 11   |  6
     3 | 13   |  6
     3 | 14   |  6
     3 | 18   | 15
     4 | 12   |  9

Como você deve ter notado, não há ORDER BYcláusula, então a ordem não é definida. A ordem que você vê aqui é de nós raiz para nós mais profundos.

Como eu ordenaria os resultados como eles apareceriam em uma exibição em árvore expandida, como você pode ver na imagem de exemplo abaixo?

Exibição em árvore expandida de nós

Basicamente, quero que os nós filhos sejam colocados logo após o nó pai correspondente. Se dois ou mais nós filhos tiverem o mesmo nó pai, quero que eles sejam classificados por seu carimbo de data/hora. Com base no exemplo acima, aqui está a ordem de saída desejada que estou tentando alcançar:

 depth | node | parent | ts
-------+------+--------+---------
     1 |  1   |        | 2014-01-01 00:00:00
     2 |  5   |     1  | 2014-01-01 00:10:00
     2 |  6   |     1  | 2014-01-01 00:20:00
     3 |  9   |     6  | 2014-01-01 00:25:00
     4 |  12  |     9  | 2014-01-01 00:27:00
     3 |  11  |     6  | 2014-01-01 00:26:00
     3 |  13  |     6  | 2014-01-01 00:30:00
     3 |  14  |     6  | 2014-01-01 00:36:00
     2 |  7   |     1  | 2014-01-01 00:21:00
     2 |  8   |     1  | 2014-01-01 00:22:00
     2 |  10  |     1  | 2014-01-01 00:23:00
     1 |  2   |        | 2014-01-01 00:08:00
     1 |  3   |        | 2014-01-01 00:09:00
     2 |  15  |     3  | 2014-01-01 10:00:00
     3 |  18  |     15 | 2014-01-01 11:05:00
     2 |  16  |     3  | 2014-01-01 11:00:00
     2 |  17  |     3  | 2014-01-01 12:00:00
     1 |  4   |        | 2014-01-01 00:10:00
postgresql order-by

1 respostas

  1. Best Answer

    Você pode ordenar pelo array que representa o caminho da raiz até a folha:

    Para simplificar, deixei de fora LIMIT(para que também não precisemos de parênteses extras) e WHEREda sua consulta original. Aqueles podem ser adicionados livremente.

    consulta básica

    WITH RECURSIVE node_rec AS (
   SELECT 1 AS depth, *
        , ARRAY[node] AS path
   FROM   nodes
   WHERE  parent IS NULL

   UNION ALL
   SELECT r.depth + 1, n.*
        , r.path || n.node
   FROM   node_rec r 
   JOIN   nodes    n ON n.parent = r.node
   )
SELECT *
FROM   node_rec
ORDER  BY path;

    O mesmo pode ser simplificado com a cláusula dedicadaSEARCH no Postgres 14 ou posterior:

    WITH RECURSIVE node_rec AS (
   SELECT 1 AS depth, *
   FROM   nodes
   WHERE  parent IS NULL

   UNION ALL
   SELECT r.depth + 1, n.*
   FROM   node_rec r 
   JOIN   nodes    n ON n.parent = r.node
   ) SEARCH DEPTH FIRST BY node SET path
SELECT *
FROM   node_rec
ORDER  BY path, ts;

    Ordenar por coluna adicional

    Se dois ou mais nós filhos tiverem o mesmo nó pai, quero que eles sejam classificados por seu carimbo de data/hora.

    Nós realmente precisamos classificar pela coluna timestamp tsprimeiro em cada nível, e nodeé apenas um desempate (o timestamp pode não ser único). A solução mais simples é usar ambos como recordtipo:

    WITH RECURSIVE node_rec AS (
   SELECT 1 AS depth, *
         , ARRAY[(ts, node)] AS path
   FROM   nodes
   WHERE  parent IS NULL

   UNION ALL
   SELECT r.depth + 1, n.*
        , r.path || (n.ts, n.node)
   FROM   node_rec r 
   JOIN   nodes    n ON n.parent = r.node
   )
SELECT *
FROM   node_rec
ORDER  BY path;

    Consulta equivalente e mais simples com a SEARCHcláusula no Postgres 14 ou posterior:

    WITH RECURSIVE node_rec AS (
   SELECT 1 AS depth, *
   FROM   nodes
   WHERE  parent IS NULL

   UNION ALL
   SELECT r.depth + 1, n.*
   FROM   node_rec r 
   JOIN   nodes    n ON n.parent = r.node
   ) SEARCH DEPTH FIRST BY ts, node SET path
SELECT *
FROM   node_rec
ORDER  BY path, ts;

    Como você pode ver, a nova sintaxe permite especificar várias colunas para a classificação.

    db<>mexa aqui

    • 16

relate perguntas