Sqlite

聚合具有欄位約束的嵌套集模型

  • August 27, 2020

我有一個建立在嵌套集模型上的表,它基本上記錄了不同性質的事務(由欄位定義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或類似。

如果有幫助,每個父節點都有一個正值,不允許有負值的節點有子節點。

這些是獲得所需結果的步驟:

  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

引用自:https://dba.stackexchange.com/questions/274417