主页 / dba / 问题 / 274417
Accepted
manoelpqueiroz
Asked: 2020-08-27 09:12:57 +0800 CST

聚合具有字段约束的嵌套集模型

  • 772

我有一个建立在嵌套集模型上的表，它基本上记录了不同性质的事务（由字段定义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

嵌套集中表示的三个

由于这些事务以特定方式嵌套，其中子节点的性质可能与其父节点的性质不同（在此过程中代表一种“转换”），我很难为每个父节点获取“余额”节点本身的性质：

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

给定原始表格，我怎样才能实现这个期望的输出？我已经尝试按照这个问题中描述的方式做一些事情，但是我设法得到的只是一个父节点及其所有具有相同的子节点，idx在我的情况下我可以更轻松地完成SELECT * FROM table WHERE idx = 2783或类似。

如果有帮助，每个父节点都有一个正值，不允许有负值的节点有子节点。

sqlite hierarchy

1 个回答

  1. Best Answer

    这些是获得所需结果的步骤：

    1. 交叉自连接以获得可能的主键的每一个组合（这将平方记录数）；
    2. 应用嵌套集过滤器（在下面注释）以删除所有不相关的节点组合（例如，pk1 和 15）；
    3. 断言只有相同性质的组合才会被提取和聚合，方法是等于idxfrom both nodeand crosst;
    4. 由于我们从 聚合crosst.idx，这意味着它将汇总所有节点的“余额”，包括那些为负的（因此是孤儿）；第三个过滤器确保我们只从最初的积极父母开始获取总余额。
    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

    并且输出与期望的完全一样：

    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

相关问题