Início / user-84685

Diggity's questions

Martin Hope
Diggity
Asked: 2016-01-12 21:08:19 +0800 CST
  • 15

Preciso calcular a profundidade de um descendente de seu ancestral. Quando um registro possui object_id = parent_id = ancestor_id, ele é considerado um nó raiz (o ancestral). Eu tenho tentado obter uma WITH RECURSIVEconsulta em execução com o PostgreSQL 9.4 .

Eu não controlo os dados ou as colunas. O esquema de dados e tabelas vem de uma fonte externa. A tabela está crescendo continuamente . Agora em cerca de 30 mil registros por dia. Qualquer nó na árvore pode estar faltando e eles serão extraídos de uma fonte externa em algum momento. Eles geralmente são extraídos em created_at DESCordem, mas os dados são extraídos com trabalhos assíncronos em segundo plano.

Inicialmente, tínhamos uma solução de código para esse problema, mas agora com mais de 5 milhões de linhas, leva quase 30 minutos para ser concluída.

Exemplo de definição de tabela e dados de teste:

CREATE TABLE objects (
  id          serial NOT NULL PRIMARY KEY,
  customer_id integer NOT NULL,
  object_id   integer NOT NULL,
  parent_id   integer,
  ancestor_id integer,
  generation  integer NOT NULL DEFAULT 0
);

INSERT INTO objects(id, customer_id , object_id, parent_id, ancestor_id, generation)
VALUES (2, 1, 2, 1, 1, -1), --no parent yet
       (3, 2, 3, 3, 3, -1), --root node
       (4, 2, 4, 3, 3, -1), --depth 1
       (5, 2, 5, 4, 3, -1), --depth 2
       (6, 2, 6, 5, 3, -1), --depth 3
       (7, 1, 7, 7, 7, -1), --root node
       (8, 1, 8, 7, 7, -1), --depth 1
       (9, 1, 9, 8, 7, -1); --depth 2

Observe que object_idnão é único, mas a combinação (customer_id, object_id)é única.
Executando uma consulta como esta:

WITH RECURSIVE descendants(id, customer_id, object_id, parent_id, ancestor_id, depth) AS (
  SELECT id, customer_id, object_id, parent_id, ancestor_id, 0
  FROM objects
  WHERE object_id = parent_id

  UNION

  SELECT o.id, o.customer_id, o.object_id, o.parent_id, o.ancestor_id, d.depth + 1
  FROM objects o
  INNER JOIN descendants d ON d.parent_id = o.object_id
  WHERE
    d.id <> o.id
  AND
    d.customer_id = o.customer_id
) SELECT * FROM descendants d;

Eu gostaria que a generationcoluna fosse definida como a profundidade que foi calculada. Quando um novo registro é adicionado, a coluna de geração é definida como -1. Existem alguns casos em que um parent_idpode não ter sido puxado ainda. Se parent_idnão existir, deve deixar a coluna de geração definida como -1.

Os dados finais devem ficar assim:

id | customer_id | object_id | parent_id | ancestor_id | generation
2    1             2           1           1            -1
3    2             3           3           3             0
4    2             4           3           3             1
5    2             5           4           3             2
6    2             6           5           3             3
7    1             7           7           7             0
8    1             8           7           7             1
9    1             9           8           7             2

O resultado da consulta deve ser atualizar a coluna de geração para a profundidade correta.

Comecei a trabalhar com as respostas a esta pergunta relacionada no SO .

postgresql update