0

I have a table built on the nested set model that basically records transactions of different natures (defined by the field 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

the three represented in the nested set

Since these transactions are nested in a particular way where a child node can be of different nature than its parent (representing a sort of "transformation" along the way), I am having a hard time trying to fetch the "balance" for each parent node in their own nature:

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

How could I achieve this desired output given the original table? I have tried doing something along the lines described in this question, but all I have managed to get was a single parent node and all its children with the same idx, which I could do more easily in my case with SELECT * FROM table WHERE idx = 2783 or similar.

In case it helps, every parent node has a positive value, no node with negative values are allowed to have children.

0

These are the steps to obtain the desired result:

  1. A cross self-join to obtain every single combination of primary keys possible (this squares the number of records);
  2. Apply the nested set filter (commented below) to remove all combinations of nodes that are not related (e.g., pk 1 and 15);
  3. Assert that only combinations of the same nature are being fetched and aggregated, by equalling the idx from both node and crosst;
  4. Since we are aggregating from crosst.idx, that means it will sum the "balance" for all nodes, including those that are negative (and thus orphans); the third filter ensures we are only taking the total balance starting from original positive parents.
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

And the output is exactly as the desired one:

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.