Início / dba / Perguntas / 274417
Accepted
manoelpqueiroz
Asked: 2020-08-27 09:12:57 +0800 CST

Agregando modelos de conjuntos aninhados com restrições de campo

  • 772

Tenho uma tabela construída no modelo de conjunto aninhado que basicamente registra transações de diferentes naturezas (definidas pelo campo idx):

pk  lft rgt idx             value
 1   1  30  2783     20402.710000
 2   2   3  2783    -13885.670000
 3   4   5   131        78.547946
 4   6   7  2783      -586.810000
 5   8   9     1         0.013421
 6  10  11  2783     -1777.630000
 7  12  13   873     10791.237266
 8  14  15  2783       -72.510000
 9  16  17  1697       128.626280
10  18  19  2783      -600.430000
11  20  21  1168         6.949175
12  22  23  2783     -2479.080000
13  24  25  1758       853.502787
14  26  27  2783     -1000.580000
15  28  29  3794      3172.428304
16  31  40  2783       615.940000
17  32  33  2783      -615.940000
18  34  39     1         0.040390
19  35  36     1        -0.000152
20  37  38     1        -0.000300
21  41  42  2783      3012.050000

os três representados no conjunto aninhado

Como essas transações são aninhadas de uma maneira particular, onde um nó filho pode ser de natureza diferente de seu pai (representando uma espécie de "transformação" ao longo do caminho), estou tendo dificuldade em buscar o "saldo" para cada pai nó em sua própria natureza :

prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000

Como eu poderia alcançar essa saída desejada dada a tabela original? Eu tentei fazer algo ao longo das linhas descritas nesta pergunta , mas tudo o que consegui foi um único nó pai e todos os seus filhos com o mesmo idx, o que eu poderia fazer mais facilmente no meu caso SELECT * FROM table WHERE idx = 2783ou similar.

Caso ajude, cada nó pai tem um valor positivo, nenhum nó com valores negativos pode ter filhos.

sqlite hierarchy

1 respostas

  1. Best Answer

    Estes são os passos para obter o resultado desejado:

    1. Uma auto-junção cruzada para obter todas as combinações possíveis de chaves primárias (isso eleva o número de registros ao quadrado);
    2. Aplique o filtro de conjunto aninhado (comentado abaixo) para remover todas as combinações de nós que não estão relacionados (por exemplo, pk1 e 15);
    3. Afirme que apenas combinações da mesma natureza estão sendo buscadas e agregadas, igualando o idxde ambos nodee crosst;
    4. Como estamos agregando de crosst.idx, isso significa que somará o "saldo" de todos os nós, incluindo aqueles que são negativos (e, portanto, órfãos); o terceiro filtro garante que estamos obtendo apenas o saldo total a partir dos pais positivos originais.
    SELECT
    crosst.pk AS prnt_pk,
    crosst.idx,
    SUM(node.value) AS aggr
FROM
    nsm_table AS node,
    nsm_table AS crosst
WHERE 1=1
    AND node.lft BETWEEN crosst.lft AND crosst.rgt --NSM filter for containers
    AND node.idx = crosst.idx
    AND crosst.value > 0
GROUP BY crosst.pk, crosst.idx
HAVING SUM(node.value) >= 0

    E a saída é exatamente a desejada:

    prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000
    • 0

relate perguntas